Overview

If the Sql Server gives an Error that prevents login from succeeding, the user will get the following error message:

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.

Different Causes of SQL Server Error 18456

There are several error messages of SQL error 18456 with different states. Various causes of error 18456 are as follows

• Login Filled 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

Indication of SQL Error 18456 by States

• Error: 18470, Severity: 14, State: 1

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 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

This error message 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 occured 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

The error indicates Password mismatch. The error is caused due to wrongly typed password(case insensitivity case) or you have specified the wrong database.

• Error: 18456, Severity: 14, State: 9

The error 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

This error 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.

How to Resolve it ?

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 your 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 helps you to recover your password by resetting it to new one.

Conclusion

In this blog I have explained about the error 18456. The article covers the causes of SQL Server error 18456, its states indicating the type of error. You can recover it by checking out the reason in error log or can use the third party tool for Password Recovery.