Three common error Analysis _mssql SQL Server connections

Three common error analysis in SQL Server connections
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. Incorrect spelling of SQL Server name or IP address
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/server name >
If ping < server IP address > is unsuccessful, indicating that there is a problem with the physical connection, check hardware devices such as NIC, HUB, router, etc.
Another possibility is that the firewall software is installed between the client and the server, such as ISA server. Firewall software may mask the response to ping,telnet, so when checking connectivity problems, we need to temporarily shut down the firewall software or open all the blocked ports.
If ping < server IP address > succeeds, ping < server name > fails, there is a problem with name resolution, so check to see if the DNS service is working properly.
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\system32\drivers\etc).
Add a corresponding record for the IP address and server name, such as:
172.168.10.24 MyServer
2, or in the SQL Server's Client Network Utility to configure, the following will be explained in detail.
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, if the command returns an "unable to open connection" error message, 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. Whether the TCP/IP protocol is 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 protocols.
Point in the TCP/IP protocol, select "Properties", we can check the SQK Server service default port settings
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.
Next we go to the client to check the client's network configuration
We can also take advantage of SQL Server's own client network usage tools for checking,
The difference is that this is the client to run this 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.
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, which 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. The alias settings are similar to those used in the Hosts file.
Through the above several aspects of the inspection, basically can eliminate the first error.
Second, "Unable to connect to the server, the user XXX login Failed"
This error occurs because SQL Server uses Windows only authentication, so users cannot connect using SQL Server's logon account, such as SA. The workaround looks like this:
1. Use Enterprise Manager on the server side and select "Use Windows Authentication" to connect to SQL Server
2, expand the "SQL Server group", the right mouse click on the name of the SQL Server 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 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" "Run", input regedit, enter Registry Editor
2, expand the registry keys in turn, and browse to the following registry key:
[HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
\MSSQLServer]
3, on the right side of the screen to find the name "LoginMode", double-click Edit Double-byte values
4, change the original value from 1 to 2, click "OK"
5. Close Registry Editor
6. Restart the SQL Server service.
At this point, the user can successfully use SA to create a new SQL Server registration in Enterprise Manager.
However, you still cannot use Windows Authentication mode to connect to SQL Server.
This is because there are two default logon accounts in SQL Server:
BUILTIN\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 the server
2, expand Security, right-click Login, and then click New Login
3, in the "Name" box, enter BUILTIN\Administrators
4, in the Server Roles tab, select System Administrators
5, click "OK" exit
6, use the same method to add < machine name >\administrator login.
Description
The following registry key:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
\mssqlserver\loginmode
Determines what authentication mode SQL Server will take.
1, the use of Windows authentication mode
2. Represents the use of mixed mode (Windows authentication and SQL Server authentication).
Third, prompt connection timeout
If you encounter a third error, it is generally stated that the client has found the server and can connect, but that an error occurs because the connection time is greater than the allowed time.
This situation typically occurs 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 in the LAN.
To resolve such an error, you can modify the client's connection timeout setting.
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 specific steps are:
Settings in Enterprise Manager:
1. In Enterprise Manager, select "Tools" on the menu, then select "Options"
2, in the pop-up SQL Server Enterprise Manager Properties window, click on the "Advanced" tab
3. Enter a larger number, such as 20, in the box to the right of login timeout (seconds) under Connection settings.
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 to write Telnet IP 1433 does not complain
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 > Complete
4, if not yet:
SQL Server > 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 Utilities > alias > Add > Write alias such as "vigorously" > "Network Library" select tcp/ip> server name write remote IP or instance name > Okay, OK.