SQL Server Login Errors

Hello Dear Reader.  Quick blog today.  If you are working in the SQL Server world eventually you will try to connect to a SQL Server and get an error.  A beautiful wonderful descriptive error that reads, pauses… looks around… stands straight…. In my best impersonation of a classic English poet proceeds to read, “Error: 18456, Severity:  14, State: X”.

Ahhh sweet music.  Okay Dear Reader I confess, it’s definitely not Lord Tennyson.  But the important part is the State.  In SQL Server 2005 they did a really great job of giving us more descriptive error descriptions when it came to the State.

SQL STATE OF MIND

When I first discovered this I made a flash card out of it.  I used to quiz myself on it.  Running production, QA, Test, Staging, and Dev DBA support this came in handy.  Whatever your environment when a user calls and says “I can’t log in”, you can use this as a guide.

ERROR STATE	ERROR DESCRIPTION
1	The Error information is not available.  This normally means that the user doesn’t have permissions to view the actual error state.  Check the SQL Server Error Log on the Instance they were trying to connect to.  If you are logging unsuccessful login attempts the real state will be in the error log.
2 and 5	The User ID is not valid.  Check for misspelled user name, or that the login has been created on the server.
6	Attempt to use a Windows login name with SQL Authentication
7	Login disabled and the password does not match what is on record.  A SQL DBA or someone with Administrative Permissions will need to Enable the Login.  If you get this for SA, then you may not have SQL Server Authentication Enabled.  Even after you enable it the SA may be disabled.
8	You typed in the password wrong, make sure you know what it is so you don’t lock it out.  If you do you’ll get error State 7
9	Invalid password
10	This was specific to SQL 2005 -When you try to use a SQL Server authenticated login to log onto the instance.  -The SQL Server service is configured to use a domain account -The SQL Authenticated logins that receive the "Logon Error: 18456" error message are configured to use Windows domain password policy enforcement More details here: http://support.microsoft.com/kb/925744
11	Login is valid, but server access failed.  From BOL: “One possible cause of this error is when the Windows user has access to SQL Server as a member of the local administrators group, but Windows is not providing administrator credentials. To connect, start the connecting program using the Run as administrator option, and then add the Windows user to SQL Server as a specific login.”
12	Login is valid, but server access failed.  When I have seen this in the past it is because a login is created with specific and restricted permissions.  Instead of setting the default database to the DB in question it is left on a default database that the user cannot access.  Double Check the Default DB for the user.
13	The SQL Server service paused, at this point you need to get the service running.  Any failed login will resolve itself at this point.
16	Occurs when logins do not have access to the target database or the database no longer exists or is offline.
18	Password change is required, and you are probably accessing in such a way that a change password prompt cannot appear
23	SQL Server is shutting down and new incomming connections are attempting to connect.  More Here: http://support.microsoft.com/kb/937745
38	Introduced in SQL 2008 for Login error where the database doesn't exist, Login doesn't have  access to the database
40	This like 16 and 38 occur when the login doesn't have access to the default database or it is offline.  HOWEVER what sets this apart is it accompanies an error 4064. (http://support.microsoft.com/kb/307864)

*NOTE

If you have errors that I haven’t listed let me know and I’ll add them to the list.  Leave your name, Twitter handle, blog address whatever and I’ll make sure to give you credit!

As always Dear Reader, Thanks for stopping by!

Thanks,

Brad