Workaround for SQL Server SA user logon failure

Source: Internet
Author: User
Tags builtin mssqlserver

I. Control Panel-"service-" MS SQL server-Login--"Local System account--" Restart MS SQL SERVER
Using Windows Authentication Login Query Analyzer--"Execute sp_password null,sa new password, ' sa '

Two. " Unable to connect to server, user XXX login Failed "
This error occurs because SQL Server uses Windows-only authentication,
Therefore, users cannot connect using SQL Server's login account (such as SA). The workaround is as follows:
1. Use Enterprise Manager on the server side and select "Use Windows Authentication" to connect on SQL Server
Operation Steps:
In Enterprise Manager
--Right-click your server instance (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
-Determine
2. Set allow SQL Server logon
Operation Steps:
In Enterprise Manager
--Expand SQL Server Group, right-click the name of your SQL Server server
--Select "Properties"
--then select the "Security" tab
--under Authentication, select SQL Server and Windows.
--OK, and restart the SQL Server service.
In the above workaround, if you use use Windows authentication to connect to SQL Server in step 1th, the
Then fix the problem by modifying the registry:
1. Click "Start"-"Run", enter Regedit, enter the Registry Editor
2. Expand the registry keys, and then browse to the following registry key:
[HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer]
3. Locate the name "LoginMode" on the right of the screen and double-click Edit DWORD 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, the user can successfully use the 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 is deleted.
To recover these two accounts, you can use the following methods:
1. Open Enterprise Manager, expand Server Groups, and then expand Servers
2. Expand Security, right-click Login, and then click New Login
3. In the Name box, enter 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.
Description
The following registry key:
Hkey_local_machine\software\microsoft\mssqlserver\mssqlserver\loginmode
The value determines what authentication mode SQL Server will take.
1. Indicates the use of Windows authentication mode
2. Represents the use of mixed mode (Windows authentication and SQL Server authentication).

Workaround for SQL Server SA user logon failure

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.