Export (0) Print
Expand All

Troubleshooting: Login failed for user 'x'

New: 14 April 2006

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>'."

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.

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

Date Source Message

2005-09-13 20:12:56.34

Logon

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

2005-09-13 20:12:56.34

Logon

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

ms366351.note(en-US,SQL.90).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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft