Why won’t my SQL Logins work?
11January 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.
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.
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.
SQL Studies 
I see a Connect item in your future….
Probably not a bad idea.
+1 for Connect item. Also, Kenenth, could you describe simple steps how to create good connect item and requirements for that?
That’s not a bad idea. I know I have a problem with it myself.
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.
I’ll be creating a connect item this weekend and writing a blog on how to do so at the same time. I’ll put the link here soon.
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
Excellent point! Obviously I need to do a followup showing the log errors.
[…] 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 […]
[…] 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 […]
For my SQL Server 2016 installation, I had to reboot my system before “SQL Server and Windows Authentication mode” would actually take effect.
Thanks for writing this article. As a total noob to SQL Server this really helped.