Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What are some tips for troubleshooting SQL Server login errors? Questions like:

  1. How Do I see the full Error Message? My client just has a generic error message that says logon failed with no real information/state information.
  2. What if there is no error in the error log, but I still can't connect?
  3. How Do I read the logon failure (Error 18456) error messages?
  4. How Do I troubleshoot the various conditions found in the error messages?
  5. What is Kerberos, why do I care and how do I fix errors related to it?
share|improve this question

1 Answer 1

How Do I See the Full Error Message?

The SQL Server error log on the instance you are trying to connect to will have any errors that SQL Server generated about the logon failure. If SQL rejected the connection/failed the logon for some reason it will generate an 18456 error typically. On the SQL side, this error will have an error state that gives clues to the cause of the error. On the client side, however, you may or may not see the useful details - it likely that you'll just see an error state of 1.

To see the full error message it is best to look at the SQL Server that you are trying to connect to and look in the SQL Server Error Log. You should see the error for the connection attempt if the connection is making it to SQL Server. From there the information is more useful than some other error messages if you know where to look.

What If There is no Error in the SQL Server Error Log?

This could be caused by several factors but it is quite likely that you are not dealing with a logon failure. Logon failures do a good job of presenting themselves in the error log. In this case it is quite possible that you are dealing with a failure to connect to SQL Server. You can troubleshoot this problem first and at least verify that you are connecting to SQL Server in the first place and that there are no issues here. Rather than get into all the potential steps for troubleshooting that here in this answer, this Technet Twiki article is an excellent resource to at least get you started.

How do I Read the Error Messages in the Log?

There are several resources for the meaning of the various error states and error messages. The below is pasted from this msdn link to get you started with some of the more common states of the 18456 error. These states may change in future versions or in the version you are on so it is a good idea to pay attention to both the message and the state and to dig deeper to understand the issue.

State ---- Meaning

2  ---- User ID is not valid.
5  ---- User ID is not valid.
6  ---- An attempt was made to use a Windows login name with SQL Server Authentication.
7  ---- Login is disabled, and the password is incorrect.
8  ---- The password is incorrect.
9  ---- Password is not valid.
11 ---- Login is valid, but server access failed.
12 ---- Login is valid login, but server access failed.
18 ---- Password must be changed.

There are many other states as well and in edits I'll add more or in other answers you'll see more. This link has a lot of other states, most of which are valid for most versions of SQL Server above 2000. The important take away here is to look in your error log on the server side and read the message - with logon failures the messages tend to be useful a lot more often than not.

And again State 1 is a generic message on the client.

Other Errors There are other errors for SQL Server login or connection failures. In future edits or answers from other people more information can be added as well.

What Do I Do With That Data?

It depends..

The Obvious States

States 2 & 5 signify an invalid User ID. Check the user you are working with. Does your login exist? Are you specifying the database and does the login exist as a user - has it been mapped as a user in that database.

State 6 - Oops! You are trying to pass an Active Directory login name in a manner that uses SQL authentication.. Basically trying to pass Login Name of 'FunDomain\Mwalsh' but you are connecting with SQL authentication, not trusted. This is sort of like a 2 or a 5, just a bit more descriptive.

State 7 - Login is disabled.. See where it is disabled, why it is disabled and rectify the situation.

States 8 & 9 - Bad password. Is it mistyped? Has the password changed?

State 18 - I've never encountered this one in the wild. But it is telling us that our password is set to be changed. I suppose you could see this if you are connecting from an application that uses SQL authentication (like Great Plains...) and have setup a new account with a password that needs to be changed on first use - but your application doesn't understand that or have a functionality to change the password. If you just use SSMS, you just get a dialog to change the password and all is well.

Less Obvious States

11 & 12 sort of mean the same thing- These are ones you have to pay attention to the message closely with.

Is the word "Token" in the error message? Like "Login failed for user 'myDomain\UserName'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors."

This can be a case of your SQL Server Service running as local system instead of a domain account with access to domain info potentially.

It can also be a case of needing to delegate authority through kerberos - for instance when accessing a Linked Server through Windows Authentication and passing the credentials - that double hop requires Kerberos, not NTLM. So you'll need to work through Kerberos.

Even this message can also mean other things - I've seen this recently after a migration to a server with an already existing logon - it was a very old logon at my client's environment. Dropping and recreating the logon resolved the issue. Why? It could be a few things but this post from Simon Sabin a couple years ago is an excellent resource here.

UPDATE After answering this I wanted to look an error code about default databases and I found a great blog post from Aaron Bertrand who answers a lot of questions here :-) Go check out his resource for more error messages including many that aren't in SQL Server 2012 or 2008 but still in the versions in use today. Great stuff there :-)

share|improve this answer
    
See a lot of SQL Server login/logon failure message come through here. This is my attempt to start a dialog with good answers to have a single point of reference for folks. Feel free to add answers. Or feel free to vote my Question or Answer down and vote to close if it's a bad idea ;-) –  Mike Walsh Nov 13 '13 at 20:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.