FIX: Dynamic IP Addresses Cannot Connect to SQL Server 2000 (306199)

SYMPTOMS

When you attempt to open a connection to a computer that is running SQL Server 2000 over a virtual private network (VPN) connection, the attempt may fail with the following error message:

SQL Server does not exist or access denied

In general, SQL Server does not respond to any new IP addresses that connect to the computer after the SQL Server 2000 service starts. If you stop the SQL Server server and then restart the server after you make the VPN connection, you can successfully connect to the server.

CAUSE

When the SQL Server 2000 service starts, it enumerates all IP connections to the computer. It then binds to the IP addresses directly and, unlike the Microsoft SQL Server 7.0 service, no longer listens on the wildcard IP address 0.0.0.0.

RESOLUTION

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

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

For additional information, click the article number below 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 Microsoft 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
 --------------------------------------------------------------------
 31-AUG-2001   2000.080.0442.00   86,588     Ssnetlib.dll   x86

MORE INFORMATION

One situation where you can encounter this problem is when you use replication with updating subscribers. Suppose you create a VPN connection from the subscriber computer to a private network where the publisher exists after starting the subscriber SQL Server instance. When the distribution agent runs on the publisher and attempts to connect to the subscriber, the connection fails because SQL Server did not bind the TCP port to the newly created VPN IP address. TCP/IP connection attempts from the publisher to the subscriber fail with the "SQL Server does not exist" error message until you stop SQL Server and then restart SQL Server on the subscriber.

The server-side TCP/IP sockets net-library Ssnetlib.dll was originally changed from listening on the wildcard IP address 0.0.0.0 in SQL Server 7.0 to enumerating the IP addresses and binding the port and IP addresses in SQL Server 2000. For this reason, SQL Server does not listen to IP addresses obtained after SQL Server starts. With Microsoft SQL Server 2000 Service Pack 2 (SP2) installed, SQL Server uses the older method of binding to the wildcard IP 0.0.0.0, which allows dynamic IPs to work without needing to restart SQL Server.