Analysis and troubleshooting of SQLServer connection failure errors

Generally, there are two ways to connect to SQL server. One is to use the client tools provided by SQL server, such as the Enterprise Manager, query analyzer, and transaction probe; the second is to use a client program developed by the user, such as ASP scripts and vbprograms. The client program uses ODBC or ole db to connect to SQL server. Next, we will discuss the two Connection Methods in detail how to solve the connection failure problem.

　　1. "SQL server does not exist or access is denied"

(WINDOWS powerful database platform) analysis and troubleshooting of connection failure errors (figure 1) "width =" 408 "height =" 139 "/> (Figure 1)

"SQL server does not exist or access is denied" errors are usually the most likely to occur, because there are many causes of errors, so there are also many aspects to check.

Generally, there are several possibilities:

1. network connection problems;

2. SQL server problems;

3. server network configuration problems;

4. Client network configuration problems.

First, check the network connection:

1. ping the Server IP address. First, shut down the firewall on the server and client to prevent the firewall software from blocking the ping and telnet responses. Then start to ping the Server IP address. If the ping server ip address fails, the physical connection is faulty. In this case, check the hardware devices, such as the NIC, HUB, and vro.

2. ping the server name. If it fails, the name resolution is incorrect. Check whether the Netbuis protocol is installed and the DNS service is normal.
Second, use the telnet command to check the operating status of the SQL server. If the command is successfully executed, you can see that the cursor does not stop flashing in the upper left corner after the screen flashes. This indicates that the SQL server is working normally and is listening for TCP/IP connections on port 1433; if the command returns an error message "unable to open the connection", it indicates that the server has not started the SQL server service, or the server has not enabled the TCP/IP protocol, or the server does not listen on the default port 1433 of SQL server.

Finally, we need to check the network configurations of the server and client. The server checks whether the named pipe is enabled, whether the TCP/IP protocol is enabled, and so on. We can use the server network tools provided by SQL server for inspection.

Click program> Microsoft SQL server network usage tool. After the tool is opened, You can see which protocols are enabled on the server in "General, by default, the named pipe and TCP/IP protocol are enabled. Click "properties" in the TCP/IP protocol to check the default port settings of the SQK Server service. Generally, we use the default port 1433 of SQL server. If you select "Hide server", it means that the client cannot see this server by enumerating the server. This protects the server, but does not affect the connection.

(WINDOWS powerful database platform) analysis and troubleshooting of connection failure errors (Figure 2) "width =" 500 "height =" 324 "/> (Figure 2) (WINDOWS powerful database platform) analysis and troubleshooting of connection failure errors (Figure 3) "width =" 441 "height =" 192 "/> (Figure 3)

Next we will go to the client to check the network configuration of the client. We can also use the client network provided by SQL server to perform the check. The difference is that this tool is run on the client this time. Click program> Microsoft SQL server Client. After you use the tool to open the tool, you can see which protocols are enabled on the client in "General. Similarly, we need to enable the named pipe and TCP/IP protocol. Click TCP/IP protocol and select "properties" to check the default connection port settings of the client. The port must be consistent with the server.

(WINDOWS powerful database platform) analysis and troubleshooting of connection failure errors (figure 4) "width =" 480 "height =" 340 "/> (Figure 4)

Through the above checks, the first error can be basically ruled out.

　　2. "unable to connect to the server, user xxx Login Failed"

(WINDOWS powerful database platform) analysis and troubleshooting of connection failure errors (figure 5) "width =" 408 "height =" 126 "/> (Figure 5)

This error occurs because SQL server uses the "Windows only" authentication method, so you cannot use the Logon account of SQL server (such as sa) to connect. The solution is as follows:

1. Use the enterprise manager on the server side and select "use Windows Authentication" to connect to SQL server;

2. Expand "SQL server group", right-click the name of the SQL server, select "properties", and then select the "Security" tab;

3. Under "authentication", select "SQL server and Windows ";

4. Restart the SQL server service.

In the preceding solution, if "use Windows Authentication" fails to connect to SQL server in step 1, modify the Registry to solve the problem:

1. Click "start" "Run", enter regedit, and press enter to enter the Registry Editor;

2. Expand the registry items in sequence and navigate to the following registry: [HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ mssqlServer];

3. Find the name "LoginMode" on the right of the screen and double-click to edit the double-byte value;

4. Change the original value from 1 to 2 and click "OK ";

5. Close the Registry Editor;

6. Restart the SQL server service.

In this case, you can successfully use sa to create an SQL server registration in the Enterprise Manager, but you still cannot connect to SQL server in Windows Authentication mode. This is because there are two default login users in SQL server:

BUILTIN \ Administrators <Machine Name> \ Administrator is deleted.

To restore these two accounts, you can use the following methods:

1. Open the Enterprise Manager, expand the server group, and then expand the server;

2. Expand security, right-click logon, and click New logon ";

3. In the Name box, enter BUILTIN \ Administrators;

4. On the "server role" tab, select "System Administrators ";

5. Click "OK" to exit;

6. Use the same method to add <machine Name> \ Administrator to log on.

Note:

The following registry key:

The value of HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ mssqlServer \ LoginMode determines the Authentication mode that SQL server will adopt.

1. indicates that the "Windows Authentication" mode is used;

2. hybrid mode (Windows Authentication and SQL server Authentication) is used ).

　　Iii. Connection timeout prompt

(WINDOWS powerful database platform) analysis and troubleshooting of connection failure errors (figure 6) "width =" 408 "height =" 126 "/> (Figure 6)

If a third error occurs, it generally indicates that the client has found this server and can be connected. However, an error occurs because the connection time exceeds the allowed time. This situation is rare. Generally, when a user runs the enterprise manager on the Internet to register another server that is also on the Internet and has a slow connection, the above timeout error may occur. In some cases, the LAN may cause such errors.

To solve this problem, you can modify the connection timeout settings of the client. By default, the timeout setting for registering another SQL server through the Enterprise Manager is 4 seconds, and the query analyzer is 15 seconds.

The procedure is as follows:

Settings in Enterprise Manager:

1. In Enterprise Manager, select "Tools" from the menu, and then select "options ";

2. In the displayed "SQL server Enterprise Manager properties" window, click the "advanced" tab;

3. Enter a large number, such as 30, in the "Login timeout (seconds)" box on the right under "connection settings.

Query settings in Analyzer:

Click "Tools"-> "options"-> "connection"; Set logon timeout to a large number, and set connection timeout to 0.

　　4. Application connection failure

The preceding three error messages occur in the client tool provided by SQL server. In the application, we also encounter similar error messages, for example:

Microsoft ole db Provider for SQL server (0x80004005) [DBNETLIB] [ConnectionOpen (Connect ().] Specified SQL server not found. Microsoft ole db Provider for SQL server (0x80004005)

User 'sa 'Login Failed. Cause: it is not associated with a trusted SQL server connection.

Microsoft ole db Provider for ODBC Drivers error '20140901 '. [Microsoft] [ODBC SQL server Driver] timeout has expired.

If a Connection timeout error occurs, you can modify the timeout settings of the Connection object in the program and then open the Connection. For example:

<% Set Conn = Server. CreateObject ("ADODB. Connection ") DSNtest = "DRIVER = {SQL server}; SERVER = ServerName; UID = USER; PWD = password; DATABASE = mydatabase" Conn. Properties ("Connect Timeout") = 15' in seconds Conn. open DSNtest %>

If you encounter a query timeout error, you can modify the timeout setting of the Recordset object in the program and then open the result set. For example:

Dim cn As New ADODB. Connection Dim rs As ADODB. Recordset ... Cmd1 = txtQuery. Text Set rs = New ADODB. Recordset Rs. Properties ("Command Time Out") = 300 'The unit is second. If it is set to 0, there is no limit. Rs. Open cmd1, cn Rs. MoveFirst ...

　　V. Summary

This article focuses on the common connection failure errors that most users encounter when using SQL server. It focuses on the use of SQL server Client tools and applications developed by users, how to diagnose and solve connection failure errors. After reading this article, I believe that every reader will have a comprehensive and in-depth understanding of the connection principle, authentication methods, and application development of SQL server.