PRB: Accessing SQL Database Fails on Second Attempt (166659)

SYMPTOMS

One of the following error occurs when trying to access a SQL database via Active Server Pages (ASP) under Internet Informaton Server (IIS) 3.0:

Error '80004005' Microsoft OLE DB Provider for ODBC Drivers error '80040e21', Errors Occurred

-or-

80004005: ConnectionWrite(GetOverLappedResult)

The error occurs on the second access to the data. For example, using a form generated by the DataForm Wizard clicking on the ">>" button to view the next 10 records results in the error described above.

This issue occurs when all of the following conditions are met:

ASP pages that have forced Windows NT LanMan (NTLM) authentication either by disabling Allow Anonymous and enabling Windows NT Challenge/Response or by setting Web permissions that would force a Windows NT Challenge/Response.
The Web browser on a different machine than the Internet Information Server (IIS).
The recordset object stored in a Session variable.

NOTES: In this scenario, if BASIC/Clear Text is turned on and NTLM is turned off, then this script runs correctly. NTLM makes this problem surface.

CAUSE

When the allow Anonymous User context is turned off, Windows NT is closing the pipe to SQL Server after the first request is complete. This is because the first connection to SQL Server is made under the IIS Anonymous User account. IIS then either impersonates the browser client on that same thread, or tries to access the connection on a different thread that is running in the impersonated user context. In either case Windows NT would detect the attempt to use a network named pipe handle that had been opened in a different user context and force the pipe closed, per its security rules.

When the connections are viewed on the SQL Server with a network monitor, a name pipe close request comes from Windows NT, causing the error in the Web browser.

RESOLUTION

There are two relatively easy workarounds:

If SQL Server is running on the same machine as IIS, you can use a local named pipe connection instead of a network named pipe connection. NT security rules would not be forced as the pipe is a local connection, rather than a network connection that can be impersonated by the Anonymous User account. In the SQL Server connection string of the Global.asa file, change the keyword SERVER=machinename to SERVER=(local). The server name "(local)" with parenthesis is a special keyword to the SQL Server ODBC driver.
You can use a non-authenticated protocol between IIS and SQL Server, such as TCP/IP sockets. This works when SQL Server is running on either the same machine or a different machine than IIS. To do so, you must configure both the SQL Server and the SQL Server client on the IIS machine:
1. To configure SQL Server to listen on TCP/IP sockets as well as named pipes, run SQL Setup. From the Microsoft SQL Server 6.5 Options dialog box, click Change Network Support and Continue. Select the entry for TCP/IP Sockets (leave Named Pipes also selected) and click OK. Accept the default Named Pipe name and TCP/IP Socket number. Exit SQL Setup. Stop and restart SQL Server.
2. To configure the SQL Server client on the machine running IIS (the same or different machine as the SQL Server), select SQL Client Configuration Utility. Click the Net Library tab and select "TCP/IP Sockets" as the Default Network. Click Done. IIS should now use TCP/IP sockets when connecting to SQL Server.

MORE INFORMATION

Steps to Reproduce Behavior

Place the following script in an ASP page (Create a system DSN named 'Pubs' to the Pubs Database):

 <%@ LANGUAGE="VBSCRIPT" %>

   <HTML>
   <HEAD>
   <META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0">
   <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1">
   <TITLE>Document Title</TITLE>
   </HEAD>
   <BODY>

   <% If IsEmpty(Session("rsConn")) Then
   Set ISSQL65 = Server.CreateObject("ADODB.Connection")
   ISSQL65.Open "DSN=pubs;UID=sa;PWD=;"
   Set cmdTemp = Server.CreateObject("ADODB.Command")
   cmdTemp.CommandText = "authors"
   cmdTemp.CommandType = 2
   Set cmdTemp.ActiveConnection = ISSQL65
   Set RS = Server.CreateObject("ADODB.Recordset")
   RS.Open cmdTemp, , 1, 3

   Else
      Set RS = Session("rsConn")
   End If %>

   <% Response.Write RS.EOF & " " & RS.BOF & "<br>"%>
   <% If not RS.EOF Then %>
     <% RS.MoveNext %>
     <%= RS(0) %><br>
     <%= RS(1) %><br>
     <%= RS(2) %><br>
   <% Else %>
     End Of File reached on Recordset<BR>
   <% End If %>
   <% Set Session("rsConn") = RS %>

   </BODY>
   </HTML>

Make sure that the SQL Client Configuration Utility -> Net Library -> Default Network is set to Named Pipes.
Make sure that the Allow Anonymous setting in IIS is turned off.
Open the script from second machine (not the IIS Machine). The script runs correctly and shows the first record.
Click Refresh.The following error appears: error '80004005'.
Click Refresh again. The "Microsoft OLE DB Provider for ODBC Drivers error '80040e21'," appears.