Sessions: 199 The postings on this site are my own and do not represent my Employer's positions, advice or strategies.


  Saturday, July 13, 2024
Wider View Login

IBM - AS400
Performance Counters
Service Broker
SQL Server
     Temporary Database
     SQL Mail
     DTS - Data Transformation Services
     Jobs SQL Agent
     User Management
     System Databases
     Temp Tables
     Find the physical se...
     Enable xp_cmdshell
     Intermittent Connect...
     Connection String Pr...
     Login Error ( State)...
     Turn Default Tracing...
     Re-index fails when ...
     Tracing of Errors
     Extra carriage Retur...
     Query to find Object...
     Named Instance Port ...
     Adding Domain Login ...
     Rename Logical File ...
     Error 3624 Delete st...
     SQL DMO Error 21776
     SQL Server Service m...
     Trace Flags for Dead...
     Change Server Name
     Alternate to Count(*...
Windows OS

Will be added as a sub-category of, SQL Server
Login Error ( State) Codes

Understanding the “login failed (Error 18456) error” message in SQL Server 2005.

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

Msg 18456, Level 14, State 1, Server <server name>, Line 1
Login failed for user '<user name>'

Of note is the State; it will always be shown to be ‘1’ back to the client, regardless of the nature of the problem, to prevent information disclosure to unauthenticated clients. 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:

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

2006-02-27 00:02:00.34 Logon Login failed for user '<user name>'. [CLIENT: <ip address>]

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

Common Error States and Descriptions:

Error State Error Description
2 and 5 Invalid User
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. SQL Account has not access to server, try named pipes?
13 SQL Server service paused
16 Incoming user does not have permissions to log into the target database. Check for default database / changing db id’s
18 Change password required
27 Server could not determine the initial database for the session. Was a database name specified? Correct spelling?