Troubleshoot “Login Failed for User” (Error 18456) in SQL Server

Error 18456 is the most common login failure in SQL Server, and the message you get is deliberately vague:

That is all the client is told, on purpose. If SQL Server explained exactly why a login failed, it would be handing an attacker a checklist. The real reason is written to the SQL Server error log as a state number, and that number is the whole game.

I have spent more time than I would like reading these states at awkward hours, so here is the version I wish I had found first.

Get the State Number First

Everything below depends on this. Without the state you are guessing.

-- Read the most recent login failures straight from the error log
EXEC xp_readerrorlog 0, 1, N'Login failed', NULL, NULL, NULL, N'DESC';

You are looking for a line like:

Login failed for user 'sa'. Reason: Password did not match that supplied for the login. [CLIENT: 10.0.0.42]
Error: 18456, Severity: 14, State: 8.

That State: 8 is the answer. The client normally receives only the generic State 1: the real reason is deliberately kept server-side so failed logins cannot be used to probe accounts, which is why the error log, not the client message, is where diagnosis starts.

If you cannot get into the instance at all, the log is still readable on disk, usually at ...\MSSQL\Log\ERRORLOG, and any text editor will open it.

What Each State Actually Means

States 8, 38 and 40 cover the large majority of real incidents. If you are in a hurry, check those three first.

The Fixes, By State

Confirm the login exists and is not locked out before assuming the password is simply wrong:

SELECT name,
       is_disabled,
       LOGINPROPERTY(name, 'IsLocked')       AS is_locked,
       LOGINPROPERTY(name, 'IsMustChange')   AS must_change,
       LOGINPROPERTY(name, 'BadPasswordCount') AS bad_password_count,
       LOGINPROPERTY(name, 'PasswordLastSetTime') AS password_last_set
FROM   sys.sql_logins
WHERE  name = N'your_login';

A non-zero bad_password_count with a recent PasswordLastSetTime usually means an application is still holding an old password and retrying, which is also how accounts get locked out. Find the application before resetting the password, or you will do this again tomorrow.

This is the state that fools people, because the login is perfectly fine.

-- Is the database actually there and online?
SELECT name, state_desc, user_access_desc, is_read_only
FROM   sys.databases
WHERE  name = N'your_database';

If state_desc is anything other than ONLINE, that is your answer. RECOVERING, SUSPECT and OFFLINE all produce a login failure that looks like a permissions problem.

State 40 specifically means the login’s default database cannot be opened. The login can be fixed without touching the database:

ALTER LOGIN [your_login] WITH DEFAULT_DATABASE = [master];

Almost always a Windows login whose access came through a group that has changed. Check what the server actually thinks the login can do:

SELECT sp.name,
       sp.type_desc,
       sp.is_disabled,
       spm.permission_name,
       spm.state_desc
FROM   sys.server_principals sp
LEFT JOIN sys.server_permissions spm
       ON spm.grantee_principal_id = sp.principal_id
      AND spm.permission_name = 'CONNECT SQL'
WHERE  sp.name = N'DOMAIN\your_login';

No CONNECT SQL grant, or a DENY, is the cause:

GRANT CONNECT SQL TO [DOMAIN\your_login];

-- 1 = Windows Authentication only, 0 = Mixed Mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;

If this returns 1 and you need SQL logins, the instance must be switched to Mixed Mode, which requires a service restart. That is a change-window job, not a quick fix.

Nothing is wrong with the server. The connection string is using a username and password for an account that is a Windows login. Switch it to integrated security.

The One That Is Not a Login Problem at All

If the error log shows no 18456 entry at the time of the failure, the connection never reached the authentication stage. That is a network, TLS or protocol problem wearing a login error’s clothing, and you should be looking at the pre-login handshake instead.

Stopping It Recurring

• Lockouts that keep coming back are almost always a service, scheduled task or connection pool holding a stale password. Find it before resetting.
• State 38 in bulk usually means an application is connecting to a database that was renamed or dropped, and nobody updated the connection string.
• Grant server access through groups, not individual logins. State 11 and 12 mostly happen when a person’s group membership changes and their individual grant was never there to begin with.

Common Questions

Related Scripts

• SELECT Permission Denied (229), the last door in the corridor this error opens
• Duplicate Key Errors (2627 / 2601), the write-side denial with the value in the message
• Login Failed: Untrusted Domain (18452), the Windows-auth sibling of this error
• Login Disabled (18470), when the login exists but is switched off
• Kerberos vs NTLM, the mechanics behind most login-failure investigations
• Get Permissions and Role Membership, one login’s effective access across the instance
• Get Error Log Patterns, spot repeated login failures before someone reports them
• Get Sysadmin Members, audit who holds the keys