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.
A
False Trail
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 for
authentication problems here. Ok,
that helps. But it’s not my solution.
MVPs to the Rescue
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.
Microsoft Improves the Documentation
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,
-Kev
-Follow me
on Twitter
-More content on my blog