How to solve the connection problem in SQL Server 2000 (from msdn)

How to solve the connection problem in SQL Server 2000

Applicable

Important: This article contains information about modifying the registry. Before modifying the registry, you must back up the registry and know how to restore the Registry in case of a problem. 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 of this task

• Summary
• Solve connection problems
  • Verify DNS settings
  • Verify the enabled protocol and alias
  • Verify that the SQL server instance is correctly listening
  • Solve MDAC Problems
  • Solve firewall Problems
  • Resolve authentication and security issues
  • Solve the pressure problem on TCP/IP sockets
  • Check whether the SQL server instance is started in single-user mode
  • Verify the named pipe connection to SQL Server
  • Solve the connection timeout problem during the recovery process.
  • Test different methods for connecting to an SQL server instance
  • Capture network monitor traces
• Reference

Summary

This article helps you solve Microsoft SQL Server 2000 connection problems. This article describes common connection problems and the steps that can be taken to help solve connection problems.

SQL Server 2000 supports multiple communication methods between SQL Server instances and client applications. If your client application and SQL server instance are on the same computer, the communication (IPC) components between Microsoft Windows processes (such as local named pipes or shared memory protocols) it will be used for communication. However, when the client application and SQL server instance are on different computers, a network IPC (such as TCP/IP or named pipe) will be used for communication.

SQL Server 2000 uses a network library (DLL) to communicate with a specific network protocol. A pair of matching network libraries must be activated on the client computer, and must also be activated on the server computer that supports the network protocol to be used. For example, if you want to enable a client application to communicate with a specific SQL server instance through TCP/IP, the client's TCP/IP Socket network library (dbnetlib. DLL) must be configured to connect to the client computer server. Similarly, the server TCP/IP Socket network library (ssnetlib. dll) must listen on the server computer. In this solution, the TCP/IP protocol stack must be installed on both the client computer and the server computer.

After installing SQL Server 2000, you can configure the properties of the client network library through the client network utility. You can configure the attributes of the server network library through the server network utility (svrnetcn.exe. When installing the server tool in the SQL Server Installation Program, the network library of the server is also installed. However, the Network Libraries of some servers may not be activated. By default, SQL Server 2000 enables and listens to TCP/IP, named pipes, and shared memory. Therefore, to connect a client to a server computer, the client must use a client network library, which must match one of the server network libraries that the SQL server instance is using.

For more information about SQL Server communication components and Network Libraries, see the following topics in the SQL Server online reference book:

• Communication components
• Client and server network library
• Management Client

Back to Top

Solve connection problems

Most of the connection problems you may notice in SQL Server 2000 are caused by TCP/IP or Windows identity authentication issues, or both.

Important: Before you begin to solve the connection problem of SQL Server 2000, make sure that the MSSQLServer service has been started on the computer running SQL Server.

Back to Top

Verify DNS settings

The name resolution process in the Domain Name System (DNS) is used to resolve the IP address of the SQL server instance name. If the name resolution process is abnormal, you cannot obtain the SQL server instance. 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 whether the server is correctly resolved during name resolution, you can ping the server by using the server name and IP address of the server. To do this, follow these steps:

1. Click Start, and then click Run ".
2. In the "run" dialog box, typeCMDAnd then click OK ".
3. Run the following command at a command prompt:

   ping <Server Name>

   Record the returned IP address.

4. At the command prompt, run the following command (IP addressIs the IP address you recorded in step 3 ):

   ping –a <IP address>

Verify that the command parses the correct server name. If either of the two specified commands fails, times out, or does not return the correct value, the DNS lookup fails to work, or other network or route problems cause problems. To view the current DNS settings, run the following command at a command prompt:

ipconfig /all

For other information about the ipconfig command, click the following article number to view the article in the Microsoft Knowledge Base:

223413 ipconfig.exe option in Windows 2000

To solve this problem, add an entry to the server in the % SystemRoot %/system32/Drivers/etc/hosts file on the client computer. To solve this problem, you can also use the named pipe network library to connect to the server.

Back to Top

Verify the enabled protocol and alias

If the alias settings on the client computer are incorrect, connection problems may occur. You can use the client network utility to view the alias. To do this, follow these steps:

1. Start the client network utility. If the SQL Server Client tool is installed on a computer that is running a client application, follow these steps to start the client network utility:

   
   

   1. Click Start and point to program ".
   2. Point to Microsoft SQL Server and 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:

   1. Click Start, and then click Run ".
   2. In the "run" dialog box, typeCliconfgAnd then click OK ".
2. In the "SQL Server Client Network utility" window, click the "General" tab and enable all the protocols you want to use.

   Note:: You must at least enable the TCP/IP protocol and the named pipe protocol.

3. Click the "alias" tab and verify that it is the alias configured for the SQL server instance.
4. Verify the attributes of the alias and confirm that the server name, IP address, and Protocol are correctly configured.

By using the server name, IP address, or other protocols, you can create a new alias to test the connection performance.

Note:: In earlier versions of Microsoft Data Access Components (MDAC), the user interface of the client network utility is different. Therefore, if you do not see the options listed in this article, install the new version of MDAC on the computer that runs the client application.

Back to Top

Verify that the SQL server instance is correctly listening

To verify that the SQL server instance is correctly listening 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 contain similar entries:

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 SQL Server protocol settings and change the properties in SQL Server, including protocols that can connect to SQL Server and ports that can be used. 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 with an incorrect IP address. Therefore, the connection from TCP/IP to SQL Server may fail. In the SQL server error log, you will receive the following error message: 15:49:14. 12 server supersockets info: bind failed on TCP port 1433.

For other information, click the following article number to view the article in the Microsoft Knowledge Base:

307197 PRB: The TCP/IP Port is being used by another application

NETSTAT -an

You can also use the portqry command line utility to obtain more information about the port in use.

For more 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 other 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: When TCP/IP is the only protocol, SQL Server may not be able to listen on TCP/IP sockets

Note:: For SQL Server naming instances, SQL Server dynamically determines the port and listens to it. Therefore, when you start the SQL Server naming instance, SQL Server will try to listen to the previously used port. If SQL Server cannot be bound to this 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. You can also specify a static port for the named instance and bind and listen to it through the client network utility.

For other information, click the following article number to view the article in the Microsoft Knowledge Base:

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

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

Back to Top

Solve MDAC Problems

MDAC problems may also cause connection problems. For example, installing a software may overwrite some MDAC files or change permissions, and you need these permissions to access the MDAC file. You can run the MDAC part Checker to verify the MDAC installation on your computer.

For more information about how to determine your current MDAC version, click the following article number to view the article in the Microsoft Knowledge Base:

301202 how to check the MDAC version

Note:: If you are connecting to a named instance of SQL Server, make sure that MDAC 2.6 or an updated version 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 the named instance.

You can use the odbcping.exe utility to verify the connection through the SQL Server ODBC driver.

For more 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 the ODBC connection to SQL Server

For other information about configuring odbc dsn, click the following article number to view the article in the Microsoft Knowledge Base:

289573 PRB: use SQL Server Net-libraries (SQL SERVER network library) to configure DSN

You can also use a. udl file to test the connection with the SQL server instance.

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

Back to Top

Solve firewall Problems

If the firewall exists between the client computer and the computer running SQL Server, make sure that the required port is enabled when the firewall is used for communication.

For more information about the ports that must be opened when you use the firewall to communicate, click the following article number to view the article in the Microsoft Knowledge Base:

287932 inf: TCP port required for communication with SQL Server through the firewall

269882 how to use ADO to connect to the SQL server after the Firewall

If you use the TCP/IP protocol to connect to the SQL server instance, make sure that you can use the telnet program to connect to the port on which 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 fails and you receive an error message, resolve the error and try to connect again.

Note:: Due to problems caused by the slammer virus, User Datagram Protocol (UDP) port 1434 may be blocked on your firewall.

Back to Top

Resolve authentication and security issues

Unable to connect to SQL Server due to identity authentication failure. If identity 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 identity authentication failure, and the error message does not mention a clear SQL Server login name, use Windows identity authentication to solve this problem. Due to Windows Identity Authentication problems, you may receive the following error message: cannot generate sspi Context

The following problems may cause identity authentication and security problems:

• NTLM identity authentication or Kerberos identity authentication problems.
• Unable to contact the domain controller due to connection issues.
• There is a problem with the trust relationship between domains.

For more information about possible causes, see Event Logs on your computer. To solve the connection problem of Windows identity authentication, you can use SQL Server identity authentication to connect to the SQL server instance. For more information about how to diagnose 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 fails during SQL Server Authentication, you will receive the following error message: Login Failed for user' <Username> '. Not associated with a trusted connection

To solve this problem, follow these steps.

Warning: Improper use of the Registry Editor can cause serious problems and the operating system may need to be re-installed. Microsoft cannot guarantee that you can solve problems caused by improper use of Registry Editor. You are at your own risk to use the Registry Editor.

1. Make sure that the SQL server instance is configured with Windows Authentication and SQL Server Authentication. To this end, make sure that the following registry entries exist on the computer on which SQL server is running. For default SQL Server instances:

   
   

   HKEY_LOCAL_MACHINE/software/Microsoft/MSSQLServer/loginmode

   For SQL Server naming instances:

   HKEY_LOCAL_MACHINE/software/Microsoft SQL Server/<Instance name>/MSSQLServer/loginmode

   Make sure that you have set the following registry key values:

   Authentication Type	Value
   Windows authentication only	1
   Hybrid mode (SQL Server Authentication and Windows Authentication)	2

   Note:: If you have made any changes to the registry, you must disable and restart the SQL server instance for the change to take effect.

2. Try to use another Windows Account or SQL Server Logon account to connect to the SQL server instance. This helps determine whether the connection fails due to a Special Logon account problem. For example, the Logon account password may have been changed.
3. Try to connect to the SQL server instance using other protocols. For example, a connection that uses the TCP/IP protocol for Windows identity authentication may fail, but a connection that uses the named pipe protocol for Windows identity authentication may succeed.

If you are using a certificate, you may receive a Security Socket Layer (SSL) error message when attempting to connect to the SQL server instance. For other 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 with Microsoft Management Console

322144 fix: secdoclienthandshake cannot connect to SQL Server

Back to Top

Solve the pressure problem on TCP/IP sockets

When you use the SQL Server ODBC driver, Microsoft ole db provider for SQL Server, or system. data. when the sqlclient manages the provider, you can use the appropriate application programming interface (API) to disable the connection pool. When the connection pool is disabled and applications frequently open or close connections, the pressure on the basic SQL SERVER network library may increase. Sometimes, the web server and JDBC driver will try to connect to the SQL server instance. Therefore, the increase in SQL Server connection requirements may exceed the processing capacity of SQL Server. This may put pressure on TCP/IP sockets. You may also receive the following error message in the SQL Server Error Log File: 20:46:21. 11 server error: 17832, severity: 20, state: 6
20:46:21. 11 server connection opened but Invalid Login packet (s) sent. Connection closed.

For other information, click the following article number to view the article in the Microsoft Knowledge Base:

154628 inf: SQL records 17832 when encountering multiple TCP/IP connection requests

328476 disable the TCP/IP settings of the SQL Server Driver when the connection pool is disabled

Note:: If you run SQL Server 2000 SP3 or SQL Server 2000 sp3a, you will not notice the pressure on the TCP/IP socket. This is because you have added a limit on the number of login data packets. Error 17832 occurs when a third-party driver is used to connect to the SQL server instance. To solve this problem, contact a third-party vendor and obtain the drivers that have been tested to be used for SQL Server 2000 SP3 and SQL Server 2000 sp3a.

Back to Top

Check whether the SQL server instance is started in single-user mode

If the SQL server instance to be connected is started in single-user mode, only one connection to SQL Server can be established. If the computer running the software automatically connects to SQL Server, the software can easily use a unique connection. For example, the following software can automatically connect to an SQL server instance:

• SQL Server Agent
• Third-party Backup Software
• Third-party monitoring software
• Third-party Virus Software
• Microsoft Internet Information Service (IIS)
• SQL Server Enterprise Manager

The client application that tries to connect to the SQL server instance receives the following error message: SQL server does not exist or access denied

When the installation process starts the SQL server instance in single-user mode, this error usually occurs during the SQL cluster installation and service package installation. The specified application will automatically connect to the SQL server instance that uses the unique available connection, and thus the installation fails.

To determine whether the SQL server instance has been started in single-user mode, check whether the SQL Server Error Log File has an entry similar to the following: 11:26:43. 79 spid3 warning ******************
11:26:43. 80 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.

Back to Top

Verify the named pipe connection to SQL Server

If you cannot connect to the SQL server instance using the named pipe, make sure that the SQL server instance is configured to accept the named pipe connection. For additional information about the process of testing the named pipeline, click the following article number to view the article in the Microsoft Knowledge Base:

68941 inf: the process of testing named Pipelines

Back to Top

Solve the connection timeout problem during the recovery process.

Each time you start an SQL server instance, SQL Server restores each database. During the restoration process, SQL Server rolls back uncommitted transactions. When you stop an SQL server instance, SQL Server also rolls forward committed transactions and changes that are not written to the hard disk. When the recovery process is completed, SQL Server writes the following information to the SQL Server Error Log File: Recovery complete

During the restoration process, the SQL Server may not accept the connection. The client that tries to connect to SQL Server within this time may receive an error message similar to the following: timeout expired

The SQL Server proxy service may fail to be started because it waits for SQL Server to restore 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 need to troubleshoot the fault.

Back to Top

Test different methods for connecting to an SQL server instance

When you connect to an SQL server instance, you can use one or more of the following methods to solve the connection problem.

• Use SQL Server Authentication and Windows authentication to test the connection to the SQL server instance.
• Test the connection to an SQL server instance 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 the connection to the SQL server instance. You can create a new alias for the SQL server instance that uses this Protocol to specify different protocols. You can also specify the protocol in your connection string by adding TCP:, NP:, LPC:, or RPC: at the beginning of the SQL server instance name. For example, if the TCP/IP connection fails, the pipe connection is successful.
• Use another Logon account to test the connection. This helps you determine whether the problem is related to a specific Logon account.
• Add an entry in the % SystemRoot %/system32/Drivers/etc/hosts file that corresponds to the IP address of the computer on which the SQL server instance is running.
• Try to connect to the SQL server instance from the computer and client running SQL Server.
• If you are connecting from a computer running SQL Server, you can specify "." or "(local)" (without quotation marks) to replace the server name and then connect.
• Try to connect to the SQL server instance by using the IP address instead of the server name.
• Try to specify the specific port on which the SQL server instance is listening. You can create an alias or add a port number to a connection string (for example, myserver/myinstance, 1433 ).

Back to Top

Capture network monitor traces

If the connection issue cannot be resolved by using the steps mentioned in the "test methods for connecting to an SQL Server 2000 instance" section, use the network monitor utility to capture network traces. For more 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 FAQs about network monitor

169292 interpret the basic knowledge of TCP/IP tracking data

102908 how to exclude TCP/IP connection issues in Windows 2000 or Windows NT

For more details, you need to use the SQL event probe trace. Network diagnostic tools can also be used for non-clustered computers running SQL Server for network tracing. For more information about how to use the network diagnostic tool, click the following article number to view the article in the Microsoft Knowledge Base:

321708 how to use the network diagnostic tool (netdiag.exe) in Windows 2000)

Back to Top reference

For more information about SQL Server-related connection problems, click the following article number to view the article in the Microsoft Knowledge Base:

812817 support for web broadcast: Microsoft SQL Server 2000: Connection troubleshooting

319930 how to connect to the Microsoft Desktop Engine

257434 inf: The network library in the existing DSN is replaced by the network library in the new DSN connected to the SQL server with the same name.

306985 an RPC error occurred while connecting to the cluster virtual server using a named pipe

313062 how to connect to a database by Using Active Server Pages in Windows 2000

313295 how to use the server name parameter in the connection string to specify the client network library

320728 how to exclude the "the maximum connection limit has been reached" error message

328306 inf: possible cause of "SQL server does not exist or access denied" (SQL server does not exist or access is denied) error message

247931 inf: How to verify the connection to SQL server on the Active Server Page

169377 how to access SQL server on the Active Server Page

328383 inf: the SQL Server Client can change the Protocol during connection.

238949 how to set the SQL server network library in the ADO connection string

270126 PRB: how to manage the connection between the client and two SQL Server 7.0 virtual servers after the active/active cluster 2000 upgrade

316779 PRB: clients with mandatory protocol encryption settings may not be connected through IP addresses

216643 inf: the ODBC/oledb connection option cannot be found when the SQL Server 7.0 connection is tracked.

265808 inf: how to use an earlier client tool to connect to a SQL Server 2000 named instance

191572 info: connection pool managed by ADO object called by ASP

313173 example connection pool manager for JDBC driver of Microsoft SQL Server 2000

237844 how to enable ODBC connection pool in the visual basic ADO Application

259569 PRB: Installing a third-party product destroys Windows 2000 MDAC Registry Settings

The information in this article is applicable:

• Microsoft SQL Server 2000 (all editions)

Latest updates:	(3.1)
Keywords:	Kbhowto kbsqlclient kbdll kbclientserver kbclient kbdns kberrmsg kbtshoot kbregistry kb827422 kbauddeveloper