How to solve connection problems in SQL Server 2000
Suitable
Important: This article contains information about modifying the registry. Before modifying the registry, you must back up the registry and must know how to restore the registry when there is a problem. For information on how to back up, restore, and edit the registry, click the article number below to view the article in the Microsoft Knowledge Base:
256986 Microsoft Windows Registry Description
This task content
Summary Solve connection issues
Verify DNS Settings Verification Enable Protocol and Alias Verify SQL Server Instances Are Correct Listening to Solving MDAC Problems Solving Firewall Problems Solving Authentication and Security Problems Solving Pressure Problems on TCP / IP Sockets View whether SQL Server instance is single user mode Startup Verification to SQL Server Named Pipe Connections Resolve Different Methods for Connection Timeout During Recovery Problems Different methods to connect to SQL Server instances Capture Network Monitor Trace Reference
SUMMARY This article can help you solve the connection problem of Microsoft SQL Server 2000. This article describes the common connection issues and the steps that can help to resolve connection issues.
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) component between Microsoft Windows processes (eg, local named pipe or shared memory protocol) will be used to communicate. However, when the client application and the SQL Server instance are on different computers, a network IPC (such as TCP / IP or Named Pipes) will be used to communicate.
SQL Server 2000 communicates with a specific network protocol using a network library (a DLL). A pair of matched 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, communicate with a particular SQL Server instance via TCP / IP, then 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 for server computers. In this scenario, the TCP / IP protocol stack must be installed on the client computer and server computer at the same time.
After installation 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 the server tool in the SQL Server installer is installed, the server's network library is also installed at the same time. However, some server network libraries may not be activated. By default, SQL Server 2000 is enabled and listened to 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 must match one of the server network libraries that the SQL Server instance is using.
For additional information about SQL Server Communication Parts and a network library, see the following topics in SQL Server Online References:
Communication Parts Client and Server Network Library Management Client
Back to top
Solving connection issues In SQL Server 2000, most of the connection issues you might notice are caused by TCP / IP issues or Windows identity authentication, or by both.
Important: Before starting to resolve the connection problem of SQL Server 2000, make sure that the MSSQLServer service is started on a computer running SQL Server.
Back to top
Verify that the name resolution process in the DNS Setting Domain Name System (DNS) is used to parse the IP address of the SQL Server instance name. If the name parsing process is not working properly, you cannot get a SQL Server instance, you may receive one or more of the following error message: SQL Server Does Not Exist or Access Denied
General Network Error
Cannot Generate SSPI CONTEXT
To verify that the name parsing process is parsing the correct server, you can use the server name and server's IP address PING server. To do this, follow these steps:
Click Start, then click Run". In the Run dialog, type CMD in the Open box, and then click OK. At the command prompt, run the following command: ping
iPConfig / All For additional information about the ipconfig command, click the article number below to see the article in the Microsoft Knowledge Base:
223413 Options for IPConfig.exe in Windows 2000
To resolve this issue, you can add an entry to the server in the% SystemRoot% / System32 / Drivers / etc / hosts file on the client computer. To resolve this issue, you can also connect to the server using the named pipeline network library.
Back to top
Verify that the enabled protocol and aliases can cause connection issues if the alias settings are incorrect on the client computer. You can use the Client Network Utility to view alias. To do this, follow these steps:
Start the client network utility. If the SQL Server client tool is installed on a computer running a client application, follow these steps to start the client network utility:
Click Start, point to Programs. 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:
Click Start, then click Run". In the Run dialog, type Cliconfg in the Open box, and then click OK. In the SQL Server Client Network Utilities window, click the General tab and enable all protocols you want to use. Note: You must enable at least TCP / IP protocols and named pipe protocols. Click the Alias tab and verify the alias configured for the SQL Server instance. Verify the properties of an alias, confirm that server names or IP addresses and protocols are correct. By using server names, IP addresses, or other protocols, you can create a new alias to test connection performance.
Note: In the earlier versions of the Microsoft Data Access Part (MDAC), the user interface of the client network utility is different. Therefore, if you don't see the options listed in this article, install the new version of MDAC on the computer running the client application.
Back to top
Verify that the SQL Server instance is correctly listening To verifying the SQL Server instance is correctly listening to named pipes, TCP / IP or other protocols you are using in client applications, open the current SQL Server error log file. SQL Server error log files may include items similar to: 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 to the correct IP address and port. By default, a default SQL Server instance listening port 1433. You can also use server network utilities to verify SQL Server's protocol settings and change properties in SQL Server, including protocols that can be connected to SQL Server and ports that can be used. For more information on 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 issue may occur if the port is being used by other applications, or if you are trying to use an error IP address to connect. Therefore, TCP / IP to SQL Server can be unsuccessful, and you will receive the following error message in the SQL Server error log:
2001-11-14 15: 49: 14.12 Server Super SUPERSOCKET INFO: BIND FAILED ON TCP Port 1433.
For additional information, click the article number below to see the article in the Microsoft Knowledge Base:
307197 PRB: TCP / IP port is used by another application
If you cannot connect to a SQL Server instance via TCP / IP, try using a named pipe protocol or share memory protocol. Run the following command at the command prompt to get information about the port being used:
NetStat -an You can also use the Portqry command line utility to get more information about the ports being used.
For additional information about the PORTQRY command line utility, click the article number below to see the article in the Microsoft Knowledge Base:
310099 PortQry.exe Command Line Utility Description For additional information related to TCP / IP socket, click the article number below 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 to TCP / IP sockets.
Note: For SQL Server named instances, SQL Server dynamically determines the port and listen to the determined port. Therefore, when you start the SQL Server named instance, SQL Server will try to listen to the previously used port. If SQL Server cannot bind to this port, named instances may dynamically bind to another port. In this case, confirm that the client application is also set to dynamically determine the port. As a selection, you can also specify a static port for the named instance, bind and listen through the client network utility.
For additional information, click the article number below to see the article in the Microsoft Knowledge Base:
286303 INF: Dynamic port detection process 823938 How to use static and dynamic port assignments in SQL Server 2000
Back to top
Solving MDAC issues MDAC 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 Parts Checker to verify that the MDAC is installed on your computer.
For additional information about how to determine your current MDAC version, click the article number below to see the article in the Microsoft Knowledge Base:
301202 How to check MDAC version
Note: If you are connecting to a naming instance of SQL Server, confirm that the MDAC 2.6 or updated version is running. Early versions of MDAC do not identify SQL Server naming instances. Therefore, you may not be able to connect to a naming instance.
You can use the ODBCPING.EXE utility to verify the connection through the SQL Server ODBC driver.
For additional information about ODBCPING.EXE, click the article number below to see the article in the Microsoft Knowledge Base:
138541 How to use odbcping.exe to verify that SQL Server ODBC Connections Configure additional ODBC DSN, click the following article number to view the appropriate article in the Microsoft Knowledge Base:
289573 PRB: Configuring DSN using SQL Server Net-Libraries (SQL Server Network Library)
You can also use a .udl file test with the SQL Server instance connection.
For additional information about how to create a .udl file, click the article number below to see the article in the Microsoft Knowledge Base:
244659 Example: How to Create a Data Link File in WINDOWS 2000
Back to top
Solve the firewall problem If the firewall is present between the client computer and the computer running SQL Server, please confirm that the port required when communicating through the firewall is open.
For additional information on ports that must be opened by firewall, click the article number below to see the article in the Microsoft Knowledge Base:
287932 INF: TCP port required to communicate with SQL Server via firewall
269882 How to use ADO to connect to SQL Server after the firewall
If you connect the SQL Server instance using the TCP / IP protocol, confirm that you can connect to the port of SQL Server is listening to the port with a Telnet program. To use a Telnet program, run the following command at the command prompt:
Telnet
Note: Because of the problem caused by the Slammer virus, on your firewall, the User Data News Protocol (UDP) port 1434 may be blocked.
Back to top
Solving authentication and security issues Because identity authentication fails, you may not be able to connect to SQL Server. If the identity authentication fails, you may receive one of the following error messages:
Login Faled for User '<
Username> '
Login Faled for User 'NTAUTHORITY / Anonymous Logon'
Login Faled for User 'Null' If you receive an error message due to identity authentication, and no clear SQL Server login name is mentioned in the error message, please use Windows authentication to resolve this issue. Due to Windows identity authentication issues, you may receive the following error message:
Cannot Generate SSPI CONTEXT
The following issues may cause identity authentication and security issues:
NTLM authentication or Kerberos authentication has problems. Due to connection issues, the domain controller cannot be connected. There is a problem with the trust relationship between the domain. For more information on possible causes, see the event log on your computer. To resolve the connection problem for Windows authentication, you can connect to the SQL Server instance using SQL Server authentication. For additional information about how to diagnose and resolve "Cannot Generate SSPI Context", click the article number below to view the article in the Microsoft Knowledge Base:
811889 How to exclude "Cannot Generate SSPI Context" error message If the connection is not successful when using SQL Server authentication, you will receive the following error message:
Login Faled for User '<
Username> '.Not Associated with a trusted connection
To resolve this issue, follow these steps.
WARNING: "Registry Editor" can cause serious problems and may need to reinstall the operating system. Microsoft does not guarantee that you can solve problems caused by improper use of the Registry Editor. Use Registry Editor at your own risk.
Make sure the SQL Server instance is configured to use Windows authentication and SQL Server authentication. To do this, confirm that there is the following registration items on the computer that is running SQL Server. For the default instance of SQL Server: HKEY_LOCAL_MACHINE / Software / Microsoft / MSSQLServer / MSSQLServer / LoginMode For a named instance of SQL Server: HKEY_LOCAL_MACHINE / Software / Microsoft / Microsoft SQL Server /
Identity authentication type value is limited to Windows Identification 1 Mixed mode (SQL Server authentication and Windows authentication) 2 Note: If you make any changes to the registry, you must turn off and restart the SQL Server instance to make changes to take effect. Try to connect to the SQL Server instance using another Windows account or SQL Server login account. This helps determine if a connection failed due to a particular login account. For example, the password for the login account may have been changed. Try to connect to the SQL Server instance using another protocol. For example, a connection to Windows identity authentication using TCP / IP protocols may fail, but the connection to use the Named Pipe Protocol will be successful. If you are using a certificate, a security socket layer (SSL) security error message may be received when trying to connect to the SQL Server instance. For additional information, click the article number below to see the article in the Microsoft Knowledge Base:
316898 How to enable SSL encryption for SQL Server 2000 with Microsoft Management Console 322144 FIX: SECDOCLIENTHANDSHAKE Unable to connect to SQL Server
Back to top
Solve pressure issues on TCP / IP sockets You can use the appropriate application programming interface (API) to disable when using the SQL Server ODBC driver, Microsoft Ole DB Provider for SQL Server or System.Data.sqlclient management provider. connection pool. The pressure of the basic SQL Server network library may increase when the connection pool is disabled and the application is frequently turned on and off. Sometimes, the web server and JDBC drivers will also try to connect to the SQL Server instance. Therefore, the increase in SQL Server connection requirements may exceed the processing power of SQL Server. This may cause pressure to TCP / IP socket, you may also 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 article number below to see the article in the Microsoft Knowledge Base:
154628 INF: SQL Record 17832 when encountering multiple TCP / IP connection requests 17832
328476 TCP / IP settings for SQL Server drivers when connecting to the pool
Note: If you run SQL Server 2000 SP3 or SQL Server 2000 SP3A, it will not notice the pressure on TCP / IP socket, which is due to the addition of the number of login packets. An error 17832 occurs when connecting to the SQL Server instance using a third-party driver. To resolve this issue, contact a third-party vendor and get a test proven to be used for drivers for SQL Server 2000 SP3 and SQL Server 2000 SP3A.
Back to top
Check if the SQL Server instance starts with a single user mode If the SQL Server instance attempt is started in single user mode, you can only create a connection to SQL Server. If the computer's computer is automatically connected to SQL Server, the software can easily use the unique connection. For example, the following software can be automatically connected to the SQL Server instance:
SQL Server Agent Third-Party Backup Software Third Party Monitoring Software Third-Party Virus Software Microsoft Internet Information Services (IIS) SQL Server Enterprise Manager attempts to connect to the SQL Server instance client application receives the following error message:
SQL Server Does NOT EXIST OR ACCESS DENIED
This error typically occurs during SQL cluster installation and service pack installation when the installation process starts a SQL Server instance in a single user mode. The specified application is automatically connected to the SQL Server instance that uniquely available connections to install failed.
To determine if the SQL Server instance is started in single user mode, check if there is a similar entry in the SQL Server error log file:
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 top
Verify that the named pipe connection to SQL Server If you are unable to connect to the SQL Server instance using a named pipe, confirm that the SQL Server instance is configured to accept named pipe connections. For additional information about the process of testing a naming pipe, click the article number below to view the article in the Microsoft Knowledge Base:
68941 INF: Test the process of naming pipeline
Back to top
Solving the problem of connecting the timeout during the recovery process When you start a SQL Server instance, SQL Server will resume each database. During this recovery process, SQL Server rolls back uncommitted transactions. When the SQL Server instance is stopped, SQL Server also changes the transactions that have been submitted and the change in the hard disk. When the recovery process is complete, SQL Server will write the following information in the SQL Server error log file:
Recovery Complete
SQL Server may not be accepted during recovery. Attempts to connect to SQL Server this time may receive a similar error message:
Timeout Expired
The SQL Server Agent service may not start because it is waiting to wait for the 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 because of the timeout error:
Recovery Complete
If the recovery process takes a long time, you may also have the failure of the recovery process.
Back to top
Testing different ways to connect to the SQL Server instance When you connect to the SQL Server instance, if you encounter a connection problem, you can use one or more methods below to solve the connection problem.
Use SQL Server authentication and Windows authentication to test the connection of the SQL Server instance. Tested from other data sources (such as ODBC DSN, .UDL files, SQL query analyzers, SQL Server Enterprise Manager, ISQL utilities, or OSQL utilities) to test the connection of the SQL Server instance. Test the connection of the SQL Server instance using another protocol. You can create a new alias for the SQL Server instance that uses the protocol to specify different protocols. You can also add TCP :, np:, lpc :, or rpc: to specify the protocol in your connection string at the beginning of the SQL Server instance name. For example, if the TCP / IP connection fails, the named pipe connection is successful. Test your connection with other login accounts so you can help you determine if the problem is related to a particular login account. Add a entry corresponding to the IP address of the computer that is running the SQL Server instance in% SystemRoot% / HOSTEM32 / DRIVERS / ETC / HOSTEM32 / DRIVERS / etc / hosts file. 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 "(locally)" replacing the server name, then connect. Try using the IP address instead of the server name to connect to the SQL Server instance. Try to specify a specific port that the SQL Server instance is listening. There are two ways: create an alias or add a port number to the connection string (such as MyServer / MyInstance, 1433).
Back to top
Capturing Network Monitor Traces If you use the step mentioned in the section of the "Test Connection to SQL Server 2000 Instance" section, you cannot resolve the connection problem, please use the Network Monitor Utility to capture the network trace. For additional information about network tracking, click the following article number to see the article in the Microsoft Knowledge Base: 148942 How to use the network monitor to capture network traffic
294818 FAQ for online monitoring
169292 Interpretation of the basics of TCP / IP tracking data
102908 How to exclude TCP / IP connection issues in Windows 2000 or Windows NT
To get more details, you need to use the SQL event probe trace. A network diagnostic tool can also be used for non-aggregated computers running SQL Server for network tracking. For additional information about how to use a network diagnostic tool, click the article number below to view the article in the Microsoft Knowledge Base:
321708 How to use Network Diagnostic Tools in Windows 2000 (NetDiag.exe)
Back to top
Refer to additional information about various connection issues related to SQL Server, click the article number below to view the article in the Microsoft Knowledge Base:
812817 Support 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 connected to the network library in the new DSN of the same name SQL Server.
306985 RPC error occurs when using a naming pipeline connection cluster virtual server
313062 How to connect to the database by using Active Server Pages in Windows 2000
313295 How to use server name parameters in the connection string to specify client network libraries
320728 How to exclude "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 reject access) error message
247931 INF: Method for verifying the connection of SQL Server in Active Server Page
169377 How to access SQL Server in Active Server Page
328383 INF: SQL Server client can change the protocol when connecting
238949 How to set SQL Server network libraries in the ADO connection string
270126 PRB: How to manage the client with two SQL Server 2000 virtual servers after the Active / Active Cluster 7.0 upgrade
316779 PRB: The client with a mandatory protocol encryption may not be connected via the IP address
216643 INF: Find the ODBC / OLEDB connection option when tracking the SQL Server 7.0 connection
265808 INF: How to connect to the SQL Server 2000 Name Instance with Early Version Client Tools
191572 Info: Connection pool management from ASP-called ADO object
313173 Sample connection pool manager for JDBC drivers for Microsoft SQL Server 2000
237844 How to enable an ODBC connection pool in a Visual Basic ADO application
259569 PRB: Install third-party products destroyed Windows 2000 MDAC registry settings
Back to top
The information in this article applies to:
Microsoft SQL Server 2000 (All Editions)
