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.

Filed under: Microsoft SQL Server, Problem Resolution, Security, Settings, SQLServerPedia Syndication Tagged: microsoft sql server, principals, security