SQL Server connection failure error and resolution [1]

server| Error | Resolving the problem with SQL Server is that the most common problems users encounter are the failure of the connection. In general, 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, to connect 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, client tool connection failed

When you connect to SQL Server with SQL Server's own client tools (for example, in Enterprise Manager), the most common errors are as follows:

1. SQL Server does not exist or access is denied

ConnectionOpen (Connect ())

　　




2, user ' sa ' login failed. Reason: not associated with a trusted SQL Server connection.

　　




3, timeout has expired.

　　


Here's how to solve the three most common connection errors in turn.

The first error "SQL Server does not exist or access is denied" is usually the most complex, the cause of the error is more, there is more to be examined. Generally speaking, there are several possibilities:

1, the SQL Server name or IP address spelling error;

2, server-side network configuration is incorrect;

3, the client network configuration is incorrect.

To solve this problem, we generally 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 the ping server IP address failed, indicating that there is a problem with physical connectivity, this time to check hardware devices, such as network cards, HUB, routers and so on. Another possibility is that the firewall software is installed between the client and the server, such as ISA server. Firewall software may block the response to Ping, Telnet, and so on, so when checking connectivity problems, we have to temporarily shut down the firewall software or open all the blocked ports.

If the ping server IP address is successful and ping the server name fails, there is a problem with name resolution, so check to see if the DNS service is normal. Sometimes the client and the server are not in the same LAN, it is very likely that the server name can not be used directly to identify the server, at this time we may use the Hosts file for name resolution, the specific method is:

1, use Notepad to open the Hosts file (usually located in C:\WINNT ystem32\drivers\etc).

2, add an IP address and server name of the corresponding records, such as:

172.168.10.24 MyServer

It can also be configured in SQL Server's client Network Utility, which is described later.

Second, use the Telnet command to check the working status of the SQL Server server:

Telnet Server IP Address > 1433

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 side 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 side is not listening on SQL Server's default port 1433.

Next, we check the server-side network configuration on the server, check 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, after opening the tool to see the screen as shown in the following image:

　　


From here we can see what protocols are enabled by the server. Generally speaking, we enable named Pipes and TCP/IP protocols.

TCP/IP protocol in dot, select "Properties" and we can check the settings for the SQK Server service default port, as shown in the following illustration:

　　




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.

After checking the server-side network configuration, we will then 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: Program-> Microsoft SQL Server-> Client Network usage tool, after opening the tool, see the picture below:

　　


From here we can see what protocols are enabled by the client. In general, we also 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 as shown in the following figure.

　　


The port must be consistent with the server.

You can also configure aliases for the server by clicking the Alias tab. The server alias is the name used for the connection, and the server in the connection parameter is the true server name, which can be the same or different. In the following illustration, we can use MyServer instead of the real server name sql2kcn-02 and use the network library Named pipes. The alias settings are similar to those used in the Hosts file.