Setting SQL Server 7.0 and IIS Security

Improve security and connectivity performance

Using default settings makes installing Microsoft IIS and SQL Server faster and easier, but accepting the default for SQL Server 7.0 authentication might put your database's security at risk. Because SQL Server 7.0 contains security loopholes, using Windows 2000 or Windows NT authentication is a better choice than SQL Server authentication. Also, people often use IIS with Web pages that connect to SQL Server through ODBC links in the ADO programming model. That approach works, but it isn't the most efficient method. In this article, I present steps you can take to improve SQL Server security and connectivity.

First, let's cover your authentication options. You have two choices when you configure authentication in SQL Server 7.0—Windows NT Authentication Mode or Mixed Mode. Microsoft strongly recommends that you use NT authentication. In fact, SQL Server 2000 defaults to NT Authentication during setup. The white paper "Microsoft SQL Server 7.0 Security" (http://www.microsoft.com/sql/techinfo/dupsecurity.doc) contains more information about setting authentication. Hacking tools that are available on the Internet can let a rogue employee who is inside your firewall—or anyone who can get through your firewall—replace your sa account password, log in, create a new account with sa privileges, log out, and replace your previous sa password. So, if you keep SQL Server authentication turned on, you might let an intruder acquire full control of your database. Microsoft's official response to this vulnerability states that you need to turn off SQL Server authentication entirely. (The article "SQL 7.0 SA Password Attack" at http://www.ntsecurity.net/go/load.asp?iD=/security/sql701.htm gives more details.)

To use SQL Server with NT authentication, first create NT accounts that your Web pages can use, then give these accounts the SQL Server permissions they'll need. Next, convert the pages to use the appropriately mapped NT accounts when users connect anonymously. After you convert the Web pages to NT authentication, reconfigure SQL Server to use NT authentication only. Make these changes on your development servers first, and after you ensure that the configuration works properly, repeat the process on production servers.

To convert your Web pages so that they can use NT accounts, you need to set your code to use trusted connections, as I will demonstrate. If you're using connection objects that you've put in DLLs, and you've installed those DLLs in Microsoft Transaction Server (MTS) as COM+ components, you have an easier job than if you've coded connections in each Web page. You just need to change the source code to use a trusted-connection string, recompile the code, and upgrade your MTS component. If you have Web pages that use ADO connection objects in each page, you'll need to edit each one. A directory-level search-and-replace tool can save time if you have many pages.

Using Trusted Connections

So why can't you substitute an NT username and password for a SQL Server login and password in your connection string? SQL Server doesn't support connections that explicitly list an NT username and password in a connection string. The closest you can come is to use a trusted connection, which means a person or process must already be logged in to a valid NT account, and SQL Server uses that account's credentials and permissions for the connection. For IIS-served Web pages that make database connections that the public uses, that NT account is the anonymous user account assigned to each Web page.

Every Web page on an IIS server either uses anonymous access or requires a login and password. Pages that require Windows logins are secure, but for public Web pages, you need to choose which Windows account IIS will use when users request a Web page. By default, the IUSR_servername local account on the IIS server is the one mapped. If your Web site needs only one common set of permissions for all pages, that default user account might be the only one you need.

If you need more than one set of permissions, you can assign different Windows accounts to individual Web pages, for example, page1.asp's anonymous user account can be User1, whereas page2.asp's account can be User2. You can change the anonymous logon account for the entire site, for all pages within one directory, or for each Web page separately. Web pages that IIS serves can use any combination of accounts between directories and pages but can use only one account per page. Of course, if you need multiple sets of permissions within a page, you must require logins so that you can use conditional code that varies output depending on who's logged in. Listing 1 shows an example in VBScript.

To change the anonymous user account on NT 4.0 with IIS 4.0, open the Internet Information Service Manager console and drill down to a Web site directory or page. Choose a directory or page and right-click, click Properties, then select the Directory Security tab, as Figure 1 shows. Click the Edit button in the Anonymous Access and Authentication Control pane, and select Allow Anonymous Access, as Figure 2 shows. Then click the Edit button to bring up the Anonymous User Account window, which Figure 3 shows. Click Browse to choose a new account. In SQL Server, you need to specify for the Windows account you choose the permissions that the stored procedures require and the queries that the Web page calls.

Be aware that when you change an anonymous user account to an NT domain account, the Enable Automatic Password Synchronization feature won't work. You'll need to turn it off and manually enter the password for the account. Automatic synchronization works only with accounts that are local to the Web server.

Now you're ready to create trusted-connection strings. To create these strings with Visual Basic (VB), first create a standard project, select References, and add the Microsoft OLE DB Service Component 1.0 Type Library. Then add the following code:

Dim objDataLink As New DataLinksDim strConn$strConn = objDataLink.PromptNew

When you run the code, PromptNew will open the Data Link Properties window that Figure 4 shows. For more information about creating connection strings, see the Microsoft article "HOWTO: Use Data Links to Create a Connection String at Run-Time" (http://support .microsoft.com/support/kb/articles/q218/6/00.asp).

Next, you need to choose a data provider. Here is a performance tip you'll want to use if you usually connect to data with OLE DB: Choose the Microsoft OLE DB Provider for SQL Server rather than the Microsoft OLE DB Provider for ODBC Drivers to eliminate the unnecessary ODBC layer and improve performance.

Next, select the Connection tab or click the Next button, enter your database server name, choose Use Windows NT Integrated Security, then enter the name of the database, as Figure 5 shows. By clicking the Test Connection button, you can test access to the server and database using your NT account.

However, the string that the method behind the button builds won't include a username or password, and whenever a user invokes the string by clicking the button, the string uses whatever account it's under the authority of. In the case of Active Server Pages (ASP), that account is the anonymous user account. The fact that these connection strings don't include usernames and passwords further enhances security by keeping that information from anyone who can access the Web page's source code.

If you're running your VB project in debug mode, clicking OK will return you to the project, and the strConn variable will contain your new string. For example, an OLE DB trusted-connection string might look like this:

Provider=SQLOLEDB.1;Integrated Security=SSPI;PersistSecurity Info=False;Initial Catalog=Sales;Data Source=Server2

where Data Source is the server name, and Initial Catalog is the database name. Here's an example of an ODBC connection string:

DSN=Sales;SERVER=Server2;APP=VisualBasic;WSID=WS2;DATABASE=Sales;QueryLogFile=Yes

Now, put your new trusted-connection string in your objects or Web pages. Putting your connection strings into compiled code in MTS objects is far better than putting the strings in scripts in a Web page, both for security and for performance. But if you're unfamiliar with MTS, OLE DB trusted-connection strings in your in-page scripts, which Listing 2 demonstrates, will improve performance and security compared with ODBC connections with SQL Server accounts.

When you finish putting the new connection string in place, and you're no longer using SQL Server accounts, you're ready to reconfigure SQL Server to use NT authentication, which is quick and easy. Open Enterprise Manager, right-click the server, select Properties, then select the Security tab. As Figure 6 shows, under Authentication, select Windows NT only, and click OK for each successive screen. Stop and restart SQL Server so that the change will take effect.

By following these steps, you'll upgrade your SQL Server security and improve data connection performance.