Event ID: 18456

Typically seen with application to SQL DB authentication. You can find more detail out on Microsoft’s support web site but here are some things to look at just off the top: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 will signify an unexpected internal processing error.So here are some other things you might want to try (as RECOVERY_PENDING is my favorite issue :p )

SELECT name, principal_id, type_desc, is_disabled, default_database_name, default_language_name
FROM

 

 

sys.server_principals
WHERE name = ‘login_name’
goSELECT name, database_id, user_access_desc, state_desc, is_in_standby
FROM

 

 

sys.databases
WHERE name = ‘default_db_name’
goSELECT

 

 

suser_name(role_principal_id), suser_name(member_principal_id)
FROM
sys.server_role_members
WHERE member_principal_id = suser_id( ‘login_name’ )
goSELECT class_desc,

 

 

suser_name(grantee_principal_id), permission_name, state_desc
FROM
sys.server_permissions
WHERE grantee_principal_id = suser_id( ‘login_name’ )
Go

Pay attention to the default database, if the login is disabled and if it has at least CONNECT SERVER permission, if the server is in a different mode than ONLINE etc.

Posted in