Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
How to troubleshoot connectivity issues in SQL Server 2000
Article ID: 827422 - View products that this article applies to.
This article can help you to resolve connectivity problems with Microsoft SQL Server 2000. This article contains descriptions of common connectivity problems and the steps that you can take to help resolve your connectivity problems.
SQL Server 2000 supports several methods of communication between the instance of SQL Server and the client applications. If your client application and the instance of SQL Server reside on the same computer, Microsoft Windows interprocess communication (IPC) components, such as local named pipes or the Shared Memory protocol, are used to communicate. However, when the client application and the instance of SQL Server reside on different computers, a network IPC, such as TCP/IP or named pipes, is used to communicate.
SQL Server 2000 uses Net-Library, a DLL, to communicate with a particular network protocol. A matching pair of Net-Libraries must be active on the client computer and the server computer to support the network protocol that you want to use. For example, if you want to enable a client application to communicate with a specific instance of SQL Server across TCP/IP, the client TCP/IP Sockets Net-Library (Dbnetlib.dll) must be configured to connect to the server on the client computer. Likewise, the server TCP/IP Sockets Net-Library (Ssnetlib.dll) must listen on the server computer. In this scenario, the TCP/IP protocol stack must be installed on both the client computer and the server computer.
After you install SQL Server 2000, you can configure the properties of the client Net-Libraries by using Client Network Utility. You can configure the properties of the server Net-Libraries by using Server Network Utility (Svrnetcn.exe). The server Net-Libraries are installed during the installation of the server tools in SQL Server Setup. However, some of the server Net-Libraries may not be active. By default, SQL Server 2000 enables and listens on TCP/IP, named pipes, and Shared Memory. Therefore, for a client to connect to a server computer, the client must connect by using a client Net-Library that matches one of the server Net-Libraries that is currently being used by the instance of SQL Server.
For additional information about SQL Server communication components and Net-Libraries, see the following topics in SQL Server Books Online:
Important Before you start to troubleshoot connectivity issues in SQL Server 2000, make sure that the MSSQLServer service is started on the computer that is running SQL Server.
SQL Server does not exist or access denied
General Network Error
To verify that the name resolution process is resolving the correct server, you can ping the server by using the server name and the IP address of the server. To do so, follow these steps:
Cannot Generate SSPI Context
For additional information about the ipconfig command, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/223413/ )Options for Ipconfig.exe in Windows 2000
To work around this problem, add an entry for the server to the %systemroot%\system32\drivers\etc\hosts file on the client computer. You can also work around the problem by connecting to the server by using the Named Pipes Net-library.
Note Earlier versions of Microsoft Data Access Components (MDAC) have a different user interface for Client Network Utility. Therefore, if you do not see the options that are listed in this article, install a later version of MDAC on the computer that is running the client application.
2003-11-06 09:49:36.17 server SQL server listening on TCP, Shared Memory, Named Pipes. 2003-11-06 09:49:36.17 server SQL server listening on 192.168.1.5:1433, 127.0.0.1:1433.
If you analyze the entries in the SQL Server error log file, you can verify that the instance of SQL Server is listening on the correct IP address and on the correct port. By default, a default instance of SQL Server listens on the port 1433. You can also use Server Network Utility to verify the protocol settings for SQL Server and to change the properties in SQL Server, including the protocols that can connect to SQL Server and the ports that can be used. For more information about using Server Network Utility, see the "SQL Server Network Utility" topic in SQL Server Books Online.
Sometimes, SQL Server 2000 may not bind to port 1433 or any other specified port. This problem may occur if the port is being used by another application or if you are trying to connect by using an IP address that is not correct. Therefore, the TCP/IP connections to SQL Server may not be successful and you may receive the following error message in the SQL Server error log file:
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
2001-11-14 15:49:14.12 server SuperSocket Info: Bind failed on TCP port 1433.
(http://support.microsoft.com/kb/307197/ )PRB: TCP\IP port in use by another application
If you cannot connect to the instance of SQL Server by using a TCP/IP connection, try to use the named pipes protocol or the Shared Memory protocol. Run the following command at a command prompt to obtain information about the ports that are in use:
You can also use the Portqry command-line utility to obtain more information about the ports that are in use.
For additional information about the Portqry command-line utility, click the following article number to view the article in the Microsoft Knowledge Base:
310099For additional information about a possible bug that is related to TCP/IP sockets, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/310099/ )Description of the Portqry.exe command-line utility
(http://support.microsoft.com/kb/306865/ )BUG: SQL Server may not listen on TCP/IP sockets When TCP/IP is the only protocol
Note For named instances of SQL Server, SQL Server dynamically determines the port and listens on the determined port. Therefore, when you start the named instance of SQL Server, SQL Server tries to listen on the port that was previously being used. If SQL Server cannot bind to that port, the named instance may dynamically bind to a different port. In that situation, make sure that the client application is also set to determine the port dynamically. Alternatively, you can also specify a static port for the named instance to bind to and to listen on by using Client Network Utility.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/286303/ )INF: Behavior of SQL Server 2000 network library during dynamic port detection
(http://support.microsoft.com/kb/823938/ )How to use static and dynamic port allocation in SQL Server 2000
For additional information about how to determine the version of MDAC, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/301202/ )HOW TO: Check for MDAC version
Note If you are connecting to a named instance of SQL Server, make sure that you are running MDAC 2.6 or later on your computer. Earlier versions of MDAC do not recognize named instances of SQL Server. Therefore, connections to named instances may not be successful.
You can use the Odbcping.exe utility to verify connections through the SQL Server ODBC driver.
For additional information about Odbcping.exe, click the following article number to view the article in the Microsoft Knowledge Base:
138541For additional information about configuring ODBC DSNs, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/138541/ )HOW TO: Odbcping.exe to verify ODBC connectivity to SQL Server
(http://support.microsoft.com/kb/289573/ )PRB: Configuring DSNs with SQL Server Net-Libraries
You can also test connectivity to the instance of SQL Server by using a .udl file.
For additional information about how to create a .udl file, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/244659/ )SAMPLE: How to create a data link file with Windows 2000
For additional information about the ports that must be open to communicate through a firewall, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/287932/ )INF: TCP ports needed for communication to SQL Server through a firewall
(http://support.microsoft.com/kb/269882/ )HOWTO: Use ADO to connect to a SQL Server that is behind a firewall
If you use the TCP/IP protocol to connect to the instance of SQL Server, make sure that you can use the Telnet program to connect to the port where SQL Server is listening. To use the Telnet program, run the following command at a command prompt:
If the Telnet program is not successful and you receive an error message, resolve the error and then try to connect again.
Note Because of issues that were caused by the Slammer virus, the User Datagram Protocol (UDP) port 1434 may be blocked on your firewall.
Login failed for user '<username>'
Login failed for user 'NTAUTHORITY\ANONYMOUS LOGON'
If you receive an error message because of an authentication failure and the error message does not mention a specific SQL Server login name, troubleshoot the problem with Windows authentication. You may receive the following error message because of problems with Windows authentication:
Login failed for user 'null'
The following problems may cause authentication and security issues:
Cannot generate SSPI Context
811889If the connection is not successful when you use SQL Server Authentication, you receive the following error message:
(http://support.microsoft.com/kb/811889/ )How to troubleshoot the "Cannot generate SSPI context" error message
Login failed for user '<username>' . Not associated with a trusted connection
To troubleshoot this problem, follow these steps.
Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/322756/ )How to back up and restore the registry in Windows
(http://support.microsoft.com/kb/316898/ )HOW TO: Enable SSL encryption for SQL Server 2000 with Microsoft Management Console
(http://support.microsoft.com/kb/322144/ )FIX: SECDoClientHandShake cannot connect to SQL Server
2003-08-07 20:46:21.11 server Error: 17832, Severity: 20, State: 6
2003-08-07 20:46:21.11 server Connection opened but invalid login packet(s) sent. Connection closed.
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/154628/ )INF: SQL logs 17832 with multiple TCP\IP connection requests
(http://support.microsoft.com/kb/328476/ )TCP/IP settings for SQL Server drivers when pooling is disabled
Note You may not notice the stress on TCP/IP sockets if you are running SQL Server 2000 SP3 or SQL Server 2000 SP3a because a limit on the number of login packets was added. The 17832 error occurs when you use third-party drivers to connect to the instance of SQL Server. To resolve this problem, contact the third-party vendor and obtain drivers that have been tested to work with SQL Server 2000 SP3 and SQL Server 2000 SP3a.
This error generally occurs during SQL Cluster Setup and service pack setup when the setup process starts the instance of SQL Server in single-user mode. The specified applications may automatically connect to the instance of SQL Server using the only available connection, and setup is not successful.
SQL Server does not exist or Access Denied
To determine if the instance of SQL Server has been started in single-user mode, check to see if the SQL Server error log file has an entry that is similar to following:
2003-07-31 11:26:43.79 spid3 Warning ******************
2003-07-31 11:26:43.80 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.
(http://support.microsoft.com/kb/68941/ )INF: Procedure for testing named pipes
The SQL Server Agent service may not start because it waits for SQL Server to recover the databases. Therefore, when you receive the following message in the SQL Server error log file, the connections will no longer fail with a timeout error: If the recovery process takes a long time, you may have to additionally troubleshoot the recovery process.
(http://support.microsoft.com/kb/148942/ )How to capture network traffic with Network Monitor
(http://support.microsoft.com/kb/294818/ )Frequently asked questions about Network Monitor
(http://support.microsoft.com/kb/169292/ )The basics of reading TCP/IP traces
(http://support.microsoft.com/kb/102908/ )How to troubleshoot TCP/IP connectivity with Windows 2000 or Windows NT
To obtain more detailed information, you may have to use SQL Profiler traces. You can also use the Network Diagnostics Tool for non-clustered computers that are running SQL Server for network tracing. For additional information about how to use the Network Diagnostics Tool , click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/321708/ )HOW TO: Use the Network Diagnostics Tool (Netdiag.exe) in Windows 2000
For additional information about various connectivity problems related to SQL Server, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/812817/ )Support WebCast: Microsoft SQL Server 2000: Troubleshooting connectivity
(http://support.microsoft.com/kb/319930/ )HOW TO: Connect to Microsoft Desktop Engine
(http://support.microsoft.com/kb/257434/ )INF: Network library in existing DSN replaced by network library in new DSN to the same SQL Server name
(http://support.microsoft.com/kb/306985/ )RPC errors when connecting a cluster virtual server with named pipes
(http://support.microsoft.com/kb/313062/ )HOW TO: Connect to a database by using Active Server Pages in Windows 2000
(http://support.microsoft.com/kb/313295/ )HOW TO: Use the server name parameter in a connection string to specify the client network library
(http://support.microsoft.com/kb/320728/ )HOW TO: Resolve "The maximum connection limit has been reached" error message
(http://support.microsoft.com/kb/328306/ )INF: Potential causes of the "SQL Server does not exist or access denied" error message
(http://support.microsoft.com/kb/247931/ )INF: Authentication methods for connections to SQL Server in Active Server Pages
(http://support.microsoft.com/kb/169377/ )How to access SQL Server in Active Server Pages
(http://support.microsoft.com/kb/328383/ )INF: SQL Server clients may change protocols when they try to connect
(http://support.microsoft.com/kb/238949/ )HOWTO: Set the SQL Server network library in an ADO connection string
(http://support.microsoft.com/kb/270126/ )PRB: How to manage client connectivity to both SQL Server 2000 virtual servers after an active/active cluster 7.0 upgrade
(http://support.microsoft.com/kb/316779/ )PRB: Clients with Force Protocol Encryption set on may fail to connect with an IP address
(http://support.microsoft.com/kb/216643/ )INF: ODBC/OLEDB connect options not seen when tracing connections to SQL Server 7.0
(http://support.microsoft.com/kb/265808/ )INF: How to connect to an SQL Server 2000 named instance with the previous version's client tools
(http://support.microsoft.com/kb/191572/ )INFO: Connection pool management by ADO objects called from ASP
(http://support.microsoft.com/kb/313173/ )Sample connection pool manager for use with Microsoft SQL Server 2000 driver for JDBC
(http://support.microsoft.com/kb/237844/ )HOWTO: Enable ODBC connection pooling in a Visual Basic ADO application
(http://support.microsoft.com/kb/259569/ )PRB: Installing third-party product breaks Windows 2000 MDAC registry settings