Remote connection to the SQL server in the LAN cannot be connected error and Solution

The first error "SQL Server does not exist or access is denied" is usually the most complicated. There are many causes of the error and there are also many problems to check.
. Generally, there are several possibilities:
1. the SQL Server name or IP address is incorrectly spelled;
2. The server network configuration is incorrect;
3. The client network configuration is incorrect.
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 network physical connection:
Ping <Server IP address>
　
Or
Ping <Server Name>
If ping <Server IP address> fails, the physical connection is faulty. In this case, Check hardware devices, such as NICs, hubs, and routers. And
One possible cause is the installation of firewall software between the client and the Server, such as ISA Server. Firewall software may block ping,
Telnet and other responses. Therefore, when checking for 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, the name resolution is incorrect. In this case, check the DNS server
Whether the service is normal. Sometimes the client and server are not in the same LAN. At this time, the server name may not be used to identify the server directly.
You can use the HOSTS file for name resolution. The specific method is as follows:
1. Use notepad to open the HOSTS file (usually in C: \ WINNT \ system32 \ drivers \ etc ).
2. Add a record corresponding to the IP address and server name, for example:
172.1610.24 myserver
You can also configure it in the SQL Server Client Network utility, which will be described in detail later.
Second, use the telnet command to check the SQL Server's working status:
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
Listen to the TCP/IP connection of port 1433. If the command returns the error message "cannot open the connection", it indicates that the Server has not started the SQL Server service.
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 need to check the network configuration on the server, whether the named pipe is enabled, whether the TCP/IP protocol is enabled, and so on. We
SQL Server's built-in Server network tools can be used for inspection.
Click program> Microsoft SQL Server> Server network tools. The following figure is displayed after the tool is opened:
Here we can see which protocols are enabled on the server. 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, as shown in:
Generally, we use the default port 1433 of SQL Server. If "Hide server" is selected, the client cannot enumerate the server
To see that this server has played a protective role, but does not affect the connection.
After checking the network configuration of the server, we will go to the client to check the network configuration of the client. We can also use SQL Server's built-in
The client network is checked using a tool. The difference is that this tool is run on the client this time.
Click program> Microsoft SQL Server> client network tool. The following figure is displayed after the tool is opened:
Here we can see which protocols are enabled on the client. 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, as shown in.
The port must be the same as 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 a real service.
Device name, which can be the same or different. For example, we can use myserver to replace the real server name sql2kcn-02 and use the network
Library Named Pipes. The alias settings are similar to the HOSTS file.
Through the above checks, the cause of Error 1 can basically be ruled out. Next we will describe in detail how to solve Error 2.
When you try to use sa in the query analyzer to connect to SQL Server, or use sa in the Enterprise Manager to create an SQL Server Registration
The error message 2 is often encountered. This error occurs because SQL Server uses the "Windows only" authentication method,
Therefore, you cannot use the SQL Server Logon account (such as sa) to connect to the instance. 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 you fail to connect to SQL Server by using "use Windows Authentication" in step 1, we will encounter
Dilemma: first, the server only allows Windows authentication. Second, even if Windows authentication is used, the server cannot connect to the service.
. This situation is vividly referred to as "locking yourself out of the door", because no matter what method is used, users cannot use the connection. Actually, we
You can change the Authentication Mode to SQL Server and Windows Hybrid Authentication by modifying a registry key value. The steps are as follows:
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]
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 the 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, but you still cannot use Windows Authentication Mode to connect
SQL Server. This is because there are two default logon accounts in SQL Server: BUILTIN \ Administrators and <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.
The following registry key
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ LoginMode
The value determines the Authentication mode that SQL Server will adopt. This value is 1, indicating that the Windows Authentication mode is used; this value is 2, indicating that the Windows Authentication mode is used
Hybrid mode (Windows Authentication and SQL Server Authentication ).
After reading how to solve the first two errors, let's take a look at the third error shown in 3.
If a third error occurs, it generally indicates that the client has found this server and can be connected, but the connection time is later
Error Caused by the time allowed. This usually happens when the user runs the enterprise manager on the Internet to register another one, which is also on the Internet.
And slow connection may cause the above timeout error. In some cases, the LAN may cause such errors.
Error.
To solve this problem, you can modify the connection timeout settings of the client. By default, the time-out for registering another SQL Server through the Enterprise Manager
The setting is 4 seconds, and 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:
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.
The query analyzer can also be set at the same position.
Simply put:
/**********************************/
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 address) --> proxy port --> 1433 --> OK
5. If not:
Sqlserver client --> Start Menu --> SQLserver --> client network utility --> alias --> Add --> write alias such as "" --> "Network
Database "select TCP/IP --> server name write remote ip or Instance name --> OK