Thursday, January 7, 2010

SQL Server Login Error Review & Fix for "Error: 18456, Severity: 14, State: 11."

If you've got a SQL Server machine on a domain chances are you've seen error 18456 enough times to have seen the following:
  • Severity is always 14
  • State 8 is a bad password (password submitted doesn't match password stored in master)
  • State 9 is an invalid password (doesn't meet requirements)
  • States 2 & 5 are invalid usernames
  • State 40 means the logins default database isn't accessible to that login
  • State 11 means the login is valid, but something else went wrong
Now, state 11 is fun. You'll get this error and then you'll probably get a call or email from someone saying they should have access but it isn't working. Often in the logs you'll see something like this:


Login failed for user 'DOMAIN\SAMAccountName'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

This is a pretty good indicator that the users login credentials are out of date which is usually a result of a laptop user trying to access the resource with cached credentials after changing their password elsewhere.
No matter what there are two simple fixes for this that have always worked for me.
  1. Have the user lock their computer and login again.
  2. Have the user log off and logon again.
That's it! Those two steps will ensure that NTLM and/or Kerberos tickets/tokens are refreshed by talking to a current domain controller and the problem should go away.

NOTE: If you're seeing errors about SSPI context there is a possibility that your SQL Server service account is invalid (password changed, locked out, disabled, etc.), or your domain (or specific DC) is having issues.

Cheers!

No comments: