THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Understanding 'login failed' (Error 18456) errors in SQL Server 2005

One of my favorite errors that I get paged for is the login failed (error 18456). I have a few users who are guaranteed to ping me at least once or twice a week for a failed login. Generally I can tell that the user is simply trying to login to the wrong server, since we have a standardized naming format of CCLLTT## for our servers that results in company/location/type/number for identification, developers can easily transpose one letter and attempt to login to a production server intending to login to a development one by a similar name.

Doing a search for the error will bring back the SQL Process Blog which is where the below information comes from. The table on the blog entry however is not updated, so I am going to summarize and update here.

In SQL 2000, Error 18456 always returns State: 1 which is not very helpful. However, SQL 2005, and 2008 make use of the error state to identify what the specific login problem was.

But what about when you get a random failed login that you don't know about or expect. It is up to the DBA to decode the state of the error message to identify exactly what happened. Error's for a failed login are similar to:

2008-02-27 08:32:16.22 Logon Error: 18456, Severity: 14, State: 2.
2008-02-27 08:32:16.22 Logon Login failed for user ''. [CLIENT: < ip >]


State Meaning
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
16 The target database could not be determined
18 Change password
23 The server is in the process of shutting down. No new connections are allowed.
27 The server could not determine the initial database for the session.
Published Tuesday, March 04, 2008 10:24 AM by Jonathan Kehayias
Filed under:

Comments

No Comments
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement