Data Access and Transactions

Security and SQL Server

For SQL Server, permissions issues multiply, especially if SQL Server and IIS 5.0 are running on different computers. By default, connections to a SQL Server use a service of the Windows 2000 operating system known as a “named pipe.” In order for a SQL server client to gain access to a Windows 2000 named pipe, the client needs to be validated by the server. This is normally accomplished either by means of a Workgroups-style validation (identical user names and passwords are created on the client and the server), or by using the domain method (both the client and server are domain members).

The SQL Server connection uses the identity of the user associated with the Web connection. If the connection is anonymous, you need to create a guest account that corresponds to the IUSR_computername account. If this guest account already exists, then make sure it has rights to log onto the SQL Server computer.

Support for the anonymous user account can be configured by any of the following means:

Add the IUSR_computername account to the local user account database on the server that hosts SQL Server.
Make the account a member of the domain that SQL Server resides in.
Enable the Windows 2000 Guest user account on the remote SQL Server computer.

If you have configured IIS 5.0 to use integrated Windows authentication in the Windows 2000 operating system (by either disabling Anonymous access, or forcing a logon by returning a “401 Access Denied” response), IIS 5.0 tries to connect to the SQL Server by using the user’s security context. See the figure below. If SQL Server resides on a separate computer from IIS 5.0, the Windows 2000 operating system detects the attempt to use a network named pipe handle that had been opened in a different user context. It then forces the pipe closed, according to its security rules.

The following OLE DB error is an indication of this problem:

Microsoft OLE DB provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][DBNMPNTW]ConnectionWrite (GetOverLappedResult()).

If SQL Server is running on the same server as IIS 5.0, use a local named pipe connection instead of a network named pipe connection. In the SQL Server connection string or in the DSN configuration, change “SERVER=computername” to “SERVER=(local).” The name “(local)” (with parentheses) is a special keyword to the SQL Server ODBC driver, and indicates that a local connection should be used.

If SQL Server is running on a different server than IIS 5.0, you can use a nonauthenticated protocol between IIS 5.0 and SQL Server, such as Transmission Control Protocol/Internet Protocol (TCP/IP) sockets. To use these, you must configure both the SQL Server and the SQL Server client on the IIS 5.0 server.

To set up a TCP/IP connection on the server hosting SQL Server

Run SQL Server Setup.
In the Options dialog box, click Change Network Support, and click Continue.
Select the entry for TCP/IP Sockets (leave Named Pipes selected also) and click OK. Accept the default Named Pipe name and TCP/IP Socket number.
Exit SQL Server Setup. Stop and restart the SQL Server.

To set up a TCP/IP connection on the server hosting IIS 5.0

From Control Panel, point to Administrative Tools. Select Data Sources (ODBC). Select a SQL Server data source, and click Configure to start the SQL Server DSN Configuration Wizard. Click Next, then click Client Configuration. The figure below shows the resulting dialog box.
Select the Network Library tab, and select TCP/IP as the default network protocol. Click OK. IIS 5.0 will now use TCP/IP sockets when connecting to the SQL Server specified in this DSN.