Analysis of the three most common error in SQL Server connection

I. "SQL Server does not exist or visited"

This is the most complicated, the cause of the error occurs, and it needs to be checked.

Generally speaking, there are several possibilities:

1, SQL Server name or IP address spelling incorrectly, 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

If ping is unsuccessful, there is a problem with physical connection, this time you have to check hardware devices, such as NIC, HUB, routers, etc. There is also a possibility due to the installation of firewall software between clients and servers. For example, ISA Server. Firewall software may shield a response to ping, telnet, etc. Therefore, when checking the connection problem, we must first turn the firewall software to temporarily shut down, or open all enclosed ports.

If ping success, ping If the name parsing is problematic, then check if the DNS service is normal. Sometimes the client and server are not in the same LAN, this is likely to be directly Use the server name to identify the server, then we can use the HOSTS file to make a name resolution, the specific method is:

1. Use Notepad to open the HOSTS file (in general in C: / Winnt / System32 / Drivers / etc). Add a corresponding record of the IP address and server name, such as: 172.168.10.24 MyServer

2. Configure it in the client network utility of SQL Server, which will be described in detail later.

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

If the command is executed, you can see the screen after flashing, the cursor is in the upper left corner, which means that the SQL Server server works fine, and is listening to the TCP / IP connection of the 1433 port. If the command returns an error message that cannot be opened 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 the named pipe is enabled. Do you have a TCP / IP protocol, etc. You can use the Server network use of SQL Server to check.

Click: Program Microsoft SQL Server Server Network Tools

After opening this tool, you can see which protocols have been enabled in "General". In general, we enable named pipes and TCP / IP protocols. Point TCP / IP protocol, select "Properties", we can check SQK Server service default port settings, we use SQL Server default 1433 port. If the "hidden server" is selected, it means that the client cannot see this server by enumerating the server, but it has played the role of protection, but Does not affect the connection.

Next we are going to the client to check the client's network configuration. We can also use the client network using the SQL Server to use tools to check, the difference is this time to run this tool in the client.

Click: Program Microsoft SQL Server Client Network Tools

After opening the tool, in the General item, you can see which protocols have been enabled. In general, we also need to enable named pipes and TCP / IP protocols. Click TCP / IP protocol to select "Properties", Check the settings of the client default connection port, which must be consistent with the server.

Click the Alias ​​tab, you can also configure an alias for the server. The alias of the server is used to connect the name, the server in the connection parameter is the real server name, the two can be the same or different. Alias ​​settings and use HOSTS files There is an similarity. By the above aspects, the first error can basically exclude.

II. "Unable to connect to the server, user XXX landing failed"

The reason for this error is because SQL Server uses the "only Windows" authentication mode, so users cannot connect to SQL Server login accounts (such as SA). Solutions As shown below: 1. Enterprise Management in Server The device, and select "Use Windows Authentication" to connect SQL Server2. Expand "SQL Server Group", right-click the name of the SQL Server server, select "Properties", select "Security" tab 3. In "Authentication "Under, select" SQL Server and Windows ". 4. Restart SQL Server service.

In the above solution, if you use the "Using Windows Authentication" to connect to SQL Server in step 1, you will resolve this issue by modifying the registry:

1. Click "Start" "Run", enter regedit, Enter the registry editor 2. Expand the registry key in turn, browse to the following registry key: [HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLServer / MSSQLServer] 3. On the screen The name "Loginmode" is found, double-click Edit Double-byte value 4. Change the original value from 1 to 2, click "OK" 5. Turn off the Registry Editor 6. Restart SQL Server service.

At this point, the user can successfully use SA to create new SQL Server registration in the Enterprise Manager, but it is still unable to use the Windows authentication mode to connect SQL Server. This is because there are two default login accounts in SQL Server: Builtin / Administrators / Administrator is deleted. To restore these two accounts, you can use the following methods:

1. Open Enterprise Manager, expand the server group, then expand the server 2. Expand "Security", right-click "Login", then click New Login 3. In the Name box, enter Builtin / Administrators4. In the Server Role tab, select "System Administrators" 5. Click "OK" to exit 6. Add the / Administrator login using the same method.

Description:

The following registration key: hkey_local_machine / software / microsoft / mssqlserver / mssqlserver / loginmode determines SQL Server will take

What kind of authentication mode .1. Represents the use of "Windows Authentication" mode 2. Indicates using mixed mode (Windows Authentication and SQL Server Authentication).

3. Tip connection timeout

If you encounter a third error, in general, the client has already found this server and can be connected, but it is wrong because the time is greater than the allowable time. This is generally when the user is Running the Enterprise Manager on the Internet to register another server on the Internet, and it is possible to cause the above timeout error. Some cases, due to the network problems of the LAN, this error can also cause such errors. .

To resolve such an error, you can modify the client's connection timeout setting. By default, the timeout setting of another SQL Server is registered by the Enterprise Manager is 4 seconds, and the query analyzer is 15 seconds (which is why in the Enterprise Manager The likelihood of errors is relatively large.).

The specific steps are: Settings in Enterprise Manager: 1. In the Enterprise Manager, select "Tools" on the menu, select "Options" 2. In the "SQL Server Enterprise Manager Properties" window that pops up, click " Advanced tab 3. Enter a relatively large number in the box under "Login Timeout (Seconds)" on the "Connection Settings", such as 20.

The settings in the query analyzer: Tools Options Connection will be set to a larger digital connection timeout change to 0

1, first guarantee ping pass 2, write Telnet IP 1433 under DOS Will not report error 3, use IP as Enterprise Manager: Enterprise Manager> Right-click SQLServer Group> New SQLServer Registration> Next> Write Remote Instance Name ( IP, machine name)> Next> Select SQLServer Login> Next> Write the login name and password (SA, pass)> Next> Next> Complete 4, if you can't: SQLServer Server> Start Menu> SQLServer> Server Network Utilities> Enable WINSOCK Agent> Proxy Address: (SQLServer Server IP)> Proxy Port> 1433> OK 5, if you can't: SQLServer client> Start menu> SQLServer> Client Network Utility> Alias> Add> Write Entry name, such as "Vital"> "Network Library" selection TCP / IP> server name writes to remote IP or instance name> OK