SQL Server 2000 supports several methods of communication between client applications and the server. When the application is on the same computer as an instance of SQL Server 2000, Windows Interprocess Communication (IPC) components, such as Local Named Pipes or Shared Memory, are used. When the application is on a separate client, a network IPC, such as TCP/IP or Named Pipes, is used to communicate with SQL Server.

SQL Server uses a dynamic link library (DLL), called a Net Library, to communicate with a particular network protocol. A matching pair of Net Libraries must be active on both client and server computers to support the desired network protocol. For example, 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 that server on the client computer, and the server TCP/IP Sockets Net Library (SSNETLIB.dll) must be listening on the server computer. Also in this case the TCP/IP protocol stack must be installed on both the client and server computers

After installing SQL Server 2000, you need to define the behaviors of the client Net Libraries by using the Client Network Utility and server Net Libraries by using the Server Network Utility. All the server Net Libraries are installed during the server portion of SQL Server Setup, but some of them may not be active. By default, SQL Server enables and listens on TCP/IP, Named Pipes, and Shared Memory. For a client to connect to a server running SQL Server 2000, the client must use a client Net Library that matches one of the server Net Libraries the server is currently listening on.

Please make sure that the MSSQLServer service for the SQL Server you are trying to connect to is started and running before doing any further troubleshooting. You can use one or more of the following steps to narrow down and resolve client connectivity issues with SQL Server.

Check Your DNS Settings
If the name resolution process does not work correctly to resolve the IP address to the SQL Server Name, then SQL Server will be unreachable and you may get errors like "SQL Server does not exist or access denied" or "General Network Error." In some cases you can also get the error "Cannot Generate SSPI Context."

Ping the server using the server name and IP address. See if the request completes successfully or times out. If either of the pings time out, fail, or do not return the correct values, then either the dns lookup is not working properly or there is some other networking or routing issue that you will need to resolve. You can run “ipconfig /all” (without the quotes) from a command prompt to check your current dns settings. You could also try adding an entry to the hosts file on your client as a workaround.

Check for Enabled Protocols and Aliases
There could be an alias or hosts file entry on the client machine that is set incorrectly and pointing to the wrong server or IP address, which in turn can lead to a connectivity failure. Check for any aliases set for the SQL Server in the SQL Client Network Utility. Also check your hosts file to see if there are any incorrect entries. Make sure the appropriate protocols are enabled on the client.

You could also try creating a new alias to test connectivity, using the server name, the IP address, or even using a different protocol. Then try connecting using that alias.

Check If SQL Server Is Listening Correctly
Verify that SQL Server is listening on Named Pipes, TCP/IP, or any other protocol that you are using. To verify it, open the current SQL Server Error Log. You should see lines similar to the following:

Verify that SQL Server is listening on the correct IP address. A SQL Server default instance listens on port 1433.

You can use the SQL Server Network Utility to verify SQL Server's protocol settings and change properties, such as which protocols can connect to SQL Server and which ports are used. Sometimes SQL Server may not bind to port 1433 or any other specified port. In that case, TCP/IP connections to SQL may fail.

NOTE: In case of named instances, SQL Server dynamically determines the port it listens on. It will try to listen on the port it used previously. If for some reason it cannot bind to that port, the named instance may bind to a different port. In that case, make sure the clients are set to dynamically determine the port. Alternatively, you could also specify a static port for that instance to bind on, using the Client Network Utility. If you are using an application to connect to SQL, this would also need to be set properly in the connection string or the dsn being used by that application.

MDAC (Microsoft Data Access Components) Issues
Sometimes connectivity issues can be due to problems with MDAC. For example, some other software installation may have overwritten some of the MDAC files or perhaps changed the permissions needed to access those files. You can run the Component Checker tool to verify your MDAC installation on that machine. In order to connect to named instances, clients would need to have MDAC 2.6 or later installed.

Firewall Issues
If there is a firewall between your client and the SQL Server machine, then make sure the appropriate ports are open according to

INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/default.aspx?scid=kb;en-us;287932

Authentication and Security Issues
Sometimes connections to SQL may fail due to authentication failures. In such cases, you may get errors like "Login failed for user 'user1'" or “Login failed for user 'NTAUTHORITY\ANONYMOUS LOGON'“ Or "Login failed for user 'null'."

These errors, which don’t mention a specific SQL Server login name, usually indicate a problem with Windows Authentication and would need further troubleshooting from that perspective. In such cases you can use SQL Authentication to connect to SQL as a workaround, until you resolve the Windows Authentication issue. In some cases where Windows Authentication fails, you would get the error "Cannot generate SSPI Context."

For details on troubleshooting and resolving this error refer to

HOW TO: Troubleshoot the "Cannot Generate SSPI Context" Error Message
http://support.microsoft.com/default.aspx?scid=kb;en-us;811889

If your connection fails with an error for a SQL Login, for example, "Login failed for user 'sa'. Not associated with a trusted connection," then verify that SQL Server is set up for Windows and SQL Authentication. Try connecting using different Windows or SQL logins. Also try connecting using alternate protocols.

Check If SQL Is Using Single User Mode
If SQL Server has been started in single user mode, and if you have SQL Server Agent, third-party backup software, third-party monitoring or virus software, or IIS, or even SQL Enterprise Manager or IIS running, which automatically connect to SQL, then they could easily use up that one connection. Any other client trying to connect to SQL Server at that point would receive the error "SQL Server does not exist or access denied."

Test Alternate Ways of Connecting to SQL
Some of these steps may be used as a temporary workaround until you resolve your main issue:

• Test connectivity using both SQL and Windows Authentication.
• Test connectivity from other sources, such as creating an odbc dsn, using a udl file, using SQL Query Analyzer, Enterprise Manager, isql, osql, etc.
• Test connectivity using other protocols. You can specify alternate protocols by creating a new alias for the server using that protocol, or you can specify the protocol in your connection string with tcp:, np:, lpc:, or rpc: before the server name. For example, in many cases if TCP/IP connections fail, named pipes connections would succeed.
• Test connectivity using a different account or login. This would help narrow down the issue to a particular login or user account.
• Try adding a hosts file entry for the SQL Server.
• Try the connection from the server itself and from the client.
• If you are connecting from the server itself, you can try specifying (local) instead of the server name.
• Try connecting using the IP address instead of the server name.
• Try specifying the port that SQL Server is listening on, either by creating an alias or adding “,{port#} to the connection string. For example, MyServer\MyInstance, 1433.

If the connectivity issue is still unresolved, you may need to take a Network Monitor Trace to get more information.

© 2003 Microsoft