Sql server error 18456 – Troubleshoot Login Failed

Sql server error 18456 is common issue appear during login process on Microsoft SQL Server. This error can happen when you try to login with local administrator, as well as under the domain administrator and under the sa. Microsoft SQL Server login failed error can be encountered due to varied reasons. Most of the time, an error code comes up with a description that gives a hint about what has gone wrong. But I some cases the error come without any description. In this article, we’ll take a look at the typical reasons of the error 18456 appear on SQL Server during login process and show different ways to solve this error.

The view of error:

“Login failed for user ‘<user_name>’. (Microsoft SQL Server, Error: 18456)”.

How To FIX SQL Server Error 18456

Troubleshoot with Short Solutions

Here, you have some possible reasons:

1. The login does not exist or was not typed correctly
2. Make sure that the username or password are correct
3. The password is incorrect
4. The user forgot the password or login
5. The Windows Authentication is not in Mixed mode
6. A virus resets all the passwords
7. A malicious hacker reset the password
8. The logins were damaged or the master database is damaged
9. The database was migrated, but the logins were not migrated
10. The administrator modified the passwords by mistake
11. Restart the SLQ Server service

Troubleshoot with State of the Microsoft SQL error 18456

Most of the time the SQL error 18456 come with the severity and state number. A state number might not mean much, yet it can offer more details as to what is wrong and where to look next.

To get a more detailed info about Microsoft SQL Server Error 18456 reason, you need to open the SQL Server error log file – ERROR.LOG. This is plain text file located under folder MSSQL\Log. Below are some states of the error 18456 sql server. The descriptions and potential solutions offer a quick explanation and potential troubleshooting guide.

Common Solution for Error 18456

If the issue cannot be resolved from with short solutions above, read below for additional information:

Read also other SQL Server Helping Posts:

1. SQL Server Error 233
2. Fix SQL server error 26 and error 40
3. Restore Master Database

Checking the Server Authentication Mode

In this case you are trying to login on SQL Server using sql user. Once we login to SSMS using Windows Authentication, we need to check the security settings to confirm whether MSSQL is set up to allow both Windows and SQL Authentication.

Check and Change SQL Server Authentication Mode from GUI:

1. In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Properties.
2. Next, click the Security page.
3. If you find Windows Authentication is the only mode configured, this is the likely cause of sql server error 18456, Login failed for user ‘’.
4. Setting the Server authentication mode to allow SQL Server and Windows Authentication, you will be able to login to MS-SQL with a SQL user and password or a Windows user and password. After making this change, you will need to restart the SQL Server service.

Server Authentication Mode

Change SQL Server Authentication Mode from regedit

You can use the registry to modify the authentication mode. Use the regedit to change the registry:

Image (regedit)

1. machine\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQLServer
2. Change the login mode value.
   1. 2 is mixed mode.
   2. 1 is Windows Authentication.

Change SQL Server Authentication Mode from regedit

Checking pass expired or login disabled

Check out that the password is not expired.

1. Open SSMS, Instance – Security – Logins and find the user that have issue
2. On general tab check if the Enforce password expiration and enforce password policy are checked
3. Un-check them

Check out that the login is enabled.

1. Open SSMS, Instance – Security – Logins and find the user that have issue
2. On status tab and check if is selected the “Enabled” option

Reset the Password of the user

If you forget your password, you can ask your DBA to reset your account. The easiest way to reset the password is by using SQL Server Management Studio (SSMS).

1. Go to security and Logins:
2. Select the login and you can change the password:

Checking pass expired or login disabled

3. If you do not like to use SSMS, you can use T-SQL to create users and change the password:

USE [master] GOALTER LOGIN [Test] WITH ‘newpasswordtest’GOChange Windows Authentication

So we hope that you fixed the issue with the sql server error 18456.