Using Windows Security with IIS and SQL Server 2000

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.