Overview
If the SQL Server gives an error that prevents login from succeeding, the user will get the following error message:
Msg 18456, Level 14, State 1, Server , Line 1
Login failed for user ‘’
SQL Server Error 18456 message depicts the Password Authentication Error while connecting to SQL Server. One can get different State errors with same error message. The above message is not in brief form. The reason this message is in encrypted form is to prevent the information declaration to an unauthorised clients. The State of this error will always be shown as 1 irrespective of the nature of the problem. To determine the error, you can check the server error’s log.
SQL Server Error 18456 - Different Causes
There are several error messages of 'Login failed for user 'sa'. (Microsoft sql server error 18456)' with different states. Various causes of SQL Server error 18456 are as follows:
- Login Failed Error
- Login doesn’t exist or match
- Incorrect Password
- Invalid Username
- Unable to validate Windows Permission
- SQL Service is not working properly
- Database is inaccessible
- Connections being attempted when the service is shut down
- Orphaned login
Login failed for user 'sa'. (Microsoft SQL Server Error 18456) - Indication by States
Error: 18470, Severity: 14, State: 1
SQL Server error 18456, State 1 occurs when login is disabled and user is not able to login to the SQL Server. Hence, will face State 1 error.
Error: 18456, Severity: 14, State: 2
State 2 in SQL Server Error 18456 login failed for user occurs when the user is trying to logged in by wrong login, or you have created a server-level login in which you logged in by wrong username.
Error: 18456, Severity: 14, State: 5
Like in State 2, this state also indicates that the login does not exist. If you are trying to connect to the database with wrong username or misspell login name, you will get this error.
Error: 18456, Severity: 14, State: 6
SQL error message 18456 indicates that you entered Windows style login in the form of Domain or Username. This means you tried to specify SQL authentication. This state occurs when you specify any login as SQL server account, but try to login as Windows account.
Error: 18456, Severity: 14, State: 7
The error occurred due to disabled login and correct password. In this, password validation is done first, while login is disabled, you will get error of 18470. The 18456 error occurs when you disabled login while password is incorrect.
Error: 18456, Severity: 14, State: 8
Login failed for user '
Error: 18456, Severity: 14, State: 9
SQL Server error 18456 message indicates Invalid Password. The state indicates that password is violating its policy check and got rejected as invalid one.
Error: 18456, Severity: 14, State: 11, Error: 18456, Severity: 14, State: 12
Microsoft SQL error 18456 indicates that the login is valid but server access failed. States 11 & 12 indicates that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions.
Error: 18456, Severity: 14, State: 13
This error occurs when you try to access the SQL Server , but its Services are paused.
Error: 18456, Severity: 14, State: 18
This state indicate the need to change password. It occurs when login needs to have its password changed as per its password policy. The error will generate the error number 18488 instead of 18456 because for SQL login , the changed password dialog is just delays logging in, not a login failure.
SQL Server Error 18456 - Know How to Resolve it ?
Microsoft SQL Error 18456 is the common error that most DBA or developer have come across, while working on SQL server. This error usually indicate that ACCESS denied to the particular login for a reason. To check out the exact reason of the error, one should check error logs where the error number along with State number is stated. You can recover SQL Server Error 18456 SQL Server Authentication error accordingly to the State number mentioned in the error. If you are not able to recover the Password error (as it is the most common error any DBA have faced) due to some mismatch or any failure,you have an alternate option of using the third party tool . The Password Recovery Tool reset SQL password by resetting it to new one and gives you an option to set new one.
Conclusion
In this blog I have explained about SQL Server error 18456, what are its causes and its states indicating the type of error. You can reset your password by checking out the reason in error log or can use the third party SQL Password Recovery Tool.
EmoticonEmoticon