SQL Server connection exceptions and solutions summary _mssql

One: [MICROSOFT][ODBC SQL Server Driver][sql Server] user ' (null) ' login failed, not associated with trusted SQL server connection
The reason is that SQL Server uses the Windows only authentication method.
Therefore, users cannot connect using SQL Server's login account (such as SA)
(method I) Change to: Windows "authentication method
second, SQL Server does not exist or access is denied
In general, there are several possibilities:
The 1.SQL server name or IP address is spelled incorrectly.
2. Incorrect server-side network configuration.
3. The client network configuration is incorrect.
Workaround:
First, check the network physical connection
Ping < server IP address/server name >
If the ping< server IP address > is not successful, indicating that there is a physical connection problem, this time to check hardware devices, such as network cards, HUB, routers and so on.
Another possibility is that the firewall software is installed between the client and the server, and you can try to temporarily turn off or open all blocked ports because of the response to ping,telnet, etc.
If the ping< server IP address > Success and,ping< server name > Failure indicates a problem with name resolution, check to see if the DNS 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 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 configure in SQL Server's client Network Utility, which is explained in detail 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 and listening for TCP/IP connections on port 1433.
If the command returns an error message "Cannot open connection", the server side does not start the SQL Server service, or the TCP/IP protocol is not enabled on the server side, or the server side is not listening on the SQL Server default port 1433.
Next, we check the server-side network configuration on the server to check whether named Pipes are enabled. TCP/IP protocol enabled and so on
You can use SQL Server's own server network usage tools 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.
TCP/IP protocol in dot, select "Properties", we can check the SQK Server service default port settings. In general, we use SQL Server's default 1433 port. If you select Hide Server, it means that the client cannot see the server by enumerating the servers. Plays a protective role, 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 the use of SQL Server's own client network tools to check, but this time it is on the client to run the 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 Aliases 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 the Windows only authentication method,
As a result, users cannot connect using SQL Server's login account, such as SA, as shown in the following ways:
1. Use Enterprise Manager on the server side and select "Use Windows Authentication" to connect SQL Server in Enterprise Manager
-Right click on your server instance (that's the one with the green icon)
-Edit SQL Server Registration properties
-Select Use Windows authentication
2. Expand SQL Server Group, right-click the name of the SQL Server server, select Properties, and 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 key sequentially and browse to the following registry keys:
[HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer]
3. Locate the name "LoginMode" on the right-hand side 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.
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 the 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. On the Server Roles tab, select System Administrators
5. Click "OK" to exit
6. Use the same method to add < machine name >\administrator login.
Detailed Description:
The following registry key:
The value of Hkey_local_machine\software\microsoft\mssqlserver\mssqlserver\loginmode determines what authentication mode SQL Server will take.
1. Means using Windows authentication mode
2. Indicates 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 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 can cause the above timeout error. In some cases, this error can be caused by network problems on 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, and then select options
2. In the SQL Server Enterprise Manager Properties window that pops up, click 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:
Tools-Options-connection-Sets the login timeout to a larger number
　　 Four, most machines use TCP/IP to succeed, sometimes you will find the use of Named pipes can?
This is because in the operating system after Windows 2000, MS configures TCP/IP as the default connection agreement for SQL Server to resolve SQL Server security issues, and you can see TCP/IP and name in Client Network Utility The order of the PIPE.
can also be in:
[Hkey_local_machine\software\microsoft\mssqlserver\client\supersocketnetlib]
"Protocolorder" =hex (7): 74,00,63,00,70,00,00,00,00,00
The default protocol can be seen here.
Q: How do I change named in a program PIPES,TCP/IP how to write its SQL statement?
A: You can modify the relative position of the registry mentioned above, as shown in the following example:
· Client side:
[Hkey_local_machine\software\microsoft\mssqlserver\client\supersocketnetlib]
"Protocolorder" =hex (7): 74,00,63,00,70,00,00,00,00,00
· Server End:
[Hkey_local_machine\software\microsoft\mssqlserver\mssqlserver\supersocketnetlib]
"Protocolorder" =hex (7): 74,00,63,00,70,00,00,00,00,00
Use Enterprise Manager on the server side and select Use Windows authentication to connect to SQL Server
Operation Steps:
In Enterprise Manager
Right click on your server instance (that's the one with the green icon)
Edit SQL Server Registration properties
Select Use Windows Authentication
Select "Use SQL Server Authentication"
Login name Input: SA, password enter the password for SA
Ok (OK, I'll get it in the end)
(method two) setting allow SQL Server to log on
In Enterprise Manager
Expand SQL Server Group and right click on the name of the SQL Server server
Select "Properties"
and select the Security tab
Under Authentication, select SQL Server and Windows.
OK, and restart the SQL Server service. (OK)
Description
Using Windows authentication to connect to SQL Server failed in (method i).
Then fix the problem by modifying the registry:
1. Click "Start"-"Run", input regedit, enter Registry Editor
2. Expand the registry key sequentially and browse to the following registry keys:
[HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer]
3. Locate the name "LoginMode" on the right side of the screen, 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.
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 the 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. On the Server Roles tab, select System Administrators
5. Click "OK" to 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. Means using Windows authentication mode
2. Indicates the use of mixed mode (Windows authentication and SQL Server authentication)