SQL Server connection Error Analysis

SQL Server connection Error Analysis
1. "SQL Server does not exist or access is denied"
This is the most complex. There are many causes of errors and there are many aspects to check.
Generally, there are several possibilities:
1. incorrect spelling of the SQL Server name or IP address
2. Incorrect server network configuration
3. Client Network Configuration Error
To solve this problem, we generally need to follow the steps below to find out the cause of the error step by step.
First, check the physical connection of the network.
Ping <Server IP Address/Server Name>
If the ping <Server IP address> fails, the physical connection is faulty. In this case, Check hardware devices, such as NICs, hubs, and routers.
Another possible cause is the installation of firewall software between the client and the Server, such as ISA Server. Firewall software may block responses to ping and telnet. Therefore, when checking connection problems, we must temporarily disable the firewall software or open all closed ports.
If the ping <Server IP address> succeeds and the ping <Server Name> fails, it indicates that there is a problem with name resolution. In this case, check whether the DNS service is normal.
Sometimes the client and server are not in the same LAN. At this time, you may not be able to directly use the server name to identify the server. At this time, you can use the HOSTS file for name resolution. The specific method is:
1. Use notepad to open the HOSTS file (usually in C: \ WINNT \ system32 \ drivers \ etc ).
Add a record corresponding to the IP address and server name, for example:
172.1610.24 myserver
2. configure it in the SQL Server Client Network utility, which will be described in detail later.
Second, use the telnet command to check the working status of the SQL Server.
Telnet <Server IP addresses> 1433
If the command is successfully executed, you can see that the cursor keeps flashing in the upper left corner after the screen flashes. This indicates that the SQL Server is working normally and is listening for TCP/IP connection at port 1433, if the command returns an error message "unable to open the connection", it indicates that the SQL Server service is not started on the Server, or the TCP/IP protocol is not enabled on the Server, or the Server does not listen on the default port 1433 of SQL Server.
Next, we will go to the server and check whether the named pipe is enabled. Whether the TCP/IP protocol is enabled or not. You can use the Server network tools provided by SQL Server to check whether TCP/IP protocol is enabled.
Click: Microsoft SQL Server network usage Tool
After the tool is turned on, you can see which protocols are enabled on the server in "General.
In general, we enable Named Pipes and TCP/IP protocols.
Click "properties" in the TCP/IP protocol to check the default port settings of the SQK Server service.
Generally, we use the default port 1433 of SQL Server. If you select "Hide server", it means that the client cannot see this server by enumerating the server. This protects the server, but does not affect the connection.
Next we will go to the client to check the network configuration of the client.
We can also use the client network tools provided by SQL Server to perform checks,
The difference is that this tool is run on the client this time.
Click: Microsoft SQL Server client network usage Tool
After you open the tool, you can see which protocols are enabled on the client in "General.
In general, we also need to enable the named pipe and TCP/IP protocol.
Click TCP/IP protocol and select "properties" to check the default connection port settings of the client. The port must be consistent with the server.
Click the "alias" tab to configure an alias for the server. The server alias is the name used for connection. The server in the connection parameter is the real server name. The two can be the same or different. The alias settings are similar to the HOSTS file.
Through the above checks, the first error can be basically ruled out.
2. "unable to connect to the server, user xxx Login Failed"
This error occurs because SQL Server uses the "Windows only" authentication method, so you cannot use the Logon account of SQL Server (such as sa) to connect. The solution is as follows:
1. Use the enterprise manager on the Server side and select "use Windows Authentication" to connect to SQL Server
2. Expand "SQL Server group", right-click the name of the SQL 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 preceding solution, if "use Windows Authentication" fails to connect to SQL Server in step 1, modify the Registry to solve the problem:
1. Click "start" "Run", enter regedit, and press enter to enter the Registry Editor.
2. Expand the registry key in sequence and browse to the following registry key:
[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer
\ MSSQLServer]
3. Find the name "LoginMode" on the right of the screen and 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.
In this case, you can successfully use sa to create an SQL Server registration in the Enterprise Manager,
However, you still cannot connect to SQL Server in Windows Authentication mode.
This is because there are two default logon accounts in SQL Server:
BUILTIN \ Administrators
<Machine Name> \ Administrator is deleted.
To restore these two accounts, you can use the following methods:
1. Open the Enterprise Manager, expand the server group, and then expand the server
2. Expand security, right-click logon, and click New logon"
3. In the Name box, enter BUILTIN \ Administrators
4. On the "server role" tab, select "System Administrators"
5. Click "OK" to exit
6. Use the same method to add <machine Name> \ Administrator to log on.
Note:
The following registry key:
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer
\ MSSQLServer \ LoginMode
The value determines the Authentication mode that SQL Server will adopt.
1. indicates that the "Windows Authentication" mode is used.
2. hybrid mode (Windows Authentication and SQL Server Authentication) is used ).
Iii. Connection timeout prompt
If a third error occurs, it generally indicates that the client has found this server and can be connected. However, an error occurs because the connection time exceeds the allowed time.
This usually occurs when a user runs the enterprise manager on the Internet to register another server that is also on the Internet and is using a slow connection, the above timeout error may occur. In some cases, the LAN may cause such errors.
To solve this problem, you can modify the connection timeout settings of the client.
By default, the timeout setting for registering another SQL Server through the Enterprise Manager is 4 seconds, the query analyzer is 15 seconds (this is also the reason why there is a high possibility of errors in the Enterprise Manager ).
The procedure is as follows:
Settings in Enterprise Manager:
1. In Enterprise Manager, select "Tools" from the menu, and then select "options"
2. In the displayed "SQL Server Enterprise Manager properties" window, click the "advanced" tab.
3. Enter a large number, such as 20, in the "Login timeout (seconds)" box on the right under "connection settings.
Query settings in Analyzer:
Tool option connection sets logon timeout to a large number
The connection timeout value is changed to 0.
1. ping is guaranteed first.
2. No error will be reported when telnet ip 1433 is written in dos
3. Use an ip address to connect to the Enterprise Manager:
Enterprise Manager> right-click SQlserver group> Create sqlserver registration> next> Write Remote Instance name (IP address, machine name)> next> select Sqlserver login> next> write login name and password (sa, pass)> next> complete
4. If not:
Sqlserver Server> Start Menu> SQLserver> server network utility> enable WinSock Proxy> proxy address: (sqlserver Server IP)> proxy port> 1433> OK
5. If not:
Sqlserver client> Start Menu> SQLserver> client network utility> alias> Add> write alias such as ""> "network library" select TCP/IP> server name write remote ip or instance name> OK