Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLBalls

Bradley Ball is a MCITP SQL 2005 & MCTS 2008 DBA with over 10 years of IT experience. Bradley spent 8 years working as a Defense contractor for clients such as the U.S. Army and The Executive Office of the President of the United States. He is currently a Sr. Consultant for Pragmatic Works. He has presented at SQL Saturdays 62, 74, 79, 85, 86, 131, for the MAGICPASS & OPASS SSUG’s, SQL Rally 2011 & 2012, SQL Dev Connections 2012, the PASS Summit in 2011, and is scheduled for the PASS Summit 2012 and SQL Live 360 later this year. He recently finished Chapter 14 of Expert SQL Server Practices on Page & Row Compression and can be found blogging on http://www.sqlballs.com.

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

Comments

Leave a comment on the original post [www.sqlballs.com, opens in a new window]

Loading comments...