SQL Server can not connect exactly how to solve it? Prompt "can not connect to the server, user xxx login failed" For what reason?
The reason for this error is that SQL Server uses the "Windows only" authentication method, so users cannot connect using the SQL Server login account (such as sa ). The solution is as follows:
![4829096_1[1].jpg](http://www.lkisalqf.cn/upload/default/20181018/5cf2dde3ee49d37c4a7d86222c17fa78.jpg "4829096_1[1].jpg")
1. Use Enterprise Manager on the server side and select "Use Windows Authentication" to connect to SQL Server.
2. Expand "SQL Server Group", right 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.
![4829096_2[1].jpg](http://www.lkisalqf.cn/upload/default/20181018/beab5e22f94c92cee1554a523fb5fffc.jpg "4829096_2[1].jpg")
In the above workaround, if you use "Use Windows Authentication" to connect to SQL Server in step 1, you can solve this problem by modifying the registry:
1. Click "Start" - "Run", type regedit, press Enter to enter the Registry Editor.
2. Expand the registry key and browse to the following registry key:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer]
3. Find the name "LoginMode" on the right 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 the 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 the Enterprise Manager, but still can't use Windows authentication mode to connect to SQL Server.
This is because there are two default login accounts in SQL Server, BUILTIN\Administrators<machine name>\Administrator.
![4829096_3[1].jpg](http://www.lkisalqf.cn/upload/default/20181018/7aff8918a2506db81fff7c3e03d3895d.jpg "4829096_3[1].jpg")
To recover 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 "Login" and click "New Login".
3. In the Name box, type BUILTIN\Administrators.
4. In the Server Roles tab, select System Administrators.
5. Click "OK" to exit.
6. Use the same method to add <machine name>\Administrator login.
![4829096_4[1].jpg](http://www.lkisalqf.cn/upload/default/20181018/326172e301c9f8b5e064dd37f70935d2.jpg "4829096_4[1].jpg")
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. Indicates that the "Windows Authentication" mode is used.
2. Indicates that mixed mode (Windows authentication and SQL Server authentication) is used.
