SQL Server unreachable in remote LAN connection error and resolution _MSSQL

The first error "SQL Server does not exist or access is denied" is usually the most complex, the cause of the error is more, there is more to be examined
。 Generally speaking, there are several possibilities:
1, the SQL Server name or IP address spelling error;
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
　
Or
Ping Server name >
If the ping server IP address failed, indicating that there is a problem with physical connectivity, this time to check hardware devices, such as network cards, HUB, routers and so on. And also
One possibility may be caused by firewall software installed between the client and the server, such as ISA server. Firewall software may block the ping,
Telnet and so on, 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 and Ping server name is failed, then there is a problem with name resolution, this time to check DNS service
Service is normal. Sometimes the client and the server are not in the same LAN, it is very likely that the server name can not be used to identify the server directly, which
When we can 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).
2, add an IP address and server name of the corresponding records, such as:
172.168.10.24 MyServer
It can also be configured in SQL Server's client Network Utility, which is described later.
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 is
Listens for TCP/IP connections on port 1433 and if the command returns an error message "Cannot open connection", the server side does not start the SQL Server service
, the TCP/IP protocol may not be 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, check whether named Pipes are enabled, whether the TCP/IP protocol is enabled, and so on. We
You can use SQL Server's own server network usage tools for checking.
Click: Program-> Microsoft SQL Server-> Server network Usage tool, after opening the tool to see the screen as shown in the following image:
From here we can see what protocols are enabled by the server. Generally speaking, we enable named Pipes and TCP/IP protocols.
TCP/IP protocol in dot, select "Properties" and we can check the settings for the SQK Server service default port, as shown in the following illustration:
Generally, we use SQL Server's default 1433 port. If "Hide Server" is selected, it means that the client cannot enumerate the server
To see this server, it has a protective effect, but does not affect the connection.
After checking the server-side network configuration, we will then go to the client to check the client's network configuration. We can also take advantage of SQL Server self
The client network uses tools to check, but this time it is running this tool on the client.
Click: Program-> Microsoft SQL Server-> Client Network usage tool, after opening the tool, see the picture below:
From here we can see what 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 as shown in the following figure.
The port 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 service
Name, which can be the same or different. In the following illustration, we can use MyServer instead of the real server name sql2kcn-02 and use the network
Library Named pipes. The alias settings are similar to those used in the Hosts file.
Through the above several aspects of the examination, error 1 The cause of the occurrence can basically be excluded. Let's describe in detail how to troubleshoot error 2.
When a user attempts to connect to SQL Server using SA in Query Analyzer, or use SA to create a new SQL Server registration in Enterprise Manager
, you will often experience the error message shown in Figure 2. This error occurs because SQL Server uses the Windows only authentication method,
Therefore, users cannot connect using SQL Server's login 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, then we will encounter a
Dilemma: First, the server only allows Windows authentication, and second, the service cannot be connected even with Windows authentication
Manager This situation is vividly referred to as "locking yourself out of the door" because, in any way, the user cannot use the connection. In fact, we
You can change the authentication mode to SQL Server and Windows mixed authentication by modifying a registry key, as follows:
1, click "Start"-"Run", input regedit, enter the 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 value;
4, change the original value from 1 to 2, click "OK";
5, close Registry Editor;
6. Restart the SQL Server service.
At this point, users can successfully use SA to create new SQL Server registrations in Enterprise Manager, but still cannot use Windows Authentication mode to connect
SQL Server. This is because there are two default login accounts in SQL Server: BUILTIN\Administrators and 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 click New Login;
3, in the "Name" box, input BUILTIN\Administrators;
4. In the Server Roles tab, select System Administrators;
5, click "OK" exit;
6, use the same method to add the machine name >\administrator login.
The following registry key
Hkey_local_machine\software\microsoft\mssqlserver\mssqlserver\loginmode
Determines what authentication mode SQL Server will take. The value is 1, which means that the Windows Authentication mode is used; The value is 2, which indicates the use of the
Mixed Mode (Windows authentication and SQL Server authentication).
After watching how to solve the first two errors, let's take a look at the third error shown in Figure 3.
If you encounter a third error, it generally means that the client has found the server and can connect, but because the connection time is greater than
The allowed time causes an error. This situation typically occurs when a user runs Enterprise Manager on the Internet to register another computer that is also on the Internet
Server and is a slow connection, it is possible to cause the timeout error above. In some cases, due to network problems in the LAN, this can also lead to this error
Miss.
To resolve such an error, you can modify the client's connection timeout setting. By default, a timeout for registering another SQL Server through Enterprise Manager
The setting 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:
1, in Enterprise Manager, select the "Tools" on the menu, and 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.
It can also be set in the same location in the Query Analyzer.
The simple point is:
/**********************************/
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 to 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 Utility--> alias--> add--> write alias such as "vigorously-->" network
Library Select Tcp/ip--> Server name write to remote IP or instance name-->ok