FIX: Connection to SQL Server Database Using IP Address Is Unusually Slow (300420)

SYMPTOMS

When Microsoft Data Access Components (MDAC) version 2.6 is installed, each attempt to connect to a SQL Server 7.0 or SQL Server 2000 database using an IP address (rather than the server name) may take longer than anticipated. "In certain reported cases, a "Timeout expired" error is reported to the client application and the connection attempt fails.

Be aware that if you are using a server alias which maps to a TCP/IP address, this problem can still occur.

This problem does not occur with MDAC 2.5 or 2.1 installed.

CAUSE

The MDAC 2.6 version of the SQL Server Network Library, Dbnetlib.dll, attempts to determine the host name of the IP address using a reverse lookup. If the client computer has a slow Domain Naming Service (DNS) server, or no DNS server, and the local HOSTS or LMHOSTS file on the computer does not contain the host name for the IP address of the SQL Server, the reverse lookup will time out after five seconds.

Note that this is an internal timeout inside of the SQL Server driver code only, and it may or may not generate a timeout error in the client application. The connection attempt can succeed, but every new connection made by the application will encounter the same five-second delay.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.6. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

300635 INFO: How to Obtain the Latest MDAC 2.6 Service Pack

Hotfix

NOTE: The following hotfix was created prior to SQL Server 2000 Service Pack 2.

The English version of this fix should have the following file attributes or later:

 Date         Version           Size    File name        Platform
 ---------------------------------------------------------------
 19-JUN-2001  2000.080.0304.00  86,082  Dbnetlib.dll     x86

WORKAROUND

To work around this problem, either provide some reliable means for performing a reverse lookup of the SQL Server server's IP address, use the SQL Server machine name instead of the IP address, or apply this hotfix.

To use the HOSTS file or LMHOSTS, put the TCP/IP address of the SQL Server server at the start of the line followed by a few spaces followed by the machine name of the SQL Server server. For example:

123.123.123.123 myservername

The HOSTS or LMHOSTS file belongs in the System32\Drivers\Etc directory for client computers running Microsoft Windows NT and Microsoft Windows 2000, or in the \Windows directory for clients running Microsoft Windows ME, Microsoft Windows 98, or Microsoft Windows 95.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in SQL Server 2000 Service Pack 2 and MDAC 2.6 Service Pack 2.

MORE INFORMATION

To reproduce this problem, run the following Microsoft Visual Basic code which connects to SQL Server by way of an IP address (rather than using the SQL Server machine name):

Dim conn as ADODB.Connection

Set conn = New ADODB.Connection
' Replace 123.123.123.123 with the IP address of your SQL Server here.
' Replace User ID=<username> and Password=<strong password> with the correct values. 
' Make sure that User ID has the appropriate permissions to perform this operation on the database.
conn.Open "Provider=SQLOLEDB;Server=123.123.123.123;User ID=<user name>;" & _
          ";Password=<strong password>;Initial Catalog=Pubs;"

Note that this problem has primarily been reported when using MDAC 2.6 in conjunction with SQL Server 7.0.

For more information on troubleshooting problems with DNS reverse lookups, see the following article in the Microsoft Knowledge Base:

164213 Description of DNS Reverse Lookups