Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 300420 - Last Review: September 26, 2005 - Revision: 5.3
FIX: Connection to SQL Server Database Using IP Address Is Unusually Slow
This article was previously published under Q300420
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.
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.
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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=290211/EN-US/
)
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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=300635/EN-US/
)
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
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.
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.
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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=164213/EN-US/
)
Description of DNS Reverse Lookups
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
| kbhotfixserver kbqfe kbbug kbfix kbmdac260sp2fix kbsqlserv2000presp2fix KB300420 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate