Knowledge Base

PRB: Connection to SQL Server from Session_OnEnd() Event May Fail

Article ID: 247968

Article Last Modified on 7/16/2001

APPLIES TO

Microsoft Active Server Pages 4.0

This article was previously published under Q247968

SYMPTOMS

When connecting to a Microsoft SQL Server database from within the Active Server Pages (ASP) event, Session_OnEnd(), the connection to the SQL Server computer may fail with one of the following errors:

Error Number: -2147467259 Error Description: [Microsoft][ODBC SQL Server Driver]Client unable to establish connection

-or-

Error Number: -2147467259 Error Description: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile())

This problem can occur if all of the following conditions are true:

You are trying to establish the connection to SQL Server using the Named Pipes network library.
The SQL Server is on a separate computer from the Internet Information Server (IIS) server.
You are trying to establish the connection to the database from within the Session_OnEnd() event of ASP.

CAUSE

The cause of this problem is twofold. First, when running in the Session_OnEnd() event, the execution takes place under a different user context than in the Session_OnStart() and regular ASP pages. This is a known issue with ASP and is described in more detail in the following Microsoft Knowledge Base article:

243828 BUG: Session_OnEnd Changes Security Context of InProcess Component

Second, the Named Pipes network library is being used to connect to the SQL Server computer. When you connect to SQL Server from the ASP page via Named Pipes, the client computer must be validated by SQL Server to use the Named Pipe. If the SQL Server computer is unable to validate (authenticate) the incoming user, then the connection request will fail. This applies even if the connection to SQL Server is being attempting using standard authentication. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

159976 Connect to the Microsoft SQL Server via Named Pipes

RESOLUTION

To resolve this problem, follow these steps:

Switch the network library that the client code is using to connect to SQL Server to Transmission Control Protocol/Internet Protocol (TCP/IP). If you use TCP/IP to connect to SQL Server, then the authentication noted earlier does not have to take place. You can do this by configuring the "client configuration" option of the data source name (DSN) that you are using to connect to SQL Server (if you are using a DSN) or by adding a parameter to the connection string telling it to connect using the TCP/IP library. The following Knowledge Base article describes how to add this parameter to the connection string:

238949 Set the SQL Server Network Library in an ADO Connection

In addition, you must make sure that SQL Server is configured to listen on the TCP/IP protocol. You can do this by using the Server Network Utility with SQL Server 7.0 or through Setup for SQL Server 6.5. For more information on this, please see the SQL Server Books Online.
If your Web application is running out of process, then the Session_OnEnd() event will fire in the security context of the local IWAM_[IIS computer name]. In this scenario, you can also work around the problem by adding a local user account on the SQL Server computer that corresponds to the IWAM_[IIS computer name] account. This local user account on the SQL Server computer must have an identical user name and password.

REFERENCES

SQL Server Books Online

Additional query words: Kbgrpmdac

Keywords: kberrmsg kbdatabase kbprb KB247968

Microsoft KB Archive/247968

Contents