I’d recently experienced a situation where I was getting repeated login failures to a SQL Server where I knew that I had the correct user name and password. Each time, I’d get error 18456 in response.
“Login failed for user ‘<user_name>’. (Microsoft SQL Server, Error: 18456)”.
The challenge when troubleshooting this error message is that you may have a problem with SQL Server or you may have a problem with Active Directory or Kerberos, if you’re using one of those authentication technologies.
When times are good, and you’re able to make a connection, you can always query sys.dm_exec_connections, to see how you’re connecting, for example, using NTLM rather than Kerberos. But that doesn’t help us when we can’t connect to the server at all.
In other situations, your problem might be caused by duplicate SPMs in Active Directory. MVP Russell Fields documented a nice solution for ridding Active Directory of duplicate SPNs here. Microsoft Support also mentions some troubleshooting steps forauthentication problems here. Ok, that helps. But it’s not my solution.
Fortunately, my MVP buddies Edwin Sarmiento (blog | twitter) of Canada and Bitemo Erik Gergely (blog) of Hungaria had already discussed and solved the problem for me!
Something Erik pointed out, but hadn’t occurred to me at first, is that if you’re getting this SQL Server error message then you’ve actually reached the server and probably aren’t having a full disconnect error. The second thing that Erik pointed out is the importance of the state element of this error message. A lot of the time, you can simply ignore the
state element of an error message. But not this time. As it turns out, state is the key to solving the problem. For example, a state of 18 indicates that the password must be changed.
Microsoft provides a pretty good description of the states of error 18456 here, but it leaves out a few things. (You’ll get more useful info if you read all of the comments too). But
again, Erik comes to the rescue by providing a complete and concise list of error 18456 states here.
Even better for all troubleshooting situation involving state information, Microsoft has now added state descriptions for errors in Books Online, including error 18456. When you look in your SQL Server error log, you see the state of the error and be able to make an accurate deduction about the nature of the error!
Hope this helps,
-Follow me on Twitter