Lỗi additional information sql server login failed for user microsoft

Sửa lỗi SQL Server login failed for user 'administrator' 18456

by My Love

27/04/2021, 8:24 AM | 27/04/2021, 8:24 AM | 5.9K | 030124

- Mở SQL Server Configuration Manager, đường dẫn tham khảo :

SQL Server 2016    C:\Windows\SysWOW64\SQLServerManager13.msc SQL Server 2014    C:\Windows\SysWOW64\SQLServerManager12.msc SQL Server 2012    C:\Windows\SysWOW64\SQLServerManager11.msc SQL Server 2008    C:\Windows\SysWOW64\SQLServerManager10.msc

- Chọn mục SQL Server Services \=> Phải chuột vào SQL Server ( MSSQLSERVER) \=> Chọn Properties \=>Chọn tab Startup Parameters - Trong ô Specify a starup parameter thêm từ khóa -m sau đó nhấn Add \=> Apply \=> OK - Khởi động lại MSSQLSERVER - Mở SQL Server lại bằng quyền administrator, lúc này bạn có thể kết nối được vào SQL Server bằng quyền administrator rồi - Thêm tên đăng nhập trong phần Security => Logins => New Login của SQL - Tắt SQL Server đi - Vào lại SQL Server Configuration Manager xóa từ khóa -m lúc trước thêm vào đi. - Quay lại mở Sql Server lên là đăng nhập được

* Chi tiết bạn xem video dưới đây:

Encountering the “Login failed for user ‘sa’. (Microsoft SQL Server, Error 18456)” error can be a frustrating experience for database administrators and developers. This error typically occurs when there’s a problem with the authentication process. In this blog, we will explore some common reasons for this error and solutions to fix it. Let’s dive in!

Solution 1: Verify Username and Password

Double-check the username and password. It’s possible that either the username is misspelled or the password has been changed without updating your connection string.

Solution 2: Check SQL Server authentication mode

Ensure that your SQL Server is configured to allow SQL Server authentication. Without that, it won’t allow the login with login name and password. To change the authentication mode, follow these steps:

1. Connect to SQL Server using a Windows Authentication account with administrative privileges.

2. Open SQL Server Management Studio (SSMS) and right-click on the server instance and select “Properties”.

3. Navigate to the “Security” tab. Choose “SQL Server and Windows Authentication mode”. Click on “OK” to save the changes.

4. Right-click on the server instance and select “Restart” to restart the SQL Server service.

Solution 3: Reset ‘sa’ Password

If you suspect that the ‘sa’ account’s password is incorrect, you can reset it. If you have administrative privileges on the SQL Server instance, follow these steps:

1. Log in to SQL Server using Windows Authentication.

2. Expand the “Server instance” and then “Security”. Inside “Security” expand “Logins”. Right click on ‘sa’ and select “Properties”.

3. Reset the password.

Solution 4: Verify SQL Server Services

Sometimes, the error 18456 can occur if the SQL Server services are not running correctly. To check and start the services, do the following:

1. Press “Win + R,” type “services.msc,” and press Enter.

2. Look for “SQL Server” in the list of services.

3. Ensure that the status of service is “Running.” If not, right-click on the service and select “Start.”

Solution 5: Unlock the ‘sa’ login

If multiple failed login attempts occur, the ‘sa’ login account might get locked out. To unlock the ‘sa’ account, follow these steps:

1. Connect to SQL Server using a Windows Authentication account with administrative privileges.

2. Expand the “Server instance” and then “Security”. Inside “Security” expand “Logins”. Right click on ‘sa’ and select “Properties”.

3. In the “Status” tab, check the “Login” option. Check the “Enabled” option, if it’s not checked.

4. Click “OK” to apply the changes.

Conclusion:

The “Login failed for user ‘sa’. (Microsoft SQL Server, Error 18456)” error can stem from various sources, ranging from incorrect credentials to database configuration problems. By systematically following the solutions outlined in this blog post, you can troubleshoot and resolve the issue, ensuring smooth access to your SQL Server instance. Remember that each environment may have unique factors contributing to the error, so patient investigation is key to finding an effective solution.