SQL Server 2008 Books Online (October 2009)
Troubleshooting: Login Failed for User 'x'

When a connection attempt is rejected because of an authentication failure that involves a bad password or user name, a message similar to the following is returned to the client: "Login failed for user '<user_name>'. (Microsoft SQL Server, Error: 18456)".

Additional information returned to the client includes the following:

"Login failed for user '<user_name>'. (.Net SqlClient Data Provider)"

------------------------------

"Server Name: <computer_name>"

"Error Number: 18456"

"Severity: 14"

"State: 1"

"Line Number: 65536"

The following message might also be returned:

"Msg 18456, Level 14, State 1, Server <computer_name>, Line 1"

"Login failed for user '<user_name>'."

Additional Error Information

To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.

State Description

2

User ID is not valid.

5

User ID is not valid.

6

An attempt was made to use a Windows login name with SQL Server Authentication.

7

Login is disabled, and the password is incorrect.

8

The password is incorrect.

9

Password is not valid.

11

Login is valid, but server access failed.

12

Login is valid login, but server access failed.

18

Password must be changed.

Other error states exist and signify an unexpected internal processing error.
Examples

In this example, the authentication error state is 8. This indicates that the password is incorrect.

Date Source Message

2007-12-05 20:12:56.34

Logon

Error: 18456, Severity: 14, State: 8.

2007-12-05 20:12:56.34

Logon

Login failed for user '<user_name>'. [CLIENT: <ip address>]

ms366351.note(en-us,SQL.100).gifNote:
When SQL Server is installed using Windows Authentication mode and is later changed to SQL Server and Windows Authentication mode, the sa login is initially disabled. This causes the state 7 error: "Login failed for user 'sa'." To enable the sa login, see How to: Change Server Authentication Mode.
See Also

Other Resources

Troubleshooting Database Engine Connectivity

Help and Information

Getting SQL Server 2008 Assistance
Tags :


Community Content

LS1 Brains
Missing state ID?
I see it says "Other error states exist and signify an unexpected internal processing error" under the listing of state IDs, but ... ummm... riddle me this: Why is state 1 is given in the example, as well as being returned in practice, yet is not included in said list? Someone obviously wrote the code which returns state 1. So c'mon, what does it mean guys?

Edit: Found it elsewhere on the 'net. Apparently state 1 means "we aren't going to show you the TRUE state, so we'll try to make your life harder by simply resetting it to another number to get you looking around in circles. The REAL state ID is to be found elsewhere - try your server logs." Yep.. So, the true state id in my particular case is 38 ... *looks at list* Par for the course. Error log says "Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]" Time for more searching via Google for specifics.

Edit again: Thank God for Google. State 38 can also be thrown when, as listed above in the note about authentication modes, your authentication mode is set to Windows Authentication only, and you attempt a named login.
Tags :

Mark Crossland
Other error states exist and signify an unexpected internal processing error.
So.. what are they and how am I supposed to be able to trouble shoot why they are happening if you don't list them?

I'm getting "Error: 18456, Severity: 14, State: 38" in the SQL Server log. Thanks to Google, I know what this error is, but have no idea how to fix it. I have not changed authentication modes, SQL Server is set to use SQL and Windows logins and I'm using a user that has permission within SQL server to create and write data to a database.

Sometimes the complete lack of important info on MSDN makes me angry.
Tags :

Page view tracker