SQL Server connection failed error and resolution

In the process of using SQL Server, the number of users encountered is that the connection failed. In general, there are two ways to connect SQL Server, one is to use the client tools that come with SQL Server, such as enterprise manager, query analyzer, transaction detector, etc .; Second, the client program developed by users. Such as an ASP script, VB program, etc., the client program is also connected to SQL Server using ODBC or OLE DB. Below, we will talk about how to resolve the connection failure on these two ways.

First, the client tool connection failed

The most common error is as follows when connecting SQL Server using SQL Server (Using Enterprise Manager as an example).

1, SQL Server does not exist or visited

ConnectionOpen (connection)

2, the user 'sa' failed. Cause: Not associated with trusted SQL Server connections.

3, timeout has expired.

Below we describe how to solve these three most common connection errors.

The first error "SQL Server does not exist or access" is often the most complicated, and there are more reasons for error, and there is more ways to check. Generally speaking, there are several possibilities:

1, the SQL Server name or IP address spelling is incorrect;

2, the server-side network configuration is incorrect;

3, client network configuration is incorrect.

To solve this problem, we generally follow the following steps to find out the cause of the error.

First, check the network physical connection:

PING

or

PING

If ping fails, there is a problem with physical connection, this time you want to check hardware devices, such as NIC, HUB, router, etc. There is also a possibility that the client and the server have a firewall software, such as ISA Server. Firewall software may shield a response to ping, telnet, etc., so when checking the connection problem, we must first turn the firewall software to close, or open all closed ports.

If the Ping success and ping If the name parsing is problematic, you have to check if the DNS service is normal. Sometimes the client and server are not in the same local area network, which is likely to use the server name directly to identify the server. At this time we can use the HOSTS file to perform name resolution, the specific method is:

1. Use Notepad to open the HOSTS file (in general in C: / WinNT / System32 / Drivers / etc).

2, add a corresponding record of the IP address and the server name, such as:

172.168.10.24 MyServer

You can also configure it in the client network utility of SQL Server, which will be described in detail later.

Second, check the SQL Server server working status using the Telnet command:

Telnet 1433

If the command is executed successfully, you can see the screen after flashing, the cursor does not flash in the upper left corner, which means that the SQL Server server is working properly and is listening to the TCP / IP connection of the 1433 port; if the command returns "Unable to open the connection" error Information, the server side does not start the SQL Server service, or the server-side does not enable the TCP / IP protocol, or the server side does not listen on the default port 1433 of SQL Server. Next, we have to check the server-side network configuration on the server, check whether named pipes are enabled, and the TCP / IP protocol is enabled. We can use the Server network using the SQL Server to check.

Click: Program -> Microsoft SQL Server -> Server Network Using Tools, the screen you see after opening the tool is shown below:

From here we can see which protocols have been enabled. In general, we enable naming pipes and TCP / IP protocols.

TCP / IP protocol, select "Properties", we can check the settings of the SQK Server service default port, as shown below:

In general, we use SQL Server default 1433 ports. If the Hidden Server is selected, it means that the client cannot see this server by enumerating the server, but does not affect the connection.

Check the server-side network configuration, then we want to check the client's network configuration. We can also use the client network used by SQL Server to check, the difference is that this time is the client running this tool.

Click: Program -> Microsoft SQL Server -> Client Network Using Tools, the screen you see after opening the tool is shown below:

From here we can see which protocols have been enabled. In general, we also need to enable naming pipes and TCP / IP protocols.

Click the TCP / IP protocol, select Properties, you can check the settings of the client default connection port, as shown below.

This port must be consistent with the server.

Click the Alias ​​tab, you can also configure alias for the server. The alias of the server is the name used to connect, and the server in the connection parameter is a real server name, both of which may be the same or different. As shown in the figure, we can use MyServer instead of the true server name SQL2KCN-02, and use the network library Named Pipes. The settings of the alias are similar to using the HOSTS file.