Microsoft KB Archive/827422

From BetaArchive Wiki

Article ID: 827422

Article Last Modified on 4/28/2004


APPLIES TO

  • Microsoft SQL Server 2000 Standard Edition


Important This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand 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 Description of the Microsoft Windows Registry



IN THIS TASK

SUMMARY

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:

  • Communication Components
  • Client and Server Net-Libraries
  • Managing Clients


back to the top

Troubleshoot connectivity issues

Most of the connectivity issues that you may notice in SQL Server 2000 occur because of problems with TCP/IP, Windows authentication, or a combination of TCP/IP and Windows authentication.

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.

back to the top

Verify your DNS settings

The name resolution process in Domain Name System (DNS) is used to resolve the IP address to the name of the instance of SQL Server. If the name resolution process does not work correctly, the instance of SQL Server is not reachable, 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 so, follow these steps:

  1. Click Start, and then click Run.
  2. In the Run dialog box, type cmd in the Open box, and then click OK.

  3. At the command prompt, run the following command:

    ping <Server Name>

    Note the IP address that is returned.

  4. At the command prompt, run the following command (where IP address is the IP address that you noted in step 3):

    ping –a <IP address>

Verify that the command resolves to the correct server name. If either of the specified commands are not successful, time out, or do not return the correct values, the DNS lookup is not working correctly or the problem occurs because of other networking or routing issues. To see your 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 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.

back to the top

Verify the enabled protocols and aliases

Connectivity problems may occur if the alias on the client computer is set incorrectly. You can view the aliases by using Client Network Utility. To do so, follow these steps:

  1. Start Client Network Utility. If the SQL Server client tools are installed on the computer that is running the client application, follow these steps to start Client Network Utility:
    1. Click Start, and then point to Programs.
    2. Point to Microsoft SQL Server, and then click Client Network Utility.

    If the SQL Server client tools are not installed on the client computer, follow these steps to start Client Network Utility:

    1. Click Start, and then click Run.
    2. In the Run dialog box, type cliconfg in the Open box, and then click OK.
  2. 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 at least enable the TCP/IP protocol and the named pipes protocol.
  3. Click the Alias tab, and then verify the aliases that are configured for the instance of SQL Server.
  4. Verify the properties of the aliases to make sure that the server name or IP address and the protocol are configured correctly.

You can create a new alias to test the connectivity by using the server name, the IP address, or even by using a different protocol.

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.

back to the top

Verify that the instance of SQL Server is listening correctly

To verify that the instance of SQL Server is listening on named pipes, TCP/IP, or another protocol that you are using at the client application, open the current SQL Server error log file. The SQL Server error log file may contain entries that are 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.



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:

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 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: 

NETSTAT -an

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:

310099 Description of the Portqry.exe command-line utility


For 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:

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:

286303 INF: Behavior of SQL Server 2000 network library during dynamic port detection


823938 How to use static and dynamic port allocation in SQL Server 2000



back to the top

Troubleshoot MDAC Issues

Connectivity problems may also occur because of problems with MDAC. For example, a software installation may overwrite some of the MDAC files or change the permissions that you must have to access the MDAC files. You can run the MDAC Component Checker to verify the MDAC installation on your computer.

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:

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:

138541 HOW TO: Odbcping.exe to verify ODBC connectivity to SQL Server


For additional information about configuring ODBC DSNs, click the following article number to view the article in the Microsoft Knowledge Base:

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:

244659 SAMPLE: How to create a data link file with Windows 2000



back to the top

Troubleshoot firewall issues

If firewall exists between the client computer and the computer that is running SQL Server, make sure that the ports that are required to communicate through the firewall are open.

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:

287932 INF: TCP ports needed for communication to SQL Server through a firewall


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: 

Telnet <IP Address> <Port Number>

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.

back to the top

Troubleshoot authentication and security issues

Connections to SQL Server may not be successful because of authentication failures. 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 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:

Cannot generate SSPI Context

The following problems may cause authentication and security issues:

  • Problems occur with NTLM authentication or with Kerberos authentication.
  • The domain controller cannot be contacted because of connectivity issues.
  • Problems occur with trust relationships across domains.

For more information about possible causes, see the event logs on the computer. To work around connectivity problems with Windows authentication, you can use SQL Server Authentication to connect to the 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 troubleshoot the "Cannot generate SSPI context" error message


If the connection is not successful when you use SQL Server Authentication, you receive the following error message:

Login failed for user '<username>' . Not associated with a trusted connection


To troubleshoot this problem, follow these steps.

Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

  1. Make sure that the instance of SQL Server is configured to use Windows authentication and SQL Server Authentication. To do so, make sure that the following registry keys are on the computer that is running SQL Server. For the default instance of SQL Server:

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\LoginMode

    For the named instance of SQL Server:

    HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\LoginMode

    Make sure that the following registry key values are set:

    Authentication type Value
    Windows authentication only 1
    Mixed mode (SQL Server Authentication and Windows authentication) 2


    Note If you make any changes to the registry, you must stop and then restart the instance of SQL Server for the changes to take effect.
  2. Try to connect to the instance of SQL Server by using different Windows accounts or SQL Server login accounts. This can help determine if the connection is not successful because of problems with a particular login account. For example, the password of the login account may have been changed.
  3. Try to connect to the instance of SQL Server by using different protocols. For example, the connections that use the TCP/IP protocol with Windows authentication may not be successful, but connections that use the named pipes protocol with Windows authentication may be successful.

If you are using certificates, you may receive a Secure Sockets Layer (SSL) security error message when you try to connect to the instance of SQL Server. For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

316898 HOW TO: Enable SSL encryption for SQL Server 2000 with Microsoft Management Console


322144 FIX: SECDoClientHandShake cannot connect to SQL Server




back to the top

Troubleshoot stress on TCP/IP sockets

When you use the SQL Server ODBC driver, the Microsoft OLE DB Provider for SQL Server, or the System.Data.SqlClient managed provider, you can disable connection pooling by using the appropriate application programming interfaces (APIs). When you disable connection pooling and your application frequently opens and closes connections, the stress on the underlying SQL Server network library may increase. Sometimes, the Web servers and the JDBC drivers may also try to connect to the instance of SQL Server. Therefore, the increase in connection requests to SQL Server may be more than SQL Server can handle. This may stress the 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 numbers to view the articles in the Microsoft Knowledge Base:

154628 INF: SQL logs 17832 with multiple TCP\IP connection requests


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.

back to the top

See if the instance of SQL Server is started in single-user mode

If the instance of SQL Server that you are trying to connect to is started in single-user mode, only one connection can be established with SQL Server. If you have software running on your computer that automatically connects to SQL Server, the software can easily use the only connection. For example, the following software can automatically connect to the 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

The client application that is trying to connect to the instance of SQL Server receives the following error message:

SQL Server does not exist or Access Denied

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.

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.


back to the top

Verify named pipes connectivity to SQL Server

If you cannot connect to the instance of SQL Server by using named pipes, make sure that the instance of SQL Server is configured to accept named pipes connections. For additional information about the procedure 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



back to the top

Troubleshoot connections that time out during the recovery process

Every time that you start an instance of SQL Server, SQL Server recovers each database. During this recovery process, SQL Server rolls back the transactions that are not committed. SQL Server also rolls forward the transactions that are committed and the changes that were not written to the hard disk when the instance of SQL Server was stopped. When the recovery process is complete, SQL Server logs the following message in the SQL Server error log file:

Recovery Complete

During the recovery process, SQL Server may not accept connections. Clients that try to connect to the instance of SQL Server during that time may receive an error message that is similar to the following:

Timeout Expired

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:

Recovery Complete

If the recovery process takes a long time, you may have to additionally troubleshoot the recovery process.

back to the top

Test different ways to connect to the instance of SQL Server

If you experience connectivity problems when you connect to the instance of SQL Server, you can use one or more of the following methods to work around the connectivity problem.

  • Test the connectivity to the instance of SQL Server by using both SQL Server Authentication and Windows authentication.
  • Test the connectivity to the instance of SQL Server from other data sources, such as an ODBC DSN, a .udl file, SQL Query Analyzer, SQL Server Enterprise Manager, the isql utility, or the osql utility.
  • Test the connectivity to the instance of SQL Server by using different protocols. You can specify different protocols by creating a new alias for the instance of SQL Server using that protocol. You can also specify the protocol in your connection string by adding tcp:, np:, lpc:, or rpc: to the beginning of the name of the instance of SQL Server. For example, if TCP/IP connections are not successful, named pipes connections succeed.
  • Test the connectivity by using a different login account to help you determine if the problem is associated with a particular login account.
  • Try to add an entry that corresponds to the IP address of the computer that is running the instance of SQL Server to the %systemroot%\system32\drivers\etc\hosts file.
  • Try to connect to the instance of SQL Server from the computer that is running SQL Server and from the client.
  • If you are connecting from the computer that is running SQL Server, you can specify "." or "(local)" (without the quotation marks) instead of the server name and then connect.
  • Try to connect to the instance of SQL Server by using the IP address instead of the server name.
  • Try to specify the specific port that the instance of SQL Server is listening on, either by creating an alias or by adding a port number to the connection string (MyServer\MyInstance, 1433, for example).

back to the top

Capture network monitor traces

If the connectivity problem is not resolved by the steps that are mentioned in "Test different ways to connect to the instance of SQL Server 2000" section, use the Network Monitor utility to capture network traces. For additional information about network tracing, click the following article numbers to view the articles in the Microsoft Knowledge Base:

148942 How to capture network traffic with Network Monitor



294818 Frequently asked questions about Network Monitor



169292 The basics of reading TCP/IP traces



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:

321708 HOW TO: Use the Network Diagnostics Tool (Netdiag.exe) in Windows 2000


back to the top

REFERENCES

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:

812817 Support WebCast: Microsoft SQL Server 2000: Troubleshooting connectivity


319930 HOW TO: Connect to Microsoft Desktop Engine


257434 INF: Network library in existing DSN replaced by network library in new DSN to the same SQL Server name


306985 RPC errors when connecting a cluster virtual server with named pipes


313062 HOW TO: Connect to a database by using Active Server Pages in Windows 2000


313295 HOW TO: Use the server name parameter in a connection string to specify the client network library


320728 HOW TO: Resolve "The maximum connection limit has been reached" error message


328306 INF: Potential causes of the "SQL Server does not exist or access denied" error message


247931 INF: Authentication methods for connections to SQL Server in Active Server Pages


169377 How to access SQL Server in Active Server Pages


328383 INF: SQL Server clients may change protocols when they try to connect


238949 HOWTO: Set the SQL Server network library in an ADO connection string


270126 PRB: How to manage client connectivity to both SQL Server 2000 virtual servers after an active/active cluster 7.0 upgrade


316779 PRB: Clients with Force Protocol Encryption set on may fail to connect with an IP address


216643 INF: ODBC/OLEDB connect options not seen when tracing connections to SQL Server 7.0


265808 INF: How to connect to an SQL Server 2000 named instance with the previous version's client tools


191572 INFO: Connection pool management by ADO objects called from ASP


313173 Sample connection pool manager for use with Microsoft SQL Server 2000 driver for JDBC


237844 HOWTO: Enable ODBC connection pooling in a Visual Basic ADO application


259569 PRB: Installing third-party product breaks Windows 2000 MDAC registry settings


back to the top

Keywords: kberrmsg kbhowto kbtshoot kbregistry kbsqlclient kbdns kbdll kbclientserver kbclient KB827422



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/827422&oldid=333588’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki