Using Windows Security with IIS and SQL Server 2000

Configuring IIS and SQL Server 2000 to use Windows logon information is easy

More and more organizations are hosting Microsoft IIS intranet and Internet applications that use Microsoft SQL Server 2000. As developers design such applications, the question of how to best provide secure access to all or part of an application or Web site comes up. To properly answer that question, you need to understand the available options.

You typically use one of two ways to secure access to part or all of an application. One way is to create a table of usernames and passwords and store that table in a database such as SQL Server. Then, when users try to access the secure part of the application, the application requests their username and password and validates this information against the user table, as Figure 1 shows. When the user enters his or her username and password, the browser uses HTTP to send the credentials to an Active Server Pages (ASP) script that processes them. The ASP script must ask SQL Server to look up the username and password in the user table to verify the user. This method works fine for both intranet and Internet applications, to a point.

The other method for securing access to an application is to let Windows 2000 or Windows NT 4.0 validate users. With this approach, when a user visits a Web page that contains an ASP script that tries to access SQL Server, SQL Server reverifies the user's security credentials with the domain controller (DC) or with the local server's Win2K SAM or NT 4.0 SAM. If SQL Server is running on a server that's part of a domain, as Figure 2 shows, SQL Server checks the DC. If not, SQL Server checks the local server's SAM (you can mirror the usernames and passwords on the IIS and SQL Server systems so that users who authenticate to IIS will match users on the SQL Server system). Whether the application checks credentials on the DC or the local server, users can log on to IIS and access SQL Server with the same account. Using a DC is arguably better because it centralizes the user accounts and groups in one place where all of your servers can access them. Another advantage is that users don't need a second username and password to access the application if the account with which they're logging on to their workstation is on the server or in the domain. Notice in Figure 2 that the browser screen doesn't ask the user to supply credentials.

When should you use Win2K or NT 4.0 authentication, and when should you use a database? My company's rule of thumb is to use Windows authentication for employees and others who have network access and to use a database for external visitors to the company Web site or extranet. Employees have accounts in the Win2K domain and can use integrated security easily. For example, all of our employees and others who have access to our network can access the intranet with no further security setup, except for changing one or two settings in Internet Services Manager (ISM). Using Win2K or NT 4.0 security lets us set up usernames in just one place. We store usernames and passwords for Web site and extranet users in a database table similar to the one in Figure 1. We must set up an account in the table for every user who needs access to the extranet part of our Web site. If you want to limit the manual work of adding to a database employees and others with network access and spare these users from having to enter their username and password when they request a SQL Server application, read on to find out how to configure SQL Server and IIS to use Windows security.

Configuring SQL Server and IIS to Use Windows Security
You can easily set up SQL Server 2000 to use Win2K or NT 4.0 security. Open SQL Server Enterprise Manager, right-click the name of the SQL Server system that you want to modify, and select Properties to display the SQL Server Properties dialog box that Figure 3 shows. On the Security tab, select the Windows only option and click OK. You must restart SQL Server to put the change into effect.

You must also set up IIS to use the authentication method that you want. Open ISM, then open the properties for the Web site or virtual directory that's hosting the application. On the Directory Security tab, click Edit to display the Authentication Methods dialog box that Figure 4 shows (the box looks slightly different in NT 4.0). Notice in Figure 4 that both the Anonymous access and Integrated Windows authentication check boxes are selected. To turn off anonymous access to the Web site, clear the Anonymous access check box and click OK.

If you've configured a SQL Server system to use Windows authentication and you don't clear the Anonymous access check box for IIS, users who attempt to access your Web application will receive an error message like the one that Figure 5 shows. This error explains that the user account being used to access the SQL Server database is IUSR_MYSERVER, which is the default anonymous-access account for IIS running on a server named MYSERVER. The IUSR account typically doesn't have permissions to access SQL Server—hence, the error. To prevent this error, you could grant the IUSR account SQL Server permissions. However, prohibiting anonymous access to a Web site is a better solution because it forces users to have a valid Win2K or NT 4.0 account to gain access to the site and to SQL Server.

Win2K, NT 4.0, Windows Me, and Windows 9x clients can all use Windows authentication to connect to your IIS application. If you select only Integrated Windows authentication (NT Challenge/Response in NT 4.0) in the Authentication Methods dialog box, users must log on to the workstation with a username and password. If a user's account is in the security database that the Web server uses (on the DC or in the local server's Win2K SAM or NT 4.0 SAM), when the user connects to the Web site with Microsoft Internet Explorer, IE validates the user with the security database. After the user is authenticated, he or she can access any resources he or she has authorization to access.

If, however, a user logs on to a workstation with an account that isn't in the security database that the Web server uses and then connects to the Web site with IE, IE prompts the user for his or her Win2K or NT 4.0 logon credentials. If the user responds with a username and password that are in the security database, IIS uses Windows authentication to log on the user.

The IIS online documentation mentions two limitations of Windows authentication: Users must use IE 2.0 or later as their browser, and integrated Windows authentication doesn't work over HTTP Proxy connections. The first limitation isn't typically a problem because the user accounts must be in either a domain or a local database. Thus, you know who the users are and can specify that they use IE 5.0 or IE 4.0 to connect to your application.

If users must use other browsers or HTTP Proxy connections, you can select the Basic authentication (password is sent in clear text) option in the Authentication Methods dialog box to control access to the Web site and therefore to SQL Server. The basic authentication method sends usernames and passwords in clear text. Thus, if you use this method with Internet connections, you should use Secure Sockets Layer (SSL) to encrypt the channel before you ask for the username and password. You can set the authentication level for a virtual directory within a Web site if you need to secure only part of the site.

Let's take a moment to review authentication versus authorization. Authentication validates users against some set of credentials, such as Win2K or NT 4.0 accounts. If users log on with a valid username and password, the OS authenticates them as valid users. Authenticated users can't take any actions with resources such as files or database tables unless the users have authorization to do so. Typically, administrators use NTFS to set permissions to files and Enterprise Manager to set permissions to SQL Server objects to control authorization to these resources.

So far, I've covered part of the process of setting up security—the part in which Win2K or NT 4.0 authenticates users. When users try to connect to SQL Server, the database must further authenticate them before opening a trusted connection. To authenticate users, SQL Server obtains their login credentials from the OS and looks them up in SQL Server. For SQL Server to authenticate users, the users must have a SQL Server login account mapped to their user account or their user account must be in a Win2K or NT 4.0 user group that's linked to a SQL Server login. The users must also be authorized for specific SQL Server resources before they can gain access to those resources.

The way to give users access to a SQL Server resource is to map their Win2K or NT 4.0 accounts, individually or as a group, to a SQL Server login, then assign that login's permissions to the resource. Figure 6 shows the Win2K NorthWindReaders group, which contains users who should have permission to read data in the sample Northwind database. To add this group as a SQL Server login, open Enterprise Manager, then open the Security folder under the desired server. Right-click the Logins folder, and select New Login. On the SQL Server Login Properties - New Login dialog box's General tab, click the ellipsis (...) beside the Name text box to open the dialog box that Figure 7 shows. Select NorthWindReaders, click Add, then click OK. At the bottom of the General tab that Figure 8 shows, select the default database for the SQL Server login from the Database drop-down list (in this case, the master database is the default).

At this point, if you click OK, users in the NorthWindReaders group will be able to log in to SQL Server but they won't be able to access any database resources because you haven't authorized them to. One way to authorize users is to set permissions on the SQL Server login. To do this, first click the SQL Server Login Properties - New Login dialog box's Database Access tab. Then, select the database the group needs access to (such as Northwind). To control access to the database, click the SQL Server Login Properties - New Login dialog box's Server Roles tab and select the database again. After you select a database, you'll see a list of available database roles for that database at the bottom of the dialog box. You want users in the NorthWindReaders group to have read-only access to the Northwind database, so select the db_datareader role and click OK to create the login. Now, users in the NorthWindReaders group can use their Windows logon to access the Web site and the database. Alternatively, you can use the sp_grantlogin stored procedure to create a login and the sp_grantdbaccess stored procedure to grant access to a login.

Authentication Alternatives
Internet Information Services (IIS) 5.0 administrators have several alternatives to Windows authentication and basic authentication for authenticating users that visit a Web site. One alternative is to map a client certificate to a Win2K or domain user account. When users connect using that certificate, IIS 5.0 uses the mapped account to log on the users; the users can then use the account credentials to access resources such as SQL Server.

Another authentication alternative (for Win2K and IIS 5.0 or later shops only) is digest authentication. With digest authentication, the browser creates a hashed version of the username and password as well as other information. A third party can't easily decipher the hashed credentials, but the DC can match the hashed information with the plaintext version stored on the DC. Thus, digest authentication lets the browser and server authenticate the user without sending clear-text passwords. Obviously, users must use a browser that supports digest authentication (such as IE 5.5 or IE 5.0), and the IIS 5.0 server must be part of a Win2K Active Directory (AD) domain.

Remember, your goal is to authenticate users with Win2K or NT 4.0 so that SQL Server can authenticate users with the OS. The authentication method you choose is up to you; just make sure the method meets your overall security needs. For example, certificates work well for some users, but what happens when users need access to your server from many different workstations? You can't rely on just certificates in that case because each workstation won't have all the necessary user certificates.

SQL Server Roles
Now that you know the basics of how SQL Server security can work with Win2K and NT 4.0 security, let's look more closely at a SQL Server security feature that I touched on earlier: roles. After you've set up a Win2K or NT 4.0 user group as a SQL Server login, you can use the login in a couple of ways. You've already seen one way—we assigned the NorthWindReaders login read permission to the Northwind database.

Another way to set up security for a database is to add a SQL Server login for a Win2K or NT 4.0 group as we did earlier, then make the SQL Server login part of a SQL Server role that has the permissions to the database that the group needs. To add an existing login to a role, perform the following steps:

If you used these steps to add the NorthWindReaders login created earlier to the db_datawriter role, SQL Server would now be able to authenticate users in the NorthWindReaders group and authorize them to read data from and write data to the Northwind database. You could open the properties for the SQL Server login and clear the Northwind database entry on the Database Access page, and users in NorthWindReaders would still have access to Northwind because they're still in the db_datareader and db_datawriter roles for Northwind. However, the NorthWindReaders group doesn't have insert, update, or delete permission because you haven't granted those permissions. You could, of course, create another login and assign it to another role that provides more authority (such as db_owner). Table 1 shows the set of fixed roles that's attached to each SQL Server database.

What if you want better control over security than simply letting a login have access to an entire database? You can edit the role selections you made when you created the login or add your own roles with custom permissions. The roles you create can limit or grant access to specific tables and even to specific columns. To set up your own roles for a database, right-click the Roles folder and select New Database Role. Click Add to add users just as you would with a fixed role, and click Permissions to access the tables, views, stored procedures, and columns that you can set permissions to.

Making SQL Server and IIS work together to use Windows security isn't complex, unless you're trying to go through a proxy server or firewall or use certificates. SQL Server's security is pretty simple to set up because of SQL Server's fixed roles, the ability to create your own roles, and the ability to map roles to Win2K and NT 4.0 user groups. I like user groups because they let me easily set up the permissions for a group of users instead of one user at a time. I can set up a group as the SQL Server login identity, then simply add a user to or remove the user from a group to change the user's authority.

However, you must carefully think through your SQL Server security plan. For instance, if you decide to map Win2K or NT 4.0 groups to SQL Server logins, don't also create SQL Server logins for individuals in the groups. If you restrict users to only Win2K or NT 4.0 group access, you have fewer points of entry to control. I addressed the mechanics of SQL Server's security settings in this article, but security policies and implementation are a separate topic for another time.