Microsoft KB Archive/300420

= FIX: Connection to SQL Server Database Using IP Address Is Unusually Slow =

Article ID: 300420

Article Last Modified on 9/26/2005

-

APPLIES TO


 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.6 Service Pack 1
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7
 * Microsoft OLE DB Provider for SQL Server 2000 2000.80.194
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q300420



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. &quot;In certain reported cases, a &quot;Timeout expired&quot; 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= and Password= with the correct values. ' Make sure that User ID has the appropriate permissions to perform this operation on the database. conn.Open &quot;Provider=SQLOLEDB;Server=123.123.123.123;User ID= ;&quot; & _ &quot;;Password= ;Initial Catalog=Pubs;&quot; 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

Additional query words: sql server connection slow dns lookup ip internet hosts lmhosts timeout tcp/ip S1T00 20084

Keywords: kbbug kbfix kbqfe kbsqlserv2000presp2fix kbmdac260sp2fix kbhotfixserver KB300420

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.