THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Troubleshooting Repeated Login Failures on SQL Server

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

Published Friday, October 21, 2011 9:05 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Kevin, FYI, I wrote a blog post back in January covering all of the 18456 states I've come across. I think I've covered some states that Erik left out, and also added more context around them:

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

October 21, 2011 3:09 PM
 

KKline said:

Fantastic, Aaron!  Very good article and adds a great deal to the blog post.  =^)

October 21, 2011 3:16 PM
 

KKline said:

I got an email from Erik (cited in the article) with some more great info:

Regarding the error 18456: we found a few weeks ago with SolidQ folks that if you usr Extended Events, you'll see *two* errors thrown per failed attempt: once the one with the informative state id and then the one sent to the client. So if you' re going to use XEvents, watch out for this.

Take care,

Erik

October 21, 2011 3:21 PM
 

alamzyah said:

its realy help me alot, i had same problem too in Login Failures on SQL Server,

Vist my blog : http://alamzyah.wordpress.com

December 8, 2011 12:15 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement