My SQL logins don’t work. Again.

Leave a comment

January 25, 2017 by Kenneth Fisher

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
sqlloginfails

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.

sqlloginagain3


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.
sqlloginfails2


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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,653 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: