PRB: Accessing SQL Database Fails on Second Attempt

 Last reviewed: December 9, 1997
Article ID: Q166659
The information in this article applies to:
  • Microsoft Visual InterDev, version 1.0

SYMPTOMS

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

   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:

  • Active Server Pages that have forced NT LanMan (NTLM) authentication either by disabling "Allow Anonymous" and enabling "NT Challenge/Response" or by setting Web permissions that would force a 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, 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 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 NT, causing the error in the Web browser.

RESOLUTION

There are two relatively easy workarounds:

  1. 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.

  2. 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:

    a. 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.

    b. 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.

STATUS

Microsoft is researching this behavior and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Place the following script in a project.

  2. Make sure that the Allow Anonymous setting in IIS is turned off.

  3. Open the script from second machine (not the IIS Machine).

The script runs correctly and shows the first record.

  1. Click Refresh.

The following error appears: error '80004005'.

  1. Click Refresh again.

The "Microsoft OLE DB Provider for ODBC Drivers error '80040e21'," appears.

Following is the sample code: 

   <%@ 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 "DRIVER={SQL Server};
    SERVER=ISSQL65;UID=sa;PWD=;
    APP=Microsoft (R) Developer   Studio;WSID=ISLAB11;DATABASE=ISCon", "sa"
   Set cmdTemp = Server.CreateObject("ADODB.Command")
   Set DataCommand1 = Server.CreateObject("ADODB.Recordset")
   cmdTemp.CommandText = "dbo.""Table1"""
   cmdTemp.CommandType = 2
   Set cmdTemp.ActiveConnection = ISSQL65
   DataCommand1.Open cmdTemp, , 1, 3

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

   <% Response.Write DataCommand1.EOF & " " & DataCommand1.BOF & "<br>"
   DataCommand1.MoveNext%>
   <%= DataCommand1("Email") %><br>
   <%= DataCommand1("Fname") %><br>
   <%= DataCommand1("Lname") %><br>

   <% Set Session("rsConn") = DataCommand1 %>

   </BODY>
   </HTML>

REFERENCES

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site: 

   http://support.microsoft.com/support/vinterdev/


Keywords          : VIASP VIServer
Version           : 1.0
Platform          : WINDOWS
Issue type        : kbprb

================================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.