How to Create an IIS Manager Provider that uses a SQL Database to Store User Credentials

By Robert McMurray

May 18, 2010

Overview

Microsoft's Internet Information Services (IIS) was built with extensibility in mind for a variety of management tasks. This extensibility allows developers the ability to create their own providers that can replace the core management functionality that was shipped when IIS was released. One of the great features in IIS is the IIS Managers, which are accounts that can be allowed to manage the configuration settings even if they are non-Windows accounts that only exist within the context of IIS.

This document walks you through creating a new provider for the IIS Managers feature that uses an SQL database for authentication.

Prerequisites

The following items are required to complete the procedures in this article:

  • IIS 7 or above must be installed on your Windows Server 2008 or Windows Server 2008 R2 computer, along with the following role services:
    • The IIS Management Console, this is used to configure IIS Managers.
    • The IIS Management Service, this allows for remote management of IIS.
  • If you do not have access to an existing SQL Server, you must install SQL Server.

Note: This document was written using Visual Studio 2008 and an instance of SQL server that is installed on the local web server. You could use this walkthrough with a different version of Visual Studio or a remote database, but you would need to change the appropriate steps accordingly.

Step 1: Create the SQL Database

The first step is to create the SQL database that will store the usernames, passwords, and statuses for IIS Managers. To do so, use the following steps:

  1. Modify the highlighted sections in the following TSQL script for your environment. For example, the script as-written will create the physical database files in "C:\databases", which is probably not a valid path on your system.
    /****** Create the Database ******/
    
    USE [master]
    GO
    
    CREATE DATABASE [DemoDB] ON  PRIMARY 
    ( NAME = N'DemoDB', FILENAME = N'C:\databases\DemoDB.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'DemoDB_log', FILENAME = N'C:\databases\DemoDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
     COLLATE SQL_Latin1_General_CP1_CI_AS
    GO
    EXEC dbo.sp_dbcmptlevel @dbname=N'DemoDB', @new_cmptlevel=90
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [DemoDB].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    ALTER DATABASE [DemoDB] SET ANSI_NULL_DEFAULT OFF 
    GO
    ALTER DATABASE [DemoDB] SET ANSI_NULLS OFF 
    GO
    ALTER DATABASE [DemoDB] SET ANSI_PADDING OFF 
    GO
    ALTER DATABASE [DemoDB] SET ANSI_WARNINGS OFF 
    GO
    ALTER DATABASE [DemoDB] SET ARITHABORT OFF 
    GO
    ALTER DATABASE [DemoDB] SET AUTO_CLOSE OFF 
    GO
    ALTER DATABASE [DemoDB] SET AUTO_CREATE_STATISTICS ON 
    GO
    ALTER DATABASE [DemoDB] SET AUTO_SHRINK OFF 
    GO
    ALTER DATABASE [DemoDB] SET AUTO_UPDATE_STATISTICS ON 
    GO
    ALTER DATABASE [DemoDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
    GO
    ALTER DATABASE [DemoDB] SET CURSOR_DEFAULT  GLOBAL 
    GO
    ALTER DATABASE [DemoDB] SET CONCAT_NULL_YIELDS_NULL OFF 
    GO
    ALTER DATABASE [DemoDB] SET NUMERIC_ROUNDABORT OFF 
    GO
    ALTER DATABASE [DemoDB] SET QUOTED_IDENTIFIER OFF 
    GO
    ALTER DATABASE [DemoDB] SET RECURSIVE_TRIGGERS OFF 
    GO
    ALTER DATABASE [DemoDB] SET  ENABLE_BROKER 
    GO
    ALTER DATABASE [DemoDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
    GO
    ALTER DATABASE [DemoDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
    GO
    ALTER DATABASE [DemoDB] SET TRUSTWORTHY OFF 
    GO
    ALTER DATABASE [DemoDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
    GO
    ALTER DATABASE [DemoDB] SET PARAMETERIZATION SIMPLE 
    GO
    ALTER DATABASE [DemoDB] SET  READ_WRITE 
    GO
    ALTER DATABASE [DemoDB] SET RECOVERY SIMPLE 
    GO
    ALTER DATABASE [DemoDB] SET  MULTI_USER 
    GO
    ALTER DATABASE [DemoDB] SET PAGE_VERIFY CHECKSUM  
    GO
    ALTER DATABASE [DemoDB] SET DB_CHAINING OFF 
    
    /****** Create the Database Table ******/
    
    USE [DemoDB]
    GO
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Authentication](
        [Username] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [Password] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [Enabled] [bit] NOT NULL CONSTRAINT [DF_Authentication_Enabled]  DEFAULT ((1)),
     CONSTRAINT [PK_Authentication] PRIMARY KEY CLUSTERED 
    (
        [UserName] ASC
    ) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
  2. Open your SQL Server management studio and execute the script as a new query. This will create the "DemoDB" database and the "Authentication" table that the provider will use.
  3. Because the management service for IIS runs as "NT AUTHORITY\LOCAL SERVICE", you need to add the "NT AUTHORITY\LOCAL SERVICE" account to the SQL Server database that you just created on your server. To do so, use the following steps:
    • Open the SQL Server Enterprise Manager on the web server.
    • Create a SQL Server login for NT AUTHORITY\LOCAL SERVICE.
    • Grant the login access to the DemoDB database by creating a database user.
    • Add the database user to the db_datareader and db_datawriter database roles.

Step 2: Set up the Visual Studio Project Environment

  1. Open Microsoft Visual Studio 2008.
  2. Click the File menu, then New, then Project.
  3. In the New Project dialog:
    • Choose Visual C# as the project type.
    • Choose Class Library as the template.
    • Type SqlIisManagerDemo as the name of the project.
    • Click OK.
  4. Add a reference path to the Microsoft.Web.Management library:
    • Click Project, then Add Reference...
    • Click the Browse tab.
    • Navigate to your "%WinDir%\System32\Inetsrv" folder.
    • Select the "Microsoft.Web.Management.dll" file.
    • Click OK.
  5. Add a strong name key to the project:
    • Click Project, then SqlIisManagerDemo Properties.
    • Click the Signing tab.
    • Check the Sign the assembly check box.
    • Choose <New...> from the strong key name drop-down box.
    • Enter SqlIisManagerDemoKey for the key file name.
    • If desired, enter a password for the key file; otherwise, uncheck the Protect my key file with a password box.
    • Click OK.
  6. OPTIONAL: Add a custom build event to automatically GAC the DLL:
    • Click Project, then SqlIisManagerDemo Properties.
    • Click the Build Events tab.
    • Enter the following in the Post-build event command line box:
      call "%VS90COMNTOOLS%\vsvars32.bat">nul
      gacutil.exe /if "$(TargetPath)"
  7. OPTIONAL: Enable debugging with IIS Manager:
    • Click Project, then SqlIisManagerDemo Properties.
    • Click the Debug tab.
    • Click to select Start External Program, then enter the following in the text box:
      C:\Windows\System32\inetsrv\InetMgr.exe
      Where C: is your operating system drive.
  8. Save the project.

Step 3: Create the Extensibility Class

  1. Add the code for the class:
    • In Solution Explorer, double-click the Class1.cs file.
    • Remove the existing code.
    • Paste the following code into the editor:
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using Microsoft.Web.Management.Server;
      using System.Data.SqlClient;
      
      namespace SqlIisManagerDemo
      {
         public class IisManagerProvider : ManagementAuthenticationProvider
         {
            // Storage for the provider's SQL connection string.
            private string _connectionString;
      
            public override void Initialize(IDictionary<string, string> args)
            {
               // Initialize the argument list.
               base.Initialize(args);
      
               // Test if the arguments contain a connection string.
               if (args.ContainsKey("connectionString"))
               {
                  // Configure the provider's connection string from the argument.
                  _connectionString = args["connectionString"];
               }
               else
               {
                  // Configure a default connection string.
                  _connectionString = "Data Source=localhost;Initial Catalog=DemoDB;Trusted_Connection=yes";
               }
            }
      
            // Retrieve a SQL connection.
            private SqlConnection GetConnection()
            {
               // Create a new SQL connection using the provider's connection string.
               SqlConnection connection = new SqlConnection(_connectionString);
               // Open the SQL connection.
               connection.Open();
               // Return the connection to the 
               return connection;
            }
      
            // Authenticate a user.
            public override bool AuthenticateUser(string userName, string password)
            {
               // Open a SQL connection.
               using (SqlConnection connection = GetConnection())
               {
                  // Create a command object.
                  SqlCommand command = connection.CreateCommand();
                  // Specify the SQL query to verify a username/password.
                  command.CommandText = "SELECT Count(Username) FROM Authentication WHERE Enabled=1 and Username=@Username and Password=@Password";
                  // Specify the username parameter.
                  command.Parameters.Add(new SqlParameter("@Username", userName));
                  // Specify the password parameter.
                  command.Parameters.Add(new SqlParameter("@Password", password));
                  // Retrieve the number of matching records.
                  int rowCount = (int)command.ExecuteScalar();
                  // Return whether the username/password are valid.
                  return rowCount != 0;
               }
            }
      
            // Create a new user.
            public override ManagementUserInfo CreateUser(string userName, string password)
            {
               // Test whether the username already exists.
               if (GetUser(userName) != null)
               {
                  // throw an exception if the user already exists.
                  throw new InvalidOperationException("Username already exists");
               }
      
               // Open a SQL connection.
               using (SqlConnection connection = GetConnection())
               {
                  // Create a command object.
                  SqlCommand command = connection.CreateCommand();
                  // Specify the SQL query to insert a username/password.
                  command.CommandText = "INSERT INTO Authentication (Username, Password) Values(@Username, @Password)";
                  // Specify the username parameter.
                  command.Parameters.Add(new SqlParameter("@Username", userName));
                  // Specify the password parameter.
                  command.Parameters.Add(new SqlParameter("@Password", password));
                  // Execute the SQL query.
                  command.ExecuteNonQuery();
               }
               // Return whether the user was successfully created.
               return GetUser(userName);
            }
      
            // Delete a user.
            public override void DeleteUser(string userName)
            {
               // Open a SQL connection.
               using (SqlConnection connection = GetConnection())
               {
                  // Create a command object.
                  SqlCommand command = connection.CreateCommand();
                  // Specify the SQL query to delete a user.
                  command.CommandText = "DELETE FROM Authentication WHERE Username=@Username";
                  // Specify the username parameter.
                  command.Parameters.Add(new SqlParameter("@Username", userName));
                  // Execute the SQL query.
                  command.ExecuteNonQuery();
               }
            }
      
            // Disable a user.
            public override void DisableUser(string userName)
            {
               // Open a SQL connection.
               using (SqlConnection connection = GetConnection())
               {
                  // Create a command object.
                  SqlCommand command = connection.CreateCommand();
                  // Specify the SQL query to disable the user.
                  command.CommandText = "UPDATE Authentication SET Enabled=0 WHERE Username=@Username";
                  // Specify the username parameter.
                  command.Parameters.Add(new SqlParameter("@Username", userName));
                  // Execute the SQL query.
                  command.ExecuteNonQuery();
               }
            }
      
            // Enable a user.
            public override void EnableUser(string userName)
            {
               // Open a SQL connection.
               using (SqlConnection connection = GetConnection())
               {
                  // Create a command object.
                  SqlCommand command = connection.CreateCommand();
                  command.CommandText = "UPDATE Authentication SET Enabled=1 WHERE Username=@Username";
                  // Specify the username parameter.
                  command.Parameters.Add(new SqlParameter("@Username", userName));
                  // Execute the SQL query.
                  command.ExecuteNonQuery();
               }
            }
      
            // Retrieve a specific username and status.
            public override ManagementUserInfo GetUser(string userName)
            {
               // Open a SQL connection.
               using (SqlConnection connection = GetConnection())
               {
                  // Create a command object.
                  SqlCommand command = connection.CreateCommand();
                  // Specify the SQL query to retrieve a specific username and status.
                  command.CommandText = "SELECT Username, Enabled FROM Authentication WHERE Username=@Username";
                  // Specify the username parameter.
                  command.Parameters.Add(new SqlParameter("@Username", userName));
                  // Execute the SQL query and retrieve a SQL reader object.
                  SqlDataReader reader = command.ExecuteReader();
                  // Test if any data was retrieved.
                  if (reader.HasRows)
                  {
                     // Retrieve a record from the database.
                     reader.Read();
                     // Retrieve the username.
                     string username = reader.GetString(0);
                     // Retrieve the status.
                     bool enabled = reader.GetBoolean(1);
                     // Close the SQL reader.
                     reader.Close();
                     // Return an IIS Manager user with the username/status.
                     return new ManagementUserInfo(username, enabled);
                  }
                  else
                  {
                     // Return null if the username was not found.
                     return null;
                  }
               }
            }
      
            // Retrieve a collection of usernames and statuses.
            public override ManagementUserInfoCollection GetUsers(int itemIndex, int itemsPerPage)
            {
               // Open a SQL connection.
               using (SqlConnection connection = GetConnection())
               {
                  // Create a command object.
                  SqlCommand command = connection.CreateCommand();
                  // Specify the SQL query to retrieve all usernames and statuses.
                  command.CommandText = "SELECT Username, Enabled FROM Authentication";
                  // Execute the SQL query and retrieve a SQL reader object.
                  SqlDataReader reader = command.ExecuteReader();
                  // Create a new collection of IIS Manager users.
                  ManagementUserInfoCollection users = new ManagementUserInfoCollection();
                  // Loop through the database records.
                  while (reader.Read())
                  {
                     // Retrieve the username.
                     string username = reader.GetString(0);
                     // Retrieve the status.
                     bool enabled = reader.GetBoolean(1);
                     // Add a user object with the username/status to the collection.
                     users.Add(new ManagementUserInfo(username, enabled));
                  }
                  // Return the collection.
                  return users;
               }
            }
      
            // Test the password strength.
            public override InvalidPasswordReason IsPasswordStrongEnough(string password)
            {
               // NOTE: You could implement a custom password strength algorithm here.
               return InvalidPasswordReason.NoError;
            }
      
            // Update the password for a specific username.
            public override void SetPassword(string userName, string newPassword)
            {
               // Open a SQL connection.
               using (SqlConnection connection = GetConnection())
               {
                  // Create a command object.
                  SqlCommand command = connection.CreateCommand();
                  // Specify the SQL query to update the password for a specific username.
                  command.CommandText = "UPDATE Authentication SET Password=@Password WHERE Username=@Username";
                  // Specify the username parameter.
                  command.Parameters.Add(new SqlParameter("@Username", userName));
                  // Specify the password parameter.
                  command.Parameters.Add(new SqlParameter("@Password", newPassword));
                  // Execute the SQL query.
                  command.ExecuteNonQuery();
               }
            }
         }
      }
  2. Save and compile the project.

Step 4: Add the Provider to IIS

  1. Determine the assembly information for the provider:
    • In Windows Explorer, open your "C:\Windows\assembly" path, where C: is your operating system drive.
    • Locate the SqlIisManagerDemo assembly.
    • Right-click the assembly and click Properties.
    • Copy the Culture value; for example: Neutral.
    • Copy the Version number; for example: 1.0.0.0.
    • Copy the Public Key Token value; for example: 426f62526f636b73.
    • Click Cancel.
  2. Replace the default provider with your demo provider:
    • Open the administration.config file for editing.
    • In <system.webServer>/<management>, locate and comment out the following default authentication provider:
      <authentication defaultProvider="ConfigurationAuthenticationProvider">
         <providers>
            <add name="ConfigurationAuthenticationProvider"
               type="Microsoft.Web.Management.Server.ConfigurationAuthenticationProvider, Microsoft.Web.Management, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
         </providers>
      </authentication>
    • Add the following default authentication provider:
      <authentication defaultProvider="SqlIisManagerDemo">
         <providers>
            <add name="SqlIisManagerDemo"
               type="SqlIisManagerDemo.IisManagerProvider, SqlIisManagerDemo, Version=1.0.0.0, Culture=neutral, PublicKeyToken=426f62526f636b73"
               connectionString="Data Source=localhost;Initial Catalog=DemoDB;Trusted_Connection=yes" />
         </providers>
      </authentication>
    • Save and close the the administration.config file.
  3. Close and re-open the IIS Manager.
  4. If you are using the Windows Management Service for IIS, you need to restart that service:
    • Open IIS Manager.
    • Highlight your server in the Connections tab.
    • Double-click the Management Service feature in the Home pane.
    • Click Restart in the Actions pane.

Summary

In this walkthrough you completed the following:

  1. Created a SQL database to store usernames, passwords, and statuses.
  2. Created a C# class project in Visual Studio for an IIS Management Provider.
  3. Replaced the default IIS Management Provider with your custom IIS Management Provider.

After completing all of these steps, when you configure IIS to allow remote management using IIS Manager credentials, those credentials will come from the SQL database instead of the built-in IIS Manager provider.



Discuss in IIS Forums