microsoft sql server error 18456 cover

How to Fix Microsoft SQL Server Error 18456?


In this article we’ll take a look at the typical reasons for the error 18456 appearing when you try to authenticate on Microsoft SQL Server and show you how to fix it. Microsoft SQL Server Error 18456 can appear when you try to login under the local administrator, as well as under the domain administrator and under the sa user.

The most common reasons for login failed can be quite different cases:

  • Incorrect username or password;
  • Disabled SQL Authentication node;
  • Expired password;
  • Deleted user account.

For example, if you run Microsoft SQL Server Management Studio and try to login to the SQL server under the sa account, the following error appears:

TITLE: Connect to Server
------------------------------
Cannot connect to server1\sql.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

microsoft sql server error 18456

The exact reasons for unsuccessful authentication can be determined with the help of error State. 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. For example, you can see following lines in the error.log file:

2018-04-19 16:10:08.07 Logon       Error: 18456, Severity: 14, State: 8.
2018-04-19 16:10:08.07 Logon       Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 10.27.65.29]

microsoft sql server error 18456 fix

As you can see, the user tried to login with a wrong password (State 8).

In the following table there are the possible states for error 18456 and short description for each one:

 StateError Description
1Error information is not available. This state usually means you do not have permission to receive the error details
2Invalid user ID
5Invalid user ID
6Attempt to use a Windows login name with SQL Authentication
7Login disabled
8Password is incorrect
9Password is not valid
11-12Valid login but server access failure
13SQL Server service paused
16Authorization is correct, but access to the selected database is not allowed
18Change password required
27Initial database not found
38Could not find database requested by user

In most cases, users encounter Microsoft SQL Server Error 18456 with state 6 when they attempt to authenticate using SQL Server Authentication. In the SQL connection windows switch to the Windows Authentication as the authentication type and try to connect to the SQL database under an account with local administrator rights.

sql server error 18456

If Windows Authentication works properly, you need to open the Properties of your SQL Instance and go to the Security tab. Change the server authentication mode to SQL Server and Windows Authentication mode (this is the mixed authentication mode).

microsoft sql server error 18456 authentication

To apply changes, you need to restart you SQL Server services by right clicking and selecting Restart option from the context menu.

sql error 18456

Try to login to the server under the SQL account (sa or custom user) in the SQL Server Authentication mode.

If the sa account is disabled or you don’t know the password, run the following queries in the Management Studio console to enable sa and reset it password.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '<some_super_strong_password>';
GO

Add Your Comment