Critical Status - Database-Oriented Issues Plus

It seems that everything has a critical status, especially when it comes to databases and software development! Here's a place for listing issues and ideas relating to database and development issues. I'll list problems and ideas mainly related to SQL Server 2005. Post your thoughts, advice or complaints! Later this year will be the 25th anniversary of being in the trenches of the IT industry. I specialize in database applications development. I'd like to share my experiences with everyone and learn of those of others. You never stop learning.

Tuesday, September 30, 2008

Here's good link for grasping the resl source of the "login failed" error message. I've supplied a link to it.

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx


Understanding "login failed" (Error 18456) error messages in SQL Server 2005

In continuing with the theme of understanding error messages I'll discuss the "login failed" messages that are surfaced by the client and written to the server's error log (if the auditlevel is set to log failures on login which is the default) in the event of an error during the login process.

If the server encounters an error that prevents a login from succeeding, the client will display the following error mesage.

Msg 18456, Level 14, State 1, Server , Line 1
Login failed for user ''

Note that the message is kept fairly nondescript to prevent information disclosure to unauthenticated clients. In particular, the 'State' will always be shown to be '1' regardless of the nature of the problem. To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written. An example of an entry is:

2006-02-27 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8.

2006-02-27 00:02:00.34 Logon Login failed for user ''. [CLIENT: ]




nThe key to the message is the 'State' which the server will accurately set to reflect the source of the problem. In the example above, State 8 indicates that the authentication failed because the user provided an incorrect password. The common error states and their descriptions are provided in the following table:

ERROR STATE
ERROR DESCRIPTION

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

18
Change password required



Other error states indicate an internal error and may require assistance from CSS.

Il-Sung Lee
Program Manager, SQL Server Protocols