server| Resolution | How the problem resolves connection problems in SQL Server 2000 apply to important notes: This article contains information about modifying the registry. Be sure to back up the registry before you modify the registry, and be sure to know how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986 Microsoft Windows Registry Description
Content Summary of this task troubleshoot connection problems Verify DNS settings verify enabled protocols and aliases verify that the SQL Server instance is listening correctly for resolving MDAC problems resolving firewall problems resolving authentication and security issues resolving pressure issues on TCP/IP sockets view SQL Ser Ver instance whether to initiate a named pipe connection to SQL Server in Single-user mode resolving a connection timeout during recovery test different ways to connect to an instance of SQL Server Capture Network Monitor traces reference Summary This article helps you troubleshoot Microsoft SQL Serve Connection problem for R 2000. This article describes common connection problems and the steps that you can take to help resolve connection problems. SQL Server 2000 supports multiple ways of communicating between SQL Server instances and client applications. If your client application and the instance of SQL Server are on the same computer, the communication (IPC) part (such as a local named pipe or shared memory protocol) between Microsoft Windows processing is used for communication. However, when client applications and instances of SQL Server are on different computers, a network IPC (such as TCP/IP or Named pipes) will be used for communication.
SQL Server 2000 uses a network library (a DLL) to communicate with a specific network protocol. A pair of matching network libraries must be active on the client computer and must be active on the server computer that supports the network protocol to be used. For example, if you want to enable a client application that communicates with a particular instance of SQL Server through TCP/IP, the client's TCP/IP Socket network library (Dbnetlib.dll) must be configured to connect to the client computer's server. Similarly, the server TCP/IP Sockets network 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 network library through the Client Network Utility. You can configure the properties of the server network library through the Server Network Utility (Svrnetcn.exe). When you install the Server tools in SQL Server Setup, the server's network library is also installed. However, some of the server's network libraries may not be activated. By default, SQL Server 2000 enables and listens for TCP/IP, named pipes, and shared memory. Therefore, if a client is to connect to a server computer, the client must use a client network library that matches one of the server network libraries that the instance of SQL Server is using.
For additional information about SQL Server communication parts and network libraries, see the following topics in the SQL Server online reference book: Communication parts client and server network library Management client
Return to the top of the page troubleshoot connection problems in SQL Server 2000, most of the connection problems you might notice are caused by problems with TCP/IP or Windows authentication, or both.
Important Before you begin troubleshooting SQL Server 2000 connection problems, verify that the MSSQLServer service is started on the computer that is running SQL Server.
Returns the IP address used by the name resolution procedure in the DNS settings Domain Name System (DNS) to resolve SQL Server instance names. If the name resolution process is not working properly, you cannot obtain an instance of SQL Server, and you may receive one or more of the following error messages:
SQL Server does not exist or access denied
General Network Error
Cannot Generate SSPI context 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 this, follow these steps: Click Start, and then click Run. In the Run dialog box, type cmd in the Open box, and then click OK. At the command prompt, run the following command:
Ping <server name>
The IP address returned by the record. At the command prompt, run the following command (the IP address here is the one you recorded in step 3):
Ping–a <ip address>
Verify that the command resolves the correct server name. If either of the two specified commands is unsuccessful, timed out, or does not return the correct value, the DNS lookup does not work properly, or there are other network or routing problems causing the problem. To view the current DNS settings, run the following command at a command prompt:
Ipconfig/all
For additional information about the ipconfig command, click the following article number to view the article in the Microsoft Knowledge Base:
223413 options for Ipconfig.exe in Windows 2000
To resolve this issue, you can add an entry for the server in the%systemroot% ystem32\drivers\etc\hosts file on the client computer. To resolve this issue, you can also use the named pipe Network library to connect to the server.
Returns the first-page validation enabled protocols and aliases if the alias on the client computer is set incorrectly, the connection problem may be caused. You can use the Client Network Utility to view aliases. To do this, follow these steps: Start the Client Network Utility. If the SQL Server Client tool is installed on the computer that is running the client application, follow these steps to start the Client Network Utility: Click Start, and then point to Programs. Point to Microsoft SQL Server, and then click Client Network Utility. If the SQL Server Client tool is not installed on the client computer, follow these steps to start the Client Network Utility: Click Start, and then click Run. In the Run dialog box, type Cliconfg in the Open box, and then click OK. In the SQL Server Client Network Utility window, click the General tab, and then enable all the protocols that you want to use.
Note: You must enable at least the TCP/IP protocol and the named pipe protocol. Click the Alias tab, and then verify the alias configured for the SQL Server instance. Verify the properties of the alias and verify that the server name or IP address and protocol are configured correctly. By using the server name, IP address, or other protocol, you can create a new alias to test connectivity performance.
Note: In earlier versions of Microsoft data Access part (MDAC), the user interface for the Client Network Utility was different. Therefore, if you do not see the options listed in this article, install a new version of MDAC on the computer where the client application is running.
Back to the top of the page verify that the SQL Server instance is listening correctly to verify that the SQL Server instance is listening correctly for Named pipes, TCP/IP, or other protocols that you use in the client application, open the current SQL Server error log file. The SQL Server error log file may include entries similar to the following:
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.
By analyzing entries in the SQL Server error log file, you can verify that the SQL Server instance is listening for the correct IP address and port. By default, a default SQL Server instance listens on port 1433. You can also use the Server Network Utility to verify the protocol settings for SQL Server and to change the properties in SQL Server, including protocols that can connect to SQL Server and the ports that you can use. For more information about using the Server Network Utility, see the "SQL Server Network Utility" topic in the SQL Server online reference book.
Sometimes, SQL Server 2000 may not be bound 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 using an incorrect IP address. As a result, TCP/IP connections to SQL Server may not be successful, and in the SQL Server error log you receive the following error message:
2001-11-14 15:49:14.12 Server SuperSocket info:bind failed on TCP port 1433. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
307197 PRB:TCP\IP port is being used by another application
If you cannot connect to an instance of SQL Server through TCP/IP connectivity, try using a named pipe protocol or a shared memory protocol. Run the following command at a command prompt to get information about the port you are using:
Netstat-an
You can also use the Portqry command-line utility to get more information about the port you are using.
For additional information about the Portqry command-line utility, click the following article number to view the article in the Microsoft Knowledge Base:
310099 Portqry.exe Command line utility description
For additional information about possible errors related to TCP/IP sockets, click the following article number to view the article in the Microsoft Knowledge Base:
306865 BUG: SQL Server may not be able to listen on TCP/IP Sockets when TCP/IP is the only protocol
Note: For SQL Server named instances, SQL Server dynamically determines the port and listens for the determined port. Therefore, when you start a SQL Server named instance, SQL Server will try to listen for the previously used port. If SQL Server cannot bind to the port, the named instance may be dynamically bound to another port. In this case, make sure that the client application is also set to dynamically determine the port. As a selection, you can also specify a static port for a named instance to bind and listen to by using the Client Network Utility.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
286303 INF: Behavior of the SQL Server 2000 network library during dynamic port detection
823938 How to use static and dynamic port assignments in SQL Server 2000
Return to the top of the page troubleshooting MDAC problems may also cause connectivity problems. For example, installing a software may overwrite some MDAC files or change permissions, and you need these permissions to access the MDAC files. You can run the MDAC part Checker to verify that MDAC is installed on your computer.
For additional information about how to determine your current version of MDAC, click the following article number to view the article in the Microsoft Knowledge Base:
301202 How to check MDAC versions
Note: If you are connecting to a named instance of SQL Server, make sure that MDAC 2.6 or later is running on your computer. Earlier versions of MDAC do not recognize named instances of SQL Server. Therefore, you may not be able to connect to a named instance.
You can use the Odbcping.exe utility to authenticate connections through SQL Server ODBC drivers.
For additional information about Odbcping.exe, click the following article number to view the article in the Microsoft Knowledge Base:
138541 How to use Odbcping.exe to verify an ODBC connection to SQL Server
For additional information about configuring ODBC DSN, click the article number below to view the article in the Microsoft Knowledge Base:
289573 PRB: Configuring DSN Using SQL Server net-libraries (SQL Server network Library)
You can also test a connection to an instance of SQL Server 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:
244659 Example: How to create a data link file in Windows 2000
Return to the top of the page troubleshoot firewall problems if the firewall exists between the client computer and the computer that is running SQL Server, verify that the port you need to communicate through the firewall is open.
For additional information about the ports that must be open when communicating through a firewall, click the following article number to view the article in the Microsoft Knowledge Base:
287932 INF: TCP Port required to communicate with SQL Server through a firewall
269882 How to use ADO to connect to SQL Server behind a firewall
If you are using the TCP/IP protocol to connect to an instance of SQL Server, verify that you can connect to the port that SQL Server is listening on by using the Telnet program. To use the Telnet program, run the following command at a command prompt:
Telnet <ip address> <port number>
If the Telnet program does not succeed and you receive an error message, resolve the error and then try to connect again.
Note: User Datagram Protocol (UDP) port 1434 may be blocked on your firewall because of problems caused by the Slammer virus.
Return to the top of the page troubleshoot authentication and security issues you may not be able to connect to SQL Server because authentication fails. If the authentication fails, you may receive one of the following error messages:
Login failed for user ' <username> '
Login failed for user ' ntauthority\anonymous LOGON '
Login failed for user ' null ' if you receive an error message because of an authentication failure, and you do not mention an explicit SQL Server login in the error message, use Windows authentication to resolve the problem. Because of a problem with Windows authentication, you may receive the following error message:
Cannot generate SSPI the following issues may cause authentication and security issues: NTLM identity authentication or Kerberos identity authentication issues. The domain controller could not be contacted due to a connection problem. There is a problem with the trust relationship between domains. For more information about possible causes, see the event log on your computer. To troubleshoot connection problems with Windows authentication, you can use SQL Server Authentication to connect to an instance of SQL Server. For additional information about how to troubleshoot and resolve the "Cannot Generate SSPI context" error, click the following article number to view the article in the Microsoft Knowledge Base:
811889 How to exclude the "Cannot generate SSPI context" error message
If the connection does not succeed when you use SQL Server authentication, you receive the following error message:
Login failed for user ' <username> '. Not associated with a trusted connection
To resolve this issue, follow these steps.
Warning: Improper use of Registry Editor can cause serious problems that may require you to reinstall your operating system. Microsoft does not guarantee that you can resolve problems caused by improper use of the Registry Editor. Using Registry Editor requires your own risk. Verify that the SQL Server instance is configured to use Windows identity authentication and SQL Server authentication. To do this, verify that the following registry key exists on the computer that is running SQL Server. For the default SQL Server instance:
HKEY_LOCAL_MACHINE Oftware\microsoft\mssqlserver\mssqlserver\loginmode
For a named instance of SQL Server:
HKEY_LOCAL_MACHINE Oftware\microsoft\microsoft SQL server\<instance Name>\mssqlserver\loginmode
Verify that the values for the following registry keys are set:
Authentication type values are limited to Windows Authentication 1 mixed Mode (SQL Server identity authentication and Windows authentication) 2
Note: If you make any changes to the registry, you must close and restart the SQL Server instance for the changes to take effect. Try connecting to an instance of SQL Server using a different Windows account or SQL Server logon account. This can help determine if the connection failed due to a particular logon account problem. For example, the password for the login account may have been changed. Try connecting to an instance of SQL Server using a different protocol. For example, a connection that uses the TCP/IP protocol for Windows authentication may fail, but a connection to Windows authentication using the named pipe protocol may succeed. If you are using a certificate, you may receive a Secure Sockets Layer (SSL) security error message when you try to connect to an instance of SQL Server. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
316898 How to enable SSL encryption for SQL Server 2000 by using Microsoft Management Console
322144 fix:secdoclienthandshake cannot connect to SQL Server
Return to the top of the page to resolve a pressure problem on a TCP/IP socket when you use the SQL Server ODBC driver, Microsoft OLE DB Provider for SQL Server, or System.Data.SqlClient management provider, You can use the appropriate application programming interface (API) to disable connection pooling. The pressure on the basic SQL Server Network library may increase when connection pooling is disabled and the application frequently opens and closes connections. Sometimes the WEB server and the JDBC driver try to connect to an instance of SQL Server. As a result, increased requirements for SQL Server connections may exceed the processing power of SQL Server. This can put pressure on TCP/IP sockets, and you may receive the following error message in the SQL Server error log file:
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 number to view the article in the Microsoft Knowledge Base:
154628 Inf:sql Records 17832 when multiple TCP\IP connection requests are encountered
328476 TCP/IP settings for SQL Server drivers when connection pooling is disabled
Note: If you are running SQL Server SP3 or SQL Server SP3a, you will not notice the pressure on the TCP/IP sockets due to the addition of a limit on the number of login packets. Error 17832 occurs when connecting to an instance of SQL Server using a third-party driver. To resolve this issue, contact a third-party vendor and obtain a test-proven driver that can be used with SQL Server SP3 and SQL Server SP3a.
Go to the top of the page view whether the SQL Server instance starts in Single-user mode if the instance of SQL Server that you are trying to connect to is started in Single-user mode, you can only establish a connection to SQL Server. If the computer running the software is automatically connected to SQL Server, the software can easily use a unique connection. For example, the following software can automatically connect to an instance of SQL Server: SQL Server Agent Third-party backup Software Third-party monitoring Software Third-party virus software Microsoft Internet Information Services (IIS) SQL Server Enterprise Manager tries to connect to The client application for the SQL Server instance receives the following error message:
SQL Server does not exist or Access denied this error typically occurs during SQL cluster installation and service package installation when the installation process starts a SQL Server instance in Single-user mode. The specified application automatically connects to an instance of SQL Server that uses the only available connection, and the installation fails.
To determine if an instance of SQL Server has been started in Single-user mode, check the SQL Server error log file for an entry similar to the 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.
Returns a named pipe connection to the top of the page validation to SQL Server if you cannot connect to an instance of SQL Server using Named pipes, make sure that the SQL Server instance is configured to accept named pipe connections. For additional information about procedures for testing named pipes, click the following article number to view the article in the Microsoft Knowledge Base:
68941 INF: Procedure for testing Named pipes
Return to the top of the page resolving the connection timeout during the recovery process SQL Server restores individual databases each time an instance of SQL Server is started. During this recovery process, SQL Server rolls back uncommitted transactions. When you stop an instance of SQL Server, SQL Server also rolls forward the committed transaction and the changes that are not written to the hard disk. When the recovery process is complete, SQL Server writes the following information in the SQL Server error log file:
Recovery Complete SQL Server may not accept connections during the recovery process. Clients that attempt to connect to SQL Server during this time may receive an error message that is similar to the following:
The Timeout expiredsql Server Agent service may not start because it waits for SQL Server to recover the database. Therefore, when you receive the following information in the SQL Server error log file, the connection will no longer fail due to a timeout error:
Recovery complete If the recovery process takes a long time, you may also want to troubleshoot the recovery process.
Go back to the top of the page test different ways to connect to an instance of SQL Server when you connect to an instance of SQL Server, if you experience a connection problem, you can use one or more of the following methods to resolve the connection problem. Use SQL Server authentication and Windows authentication to test connections to instances of SQL Server. Test connections to instances of SQL Server from other data sources, such as ODBC DSN,. udl files, SQL Query Analyzer, SQL Server Enterprise Manager, isql utility, or osql utility. Use other protocols to test connections to instances of SQL Server. You can specify a different protocol by creating a new alias for the instance of SQL Server that uses the protocol. You can also specify the protocols in your connection string by adding TCP:, NP:, LPC:, or RPC: At the beginning of the SQL Server instance name. For example, if a TCP/IP connection fails, the named pipe connection succeeds. Test the connection with a different logon account, which can help you determine if the problem is related to a particular login account. Add an entry in the%systemroot% ystem32\drivers\etc\hosts file that corresponds to the IP address of the computer that is running the instance of SQL Server. Attempt to connect to a SQL Server instance from a computer running SQL Server and a client. If you are connecting from a computer that is running SQL Server, you can specify "." or "(local)" (without quotes) to replace the server name, and then connect. Attempt to connect to an instance of SQL Server using an IP address instead of a server name. Attempt to specify a specific port on which the SQL Server instance is listening. There are two ways to create an alias or add a port number to a connection string (for example, myserver\myinstance,1433). Back to top capture Network Monitor Trail If you use the steps that are mentioned in the "testing various methods for connecting to a SQL Server 2000 instance" section to resolve connection problems, use the Network Monitor utility to capture network traces. For additional information about network tracing, click the following article number to view the article in the Microsoft Knowledge Base:
148942 How to use Network Monitor to capture network traffic
294818 frequently asked questions about Network Monitor
169292 basic knowledge of TCP/IP trace data
102908 How to troubleshoot TCP/IP connectivity problems in Windows 2000 or Windows NT
For more detailed information, you need to use SQL Profiler traces. You can also use the Network Diagnostics tool for a nonclustered computer 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:
321708 How to use the Network Diagnostics tool (Netdiag.exe) in Windows 2000
Return to the top of the page for additional information about various connection issues related to SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
812817 support for WEB broadcasts: Microsoft SQL Server 2000: Troubleshooting Connectivity
319930 How to connect to the Microsoft Desktop Engine
257434 INF: The network library in an existing DSN is replaced by a network library that is connected to a new DSN in SQL Server with the same name.
306985 RPC error occurred while connecting to the cluster virtual server using Named Pipes
313062 How to connect to a database by using Active Server Pages in Windows 2000
313295 How to specify the client network library using the server name parameter in the connection string
320728 how to exclude "the Maximum Connection Limit has Been" error message
328306 INF: Possible cause of "SQL Server does not Exist or Access Denied" (SQL Server does not exist or denied access) error messages
247931 INF: Method of verifying connections to SQL Server in Active Server Page
169377 How to access SQL Server in Active Server Page
328383 inf:sql Server Clients can change the protocol when connecting
238949 How to set up the SQL Server Network library in the ADO connection string
270126 PRB: How to manage clients ' connections to two SQL Server 2000 virtual servers after active/active Cluster 7.0 upgrade
316779 PRB: Clients with mandatory protocol encryption settings may not be able to connect via IP address
216643 INF: Odbc/oledb connection option not found when tracing to a connection to SQL Server 7.0
265808 INF: How to connect to a SQL Server 2000 named instance with an earlier version of the client tool
191572 INFO: A connection pool managed by ADO objects called from ASP
313173 Example Connection pool manager for the JDBC driver for Microsoft SQL Server 2000
237844 How to enable ODBC connection pooling in Visual Basic ADO applications
259569 PRB: Installing a third-party product destroys Windows MDAC registry settings
