My recent post on why SQL logins aren’t working has generated a couple of follow up posts. The first one was on how to create a connect item and then this one. It was pointed out to me that the errors in the log have quite a bit more information than the ones displayed while trying to log in. This important because, as was also pointed out, the public ones are just that. Public. They are kept reasonably vague to avoid giving someone trying to cause problems any more information than absolutely necessary. The log entries can have more information because anyone who has access to them already has some level of access to the instance/server. So in the interest of people doing web searches to find the various issues and just to prove the commenter correct let’s go through the possibilities.
The account is disabled or locked out
Error Displayed
Login failed for user ‘Kenneth_Test’. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)
Login failed for user ‘Kenneth_Test’ because the account is currently locked out.
The system administrator can unlock it.
Log
Login failed for user ‘Kenneth_Test’. Reason: The account is disabled. [CLIENT: ]
Sorry, I wasn’t able to cause a lockout so couldn’t get the log entry.
How do I fix it
If the account is locked out you may be asked to change the password. If so you can unlock it without changing the password by unchecking the Enforce password policy checkbox as well as unchecking the Login is locked out checkbox. You may still be prompted that you need to change the password but if you go back and look you find that it’s unlocked.
Bad password
Error Displayed
Login failed for user ‘Kenneth_Test’. (Microsoft SQL Server, Error: 18456)
Log
Login failed for user ‘Kenneth_Test’. Reason: Password did not match that for the login provided. [CLIENT: ]
How do I fix it
Simple enough. Change the password. However, there are a few warnings here. If you change the password and it’s being used by someone (for example this is an application id) you may be breaking the application. If this is production, that could be a no-no. Just in case I like to back up the password hash before changing it.
Windows authentication only
Error Displayed
Login failed for user ‘Kenneth_Test’. (Microsoft SQL Server, Error: 18456)
Log
Login failed for user ‘Kenneth_Test’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]
How do I fix it
Change the Server Authentication from Windows Authentication mode to SQL Server and Windows Authentication mode. This will require restarting the instance.
Correct Login
Obviously, there are no errors displayed.
Log
As long as login auditing properties are set to collect successful logins you will get the following.
Login succeeded for user ‘Kenneth_Test’. Connection made using SQL Server authentication. [CLIENT: ]
How do I fix it
Umm, nothing.
Summary
And there you go. The information in the log is far more detailed than the errors displayed. Which of course gives us the moral of our story.
If I’d just make checking the log my first step when problem solving my life would be a lot easier.
And there you go.
Filed under: Microsoft SQL Server, Problem Resolution, Security, Settings, SQLServerPedia Syndication Tagged: microsoft sql server, principals, security 
