Summary SQL server connection failure errors and Solutions

When using SQL Server, the most common problem you encounter is that the connection fails. 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.

I. Client tool connection failed
When using the client tool provided by SQL Server (taking the Enterprise Manager as an example) to connect to SQL Server,
1. connectionopen (connect () is rejected because SQL server does not exist or access is denied ())

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

3. the timeout has expired.
The following describes how to solve the three most common connection errors.

The first error "SQL server does not exist or access is denied" is usually the most complicated. There are many causes of the error and there are many aspects to check. Generally, there are several possibilities:
1. the SQL server name or IP address is incorrectly spelled;
2. The server network configuration is incorrect;
3. The client network configuration is incorrect.
To solve this problem, we generally need to follow the steps below to find out the cause of the error step by step.
First, check the network physical connection:
Ping <Server IP address>

Or
Ping <Server Name>
If Ping <Server IP address> fails, the physical connection is faulty. In this case, Check hardware devices, such as NICs, hubs, and routers. Another possible cause is the installation of firewall software between the client and the server, such as ISA Server. Firewall software may block responses to ping, telnet, and so on. Therefore, when checking connection problems, we must temporarily disable the firewall software or open all closed ports.
If the ping <Server IP address> succeeds and the ping <Server Name> fails, it indicates that there is a problem with name resolution. In this case, check whether the DNS service is normal. Sometimes the client and server are not in the same LAN. At this time, the server name may not be used to identify the server. At this time, we can use the hosts file
1. Use notepad to open the hosts file (usually in C: winntsystem32driversetc)
2. Add a record corresponding to the IP address and server name, for example:
172.1610.24 myserver
You can also configure it in the SQL Server Client Network utility, which will be described in detail later.
Second, use the Telnet command to check the SQL server's working status:
Telnet <Server IP addresses> 1433
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 SQL Server service is not started on the server, or the TCP/IP protocol is not enabled on the server, or the server does not listen on the default port 1433 of SQL Server. Next, we need to check the network configuration on the server, 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> server network tools. The following figure is displayed after the tool is opened:

Here we can see which protocols are enabled on the server. In general, we enable Named Pipes and
Click "properties" in the TCP/IP protocol to check the default port settings of the sqk Server Service, as shown in:

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. After checking the network configuration of the server, we will go to the client to check the network configuration of the client. We can also use the client network tool that comes with SQL Server to perform the check. The difference is that this time when the customer clicks program> Microsoft SQL Server> client network tool, the following figure is displayed after the tool is opened:

Here we can see which protocols are enabled on the client. In general, we also need to enable the named pipe and click the TCP/IP protocol and select "properties" to check the default connection port settings of the client, as shown in
.

The port must be the same as the server.
Click the "alias" tab to configure an alias for the server. The server alias is the name used for connection.
The server in the connection parameter is the real server name. The two can be the same or different. For example, we can use
Myserver replaces the Real Server Name sql2kcn-02 and uses the network library named pipes. Alias settings and
The hosts file is similar.

Through the above checks, the cause of Error 1 can basically be ruled out. Next we will detail
When you try to use SA in the query analyzer to connect to SQL Server, or use
When SA creates an SQL Server registration, it often encounters the error message 2. The cause of this error is
Because SQL server uses the "Windows only" authentication method, you cannot connect to SQL Server login accounts (such as SA. The solution is as follows:
1. Use the enterprise manager on the server side and select "use Windows Authentication" to connect to SQL
2. Expand "SQL Server group", right-click the name of the SQL Server server, select "properties", and then
3. Under "authentication", select "SQL Server and Windows ".
4. Restart the SQL Server service.
In the above solution, if you fail to connect to SQL server by using "use Windows Authentication" in step 1, we will encounter a dilemma: first, the server only supports Windows authentication. Second, even if Windows authentication is used, the server cannot be connected. This situation is vividly referred to as "locking yourself out of the door", because no matter what method is used, users cannot use the connection. In fact, we can change the authentication method to SQL Server and Windows Hybrid Authentication by modifying a registry key value.
1. Click "start"-"run", enter regedit, and press enter to enter the Registry Editor;
2. Expand the registry key in sequence and browse to the following registry key:
[Hkey_local_machinesoftwaremicrosoftmssqlservermssqlserver]
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.
At this time, you can successfully use SA to create an SQL Server registration in the Enterprise Manager, but still cannot
Connect to SQL Server in Windows Authentication mode. This is because there are two default logon accounts in SQL Server.
User: builtinadministrators and <machine Name> administrator are deleted. To restore these two accounts
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 builtinadministrators;
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.
The following registry key
Hkey_local_machinesoftwaremicrosoftmssqlservermssqlserverloginmode
The value determines the Authentication mode that SQL server will adopt. This value is 1, indicating that the windows identity is used
Authentication mode. If this value is 2, hybrid mode is used (Windows Authentication and SQL Server Authentication ).
After reading how to solve the first two errors, let's take a look at the third error shown in 3.
If the third error occurs, it generally indicates that the client has found this server and can connect to it.
However, an error occurs because the connection time exceeds the allowed time. This usually happens when the user
When you run the enterprise manager on the Internet to register another server that is also on the Internet and is using 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 is 4 seconds, and the query analyzer is 15 seconds (which is why
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 in the "Login timeout (seconds)" box on the right under "connection settings", for example, 20
The query analyzer can also be set at the same position.
2. Application connection failed
All the above three error messages occur in the client tool provided by SQL Server. In the application, I
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.
First, let's take a detailed look at the following to learn about using ODBC and using ole db connection
What are the differences between SQL Server and SQL server.

From this, we can see that in actual use, the application creates and uses various ADO objects, ADO
The ole db provider used by the object framework call. To access the SQL Server database, OLE DB provides two
The same method: ole db provider for SQL Server and OLE DB provider for ODBC. None
The same method corresponds to two different connection strings. The standard connection string is written as follows:
1. Use the ole db provider for SQL Server:
Use SQL Server Authentication:
Oconn. Open "provider = sqloledb ;"&_
"Data Source = myservername ;"&_
"Initial catalog = mydatabasename ;"&_
"User ID = myusername ;"&_
"Password = mypassword"
Use Windows authentication (trusted connection ):
Oconn. Open "provider = sqloledb ;"&_
"Data Source = myservername ;"&_
"Initial catalog = mydatabasename ;"&_
"Integrated Security = sspi"
2. Use the ole db provider for ODBC (do not use the ODBC Data Source ):
Use SQL Server Authentication:
Oconn. Open "driver = {SQL Server };"&_
"Server = myservername ;"&_
"Database = mydatabasename ;"&_
"Uid = myusername ;"&_
"Pwd = mypassword"
Use Windows authentication (trusted connection ):
Oconn. Open "driver = {SQL Server };"&_
"Server = myservername ;"&_
"Database = mydatabasename ;"&_
"Trusted_connection = yes"
3. Use the ole db provider for ODBC (using the ODBC Data Source ):
Oconn. Open "DSN = mysystemdsn ;"&_
"Uid = myusername ;"&_
"Pwd = mypassword"
If the connection fails, we only need to combine the connection string in the program according to the method shown in
Check to solve the problem. Note the following:
1. When configuring the ODBC data source, click the "client" configuration option to let us specify the network library used for connection
, Port number, and other attributes, as shown in:

2. If a connection timeout error occurs, we can modify the timeout settings of the connection object in the program.
<%
Set conn = server. Createobject ("ADODB. Connection ")
Dsntest = "driver = {SQL Server}; server = servername; uid = user; Pwd = pass
Conn. properties ("Connect timeout") = 15' in seconds
Conn. Open dsntest
%>
3. If a query timeout error occurs, we can modify the timeout setting of the recordset object in the program.
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
...
Iii. Summary
This article focuses on common connection failure errors that most users encounter when using SQL Server.
How to diagnose and solve connection failures when using SQL Server Client tools and user-developed applications
Failed error. After reading this article, I believe that every reader will connect to SQL Server
And application development. All the tests or examples in this article are
Windows 2000 Advanced Server + SQL Server 2000 Enterprise Edition.