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)
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]
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:
| State | Error Description |
| 1 | Error information is not available. This state usually means you do not have permission to receive the error details |
| 2 | Invalid user ID |
| 5 | Invalid user ID |
| 6 | Attempt to use a Windows login name with SQL Authentication |
| 7 | Login disabled |
| 8 | Password is incorrect |
| 9 | Password is not valid |
| 11-12 | Valid login but server access failure |
| 13 | SQL Server service paused |
| 16 | Authorization is correct, but access to the selected database is not allowed |
| 18 | Change password required |
| 27 | Initial database not found |
| 38 | Could 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.
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).
To apply changes, you need to restart you SQL Server services by right clicking and selecting Restart option from the context menu.
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
- How to Find Active Directory Nested Group Members? - August 1, 2020
- Sysprep a Windows 7 Machine – Start to Finish V2 - July 31, 2020
- How to Install VMWare Tools on Guest VMs? - July 31, 2020