SQL 18456 “Login failed” or Error 18456 in SQL Server 2005 can happen as a result of who set of reasons.
When a server encounters an error which prevents a login from being successful, this error will be displayed:
Msg 18456, Level 14, State 1, Server <server>, Line 1
Login failed for user ‘<user>’
To find the reason for the failure, you can look in the server error log.
A sample entry:
XXDate-TimeXX Logon Error: 18456, Severity: 14, State: 11.
XXDate-TimeXX Logon Login failed for user ‘<user name>’.
‘State‘ will indicate the reason for the problem.
Please find below a list of common error state with explanation.
State 2, 5 – Invalid user id.
State 6 – Attempt to use a Windows login name with SQL Authentication.
State 7 – Login disabled.
State 8 – Password mismatch (One way to overcome this is by un-ticking the enforce password policy in the properties of the “sa” user).
State 9 – Invalid password.
State 11, 12 – Valid login but server access failure (i.e. sql account has no access to the server).
State 13 – SQL Server service paused.
State 16 – User does not have permission to log into the target database or the target database could not be determined.
State 18 – Password need to be change.
State 23 – Server was shutting down and some user was trying to log in.
State 27 – The server could not determine the initial database for the session.
If you have any questions about SQL 18456, do leave a comment.