SQL Server connection exceptions and Solutions

I. [MICROSOFT] [odbc SQL server driver] [SQL server] user '(null)' login failed, not associated with a trusted SQL SERVER connection
The reason is that SQL Server uses the "Windows only" authentication method,
Therefore, you cannot use the SQL Server login account (such as sa) to connect
(Method 1) change to: Windows "authentication method
2. SQL Server does not exist or access is denied
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.
Solution:
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 reason may be that a firewall software is installed between the client and the server. You can temporarily disable or open all closed ports for ping, telnet, and other responses.
If the ping <Server IP address> succeeds, or the ping <Server Name> fails, 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 connections 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 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.
SQL Server's built-in Server network tools can be used for inspection.
Click Program-Microsoft SQL Server-Server network usage tool.
After you open the tool, 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 tool provided by SQL Server to perform the check. The difference is that this tool is run on the client this time.
Click Program-Microsoft SQL Server-client network tools.
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,
Therefore, you cannot use the Logon account of SQL Server (such as sa) for connection. The solution is as follows:
1. Use Enterprise Manager on the Server side and select "use Windows Authentication" to connect to SQL Server in Enterprise Manager
-Right-click your server instance (the one with the green icon)
-Edit SQL Server Registration attributes
-Select "use windows Authentication"
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]
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. Disable 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.
Detailed description:
The following registry key:
The value of HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ LoginMode determines the Authentication mode used by SQL Server.
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 has 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 in the "Login timeout (seconds)" box on the right under "connection settings", for example, 20.
Query settings in Analyzer:
Tool-Option-connection-set logon timeout to a large number
　　 4. Most servers use TCP/IP to succeed. Sometimes you will find that Named Pipes can be used?
This is because in WINDOWS 2000 and later operating systems, MS configures TCP/IP as the default connection protocol for SQL server to solve SQL Server security problems, you can see the order of TCP/IP and name pipe in client network utility.
You can also:
[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ Client \ SuperSocketNetLib]
"ProtocolOrder" = hex (7 ):,
The default protocol is displayed here.
Q: How can I change the SQL statements of Named Pipes and TCP/IP in a program?
A: You can modify the relative location of the registry mentioned above. The example is as follows:
· CLIENT:
[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ Client \ SuperSocketNetLib]
"ProtocolOrder" = hex (7 ):,
· SERVER:
[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ SuperSocketNetLib]
"ProtocolOrder" = hex (7 ):,
Use the enterprise manager on the Server side and select "use Windows Authentication" to connect to SQL Server
Procedure:
In Enterprise Manager
Right-click your server instance (the one with the green icon)
Edit SQL Server Registration attributes
Select "use windows Authentication"
Select "use SQL Server Authentication"
Login Name: sa, Password enter sa Password
OK (OK, just connect it to the end)
(Method 2) allow SQL Server login
In Enterprise Manager
Expand "SQL Server group", right-click the name of the SQL Server
Select "properties"
Select the "Security" tab.
Under "authentication", select "SQL Server and Windows ".
OK, and restart the SQL Server service. (OK)
Note:
(Method 1) "using Windows Authentication" fails to connect to SQL Server,
Modify the Registry to solve this 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]
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. Disable 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 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 \ 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)