Generally speaking, there are two ways to connect SQL Server, one is to take advantage of SQL Server's own client tools, such as Enterprise Manager, Query Analyzer, transaction Profiler, and so on, the second is to use the user's own development of client programs, such as ASP scripts, VB programs, client programs are using ODBC or OLE DB for connection to SQL Server. Next, we'll talk about how to solve the problem of connection failure in terms of these two ways of connecting.
One, "SQL Server does not exist or access is denied"
|
(Powerful database platform on Windows platform) failure analysis and troubleshooting of connection failures (figure I) "width=" 408 "height=" 139 "/>
(Figure 1)
|
"SQL Server does not exist or access denied" error, usually the most likely to occur, because the cause of the error is more, so need to check more aspects.
Generally speaking, there are several possibilities:
1, network connectivity issues;
2, SQL Server server problem;
3. Server-side network configuration problem;
4, the Client network configuration problem.
First, we check the network connection:
1, Ping the server IP address. First shut down the server and the client firewall, in order to exclude firewall software may be shielding the response to ping,telnet, etc. Then start ping the server IP address, if ping server IP address is not successful, indicating physical connection problems, this time to check hardware devices, such as network cards, HUB, routers and so on.
2, Ping the server name. If failure indicates a problem with name resolution, check to see if the Netbuis protocol is installed, and the DNS service is normal.
Second, use the Telnet command to check the working status of the SQL Server server. If the command succeeds, you can see the cursor flashing in the upper-left corner after the screen flashes, indicating that the SQL Server server is working properly and listening for TCP/IP connections on port 1433, and that the server does not start SQL if the command returns an error message "Cannot open connection" Server service, either the TCP/IP protocol is not enabled on the server side or the server is not listening on SQL Server's default port 1433.
Finally, we want to check the server, client network configuration. The server checks whether named pipes are enabled, whether the TCP/IP protocol is enabled, and so on. We can use SQL Server's own server network using tools to check.
Click: Program-> Microsoft SQL Server server network usage tool; When you open the tool, you can see in general what protocols are enabled by the server, and by default we enable Named Pipes and the TCP/IP protocol. TCP/IP protocol in dot, select "Properties", we can check the SQK Server service default port settings. Generally, we use SQL Server's default 1433 port. If "Hide Server" is selected, it means that the client cannot see the server by enumerating the servers, and it has a protective effect, but does not affect the connection.
|
(Strong database platform on Windows platform) failure analysis and troubleshooting of connection failures (Figure II) "Width=" height= "324"/>
(Figure 2)
(A powerful database platform on Windows platform) failure analysis and troubleshooting of connection failures (figure III) "Width=" 441 "height="/>
(Figure 3)
|
Next we go to the client to check the client's network configuration. We can also take advantage of the use of SQL Server's own client network tools to check, but this time it is on the client to run the tool. Click: After the program-> Microsoft SQL Server Client Network Use tool to open the tool, in the general item, you can see which protocols are enabled by the client. Again, we need to enable named Pipes and TCP/IP protocols. Click the TCP/IP protocol and select Properties to check the client default connection port settings, which must be consistent with the server.
|
(Strong database platform on Windows platform) failure analysis and troubleshooting of connection failures (Figure IV) "width=" height= "/>"
(Figure 4)
|
Through the above several aspects of the inspection, basically can eliminate the first error.
Second, "Unable to connect to the server, the user XXX login Failed"
|
(A powerful database platform on Windows platform) failure analysis and troubleshooting of connection failures (Figure V) "width=" 408 "height=" 126 "/>
(Figure 5)
|
This error occurs because SQL Server uses Windows only authentication, so users cannot connect using SQL Server's logon account, such as SA. The workaround looks like this:
1. Use Enterprise Manager on the server side and select "Use Windows Authentication" to connect to SQL Server;
2, expand the "SQL Server group", the right mouse click on the name of the SQL Server 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 above workaround, if you failed to connect to SQL Server using Windows authentication in step 1th, you can fix the problem by modifying the registry:
1, click "Start" "Run", input regedit, enter Registry Editor;
2, expand the key in turn, browse to the following registry: [HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer];
3, on the right side of the screen to find the name "LoginMode", double-click Edit Double byte value;
4, change the original value from 1 to 2, click "OK";
5, close Registry Editor;
6. Restart the SQL Server service.
At this point, users can successfully use SA to create new SQL Server registrations in Enterprise Manager, but still cannot use Windows Authentication mode to connect to SQL Server. This is because there are two default login users in SQL Server:
BUILTIN\Administrators
< machine name >\administrator was deleted. |
To recover both accounts, you can use the following methods:
1, open Enterprise Manager, expand the server group, and then expand the server;
2, expand Security, right-click Login, and click New Login;
3, in the "Name" box, input BUILTIN\Administrators;
4. In the Server Roles tab, select System Administrators;
5, click "OK" exit;
6, use the same method to add < machine name >\administrator login.
Description
The following registry key:
The value of Hkey_local_machine\software\microsoft\mssqlserver\mssqlserver\loginmode determines what authentication mode SQL Server will take.
1, the use of "Windows Authentication" mode;
2. Represents the use of mixed mode (Windows authentication and SQL Server Authentication).
third, prompt connection timeout
|
(A powerful database platform on Windows platform) failure analysis and troubleshooting of connection failures (Figure VI) "width=" 408 "height=" 126 "/>
(Figure 6)
|
If you encounter a third error, it is generally stated that the client has found the server and can connect, but that an error occurs because the connection time is greater than the allowed time. This is a rare situation, which typically occurs when a user runs Enterprise Manager on the Internet to register another server that is also on the Internet, and is a slow connection, which can cause more than one timeout error. In some cases, this error can also be caused by network problems in the LAN.
To resolve such an error, you can modify the client's connection timeout setting. By default, the timeout setting for registering another SQL Server through Enterprise Manager is 4 seconds, and the Query Analyzer is 15 seconds.
The specific steps are:
Settings in Enterprise Manager:
1, in Enterprise Manager, select the "Tools" on the menu, and then select "Options";
2, in the pop-up SQL Server Enterprise Manager Properties window, click on the "Advanced" tab;
3. Enter a larger number, such as 30, in the box to the right of login timeout (seconds) under Connection settings.
Settings in Query Analyzer:
Click "Tools"-> "Options"-> "Connect"; Set the logon timeout to a larger number, and the connection timeout to 0.
IV. Application connection Failure
All three of the above error messages occur in SQL Server's own client tools, and similar error messages are encountered in the application, such as:
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. Reason: not associated with a trusted SQL Server connection.
Microsoft OLE DB Provider for ODBC Drivers error ' 80004005 '.
[Microsoft] The [ODBC SQL Server Driver] timeout has expired. |
If you encounter a connection timeout error, we can modify the timeout setting for 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, we can modify the timeout setting for 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
' Same in seconds, if set to 0 means unrestricted
Rs. Open Cmd1, CN
Rs. MoveFirst
. . . |
v. Summary
This article focuses on how to troubleshoot and troubleshoot connection failures in both cases of using SQL Server client tools and user-developed applications for the common failure of connections that most users use during SQL Server. After reading this article, I believe that each reader will have a more comprehensive and in-depth understanding of how SQL Server works, how to authenticate, and how to develop applications.
