Solution for Logon Failure of user SA on SQL Server 2000

Source: Internet
Author: User
Tags builtin mssqlserver
SQL Server 2000 user SA logon Failure Solution

I. log on to the control panel-> services-> ms SQL Server-> Local SYSTEM account-> restart ms SQL Server
Log on to the query analyzer using Windows verification -- execute sp_password null, sa new password, 'sa'

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 connect to the SQL Server login account (such as SA). The solution is as follows:
1. 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"
-- Enter SA in the login name and SA in the password
-- OK
2. Set to allow SQL Server login
Procedure:
In Enterprise Manager
-- Expand "SQL Server group", right-click the name of the SQL Server
-- Select "attribute"
-- Select the "Security" tab.
-- Under "authentication", select "SQL Server and Windows ".
-- OK, and 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,
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. indicates that the hybrid mode is used (Windows Authentication and SQL Server Authentication ).

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.