Why won’t my SQL Logins work?

10

January 12, 2017 by Kenneth Fisher

I recently had an interesting time getting the SQL Auth logins on a new instance working. I was doing a side by side upgrade and the only server principals used by the application were SQL Server authenticated logins. I tried over and over again but kept getting the same error.

Login failed for user ‘Kenneth_Test’. (Microsoft SQL Server, Error: 18456)

So the first thing I checked was if the login was disabled or locked out.

sqlloginfails

To be fair it wasn’t going to be because I wasn’t getting the error that matched one of those problems. But it’s the first thing on my checklist.

Real Errors

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.

No go. Next thing to check is the password. The error I’m getting does indicate the incorrect password so it’s a distinct possibility. Now when I moved the server principal I made sure to copy the password hash from the old server so the password should be the same. However, I’ve made mistakes before, and odd things happen, so I decided to change the password just in case. Before I did I backed up the original password just in case.

Unfortunately, I continued to get the same login failed error.

Finally, after far too long banging my head against the desk, I realized this instance had been built with our standard Windows Authentication mode and not SQL Server and Windows Authentication mode which obviously this instance will require.

sqlloginfails2

What’s annoying here, and part of what took me so long is that logins failing because SQL Auth isn’t allowed, have exactly the same error as using a bad password.

What’s also interesting is that Windows Authentication mode doesn’t stop you from creating new server principals, enabling/disabling or changing their passwords. You just can’t actually use them.

10 thoughts on “Why won’t my SQL Logins work?

  1. dallasbikr says:

    I see a Connect item in your future….

  2. anil kumar says:

    Hello Everyone,

    I tried searching for connect item but couldn’t find any. Kindly intimate me if you get to know about same, in other case we can submit a connect item for this issue.

    Misleading error message shouldn’t find their place in a mature product like SQL Server.

    Thank you.

  3. James Lean says:

    Hi Kenneth,

    Not returning more detailed error information to the user for login failures is actually a deliberate choice – you don’t want to give potential attackers too much information about what the problem is, so they can’t focus their attention on that specifically. For example, if you returned a specific error saying the password was incorrect, this would at least confirm to them that the username WAS correct, which presents an enumeration risk (they can work out which users exist, and then concentrate on breaking the password).

    Whenever error 18456 is raised, it gets logged in the SQL error log, and here it DOES give you more detailed information, to aide administrators, e.g. you might see:

    “Login failed for user ‘sa’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only”.

    Regards,
    James

  4. […] recently wrote a blog about how to tell why your SQL login isn’t working. There were a lot of good comments and several of them suggested that I create a connect entry to […]

  5. […] 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 […]

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,614 other followers

Follow me on Twitter

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