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

Alla lainattuna Microsoftin blogista ja muista artikkeleista liittyen Login Failed ilmoitukseen. Käytännössä virheen selvittämiseksi pitää päästä käsiksi SQL Serverin error-lokiin. Jos siis esimerkiksi Open Library Solutions Finland Oy:n valmistamat ohjelmat palauttavat virheen "18456", voi SQL Serverin lokista tarkistaa tarkemman syyn allaolevan selityksen avulla.

Nämä tiedot pätevät SQL Server 2005 ja 2008 -versioihin. Alkuperäinen Blog-post viittaa SQL Server 2005:n. Pätee todennäköisesti myös uudempiin, mutta sitä ei ole erikseen varmistettu.

--

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 '<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. The common error states and their descriptions are provided in the following table:

ERROR STATE ERROR DESCRIPTION

2 & 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 & 12 Valid login but server access failure
13 SQL Server service paused
16 The target database could not be determined
18 Change password required
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.

2014/PP: Lisäinfoa uudemmista artikkeleista. Lisätty eri state-numeroita. 2008.12.15/PP: Alkuperäinen blogi: http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx