When you connect to the SQL Server 2005 database engine, you may experience longer connection times (918478)


The information in this article applies to:

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition

SYMPTOMS

When you connect to the Microsoft SQL Server 2005 database engine, you may experience longer connection times than you expect for a SQL Server 2000 connection. This behavior may occur when all the following conditions are true:
  • The client program uses Microsoft Data Access Component (MDAC) 2.8 or earlier versions to connect to the SQL Server 2005 database engine. For example, the client program uses the SQL Server ODBC driver or the OLE DB provider for SQL Server in MDAC 2.8.
  • The TCP/IP protocol is disabled in SQL Server 2005.
  • The shared memory protocol is enabled on the client computer and on the computer that is running SQL Server 2005.

CAUSE

The shared memory protocol that is used in MDAC is incompatible with the shared memory protocol that is used by SQL Server 2005. When a client computer tries to connect to SQL Server 2005 by using the shared memory protocol, the client computer is automatically switched to the TCP/IP protocol for the connection. However, because TCP/IP is disabled, a delay occurs. The client computer tries to use the TCP/IP protocol for approximately one second and then connects by using the named pipe protocol. SQL Server 2005 provides MDAC client computers with local named pipe connectivity if the shared memory protocol is enabled in SQL Server 2005.

WORKAROUND

To work around this problem, use one of the following methods.

Method 1

Use the SQL Server Native Client (SQL Native Client) to connect to SQL Server 2005 instead of using MDAC.

Method 2

Enable the TCP/IP protocol in SQL Server 2005. To do this, follow these steps:
  1. Open SQL Server Configuration Manager.
  2. Expand SQL Server 2005 Network Configuration.
  3. Click Protocols for SQLInstanceName. SQLInstanceName is a placeholder for the name of the instance of SQL Server 2005.
  4. Enable TCP/IP.

Method 3

On the client computer, create an alias for SQL Server 2005 client computers in SQL Server Configuration Manager, and then specify the named pipe protocol explicitly for this alias. To do this, follow these steps:
  1. Open SQL Server Configuration Manager.
  2. Expand SQL Native Client Configuration.
  3. Right-click Alias, and then click New Alias.
  4. For the Protocol option, specify Named Pipes, and then provide the information for Alias Name, for Pipe Name, and for Server.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Modification Type:MajorLast Reviewed:5/18/2006
Keywords:KBsql2005connect kbSqlClient kbExpertiseAdvanced kbtshoot kbprb KB918478 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.