Analysis of three most common error causes in SQL Server connections

server| wrong one. " SQL Server does not exist or access is denied "
This is the most complex, the cause of the error is more, need to check the aspect also more.

Generally speaking, there are several possibilities:

1,sql server name or IP address is spelled incorrectly 2, server-side network configuration is 3 incorrect, 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/server name >

If ping < server IP address > is unsuccessful, indicating that there is a problem with the physical connection, check the hardware devices, such as network cards, HUB, routers, and so on. Another possibility is caused by firewall software installed between the client and the server, such as the ISA Server. Firewall software may mask responses to ping,telnet so when checking connectivity problems, we have to temporarily shut down the firewall software or open all the blocked ports.

If ping < server IP address > success, ping < server name > failure indicates a problem with name resolution, check to see if the DNS service is normal. Sometimes the client and 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 which point 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). Add a corresponding record for the IP address and server name, such as: 172.168.10.24 myserver

2. Or configure in SQL Server's client Network Utility, which is explained in detail later.

Second, use the Telnet command to check the SQL Server server's working state 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 and listening for TCP/IP connections on port 1433 if the command returns an error message "Cannot open connection", the server side does not start SQL 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 to check whether named Pipes are enabled. TCP/IP protocol enabled and so on, you can use the SQL Server's own server network usage tool for checking.

Click: program Microsoft SQL Server server network usage tool

After you open the tool, you can see in general what protocols are enabled by the server. Generally speaking, we enable named Pipes and TCP/IP protocol. In the TCP/IP protocol in dot, select "Properties" and we can check the settings of the SQK Server service default port generally, we use SQL Server default port 1433. If you select Hide Server, 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.

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

Click: Program Microsoft SQL Server Client Network usage tool

After you open the tool, in the general item, you can see which protocols are enabled by the client. Generally speaking, 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, which must be consistent with the server.

You can also configure aliases for the server by clicking the Aliases 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. The alias settings are similar to those used in the Hosts file. Through the inspection of the above aspects, You can basically rule out the first error.

Two. " Unable to connect to server, user XXX login failed.

This error occurs because SQL Server uses Windows only authentication, so users cannot connect using SQL Server's login account, such as SA. The workaround is as follows:

1. Use Enterprise Manager on the server side and select "Use Windows Authentication" to connect to SQL Server2. Expand SQL Server Group, right-click the name of the SQL Server server, select Properties, and then select Security Tab 3. In the 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" and "Run", enter regedit, return enter Registry Editor 2. Expand the registry key sequentially and browse to the following registry keys: [HKEY_LOCAL_MACHINE Oftware\microsoft\mssqlserver\ Mssqlserver]3. Locate the name "LoginMode" on the right side of the screen, double-click to edit the double byte value 4. Change the original value from 1 to 2 and click OK 5. Close Registry Editor 6. Restart the SQL Server service.

At this point, users can successfully use the 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 logon accounts in SQL Server: builtin\ The 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 Server 2. Expand Security, right-click Login, and then click New Login 3. In the Name box, enter BUILTIN\ADMINISTRATORS4. In the Server Roles tab, select System Administrators "5. Click" OK "to exit 6. Use the same method to add < machine name >\administrator login.

Description

The following registry key: The value of HKEY_LOCAL_MACHINE Oftware\microsoft\mssqlserver\mssqlserver\loginmode determines that SQL Server will take

which authentication mode. 1. Indicates the use of Windows Authentication Mode 2. Represents the use of mixed mode (Windows Authentication and SQL Server authentication).



Three. Prompt connection timeout

If you encounter a third error, it generally means that the client has found the server and can connect, However, an error occurs because the time of the connection is greater than the allowed time. This typically happens 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 may cause the above timeout error. In some cases, This error can also be caused by network problems on the LAN.

To resolve such errors, you can modify the client's connection timeout settings by default, the timeout setting for registering another SQL Server through Enterprise Manager is 4 seconds, and the Query Analyzer is 15 seconds (which is why there is a greater likelihood of errors occurring in Enterprise Manager).

The steps are: Setup in Enterprise Manager: 1. In Enterprise Manager, select Tools on the menu, and then select Options 2. In the SQL Server Enterprise Manager Properties window that pops up, click the Advanced tab 3. Login timeout (seconds) under Connection settings In the box on the right, enter a larger number, such as 20.

Settings in Query Analyzer: Tool options Connection Sets the login timeout to a larger number



Connection Timeout changed to 0

1, the first guarantee ping pass 2, in DOS write Telnet IP 1433 will not error 3, with IP even such as Enterprise Manager: Enterprise Manager > Right key SQL Server group > new SQL Server registration > Next > Write remote instance name (IP, Machine name) > Next > Select SQL Server login > Next > Write login name and password (sa,pass) > Next > Next > 4, if not yet: SQL Server servers > Start menu > Sqlserver> Server Network Utility > Enable Winsock agent > Proxy Address: (SQL Server IP) > proxy port >1433>ok 5, if not yet: SQL Server client > Start Menu >SQLserver> Client Network Utility > alias > Add > Write alias such as "vigorously" > "Network Library" select tcp/ip> server name write remote IP or instance name >ok