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. The state numbers are documented on Microsoft Learn; the table below is that list with what each one turns out to be in practice.

-- The two halves of a login failure are on SEPARATE lines in the log,
-- and no single filter returns both: the Error line does not contain 'Login failed',
-- and the Reason line does not contain '18456'. Read twice and union.
DECLARE @log TABLE (LogDate datetime, ProcessInfo nvarchar(64), Txt nvarchar(max));

INSERT INTO @log EXEC xp_readerrorlog 0, 1, N'18456';        -- gives you the State
INSERT INTO @log EXEC xp_readerrorlog 0, 1, N'Login failed'; -- gives you the reason and the client

SELECT TOP (20) LogDate, Txt
FROM   @log
ORDER  BY LogDate DESC;

You are looking for a line like:

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

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 3 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.

In a corporate environment the Windows sibling of this error, 18452, is the one I see more than almost any other, and when I do it is usually the same story as State 8: the account is locked out, or the password changed and the client is still holding the old one.

This is the state that fools people, because the login is perfectly fine. State 38, and 46 which Microsoft lists alongside it, means the database the connection string asked for: the reason line reads Failed to open the explicitly specified database 'YourDb'. State 40 means the login’s default database instead, and its reason line reads Failed to open the database 'YourDb' specified in the login properties. Check the database either way:

-- 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. If the database is not there at all and this is State 38, the fix is on the client: the connection string’s Initial Catalog names a database that was renamed, dropped or never existed on this instance.

For State 40 the login’s default database is the problem, and 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];

If the grant is there and State 11 keeps coming, read the reason line. Token-based server access validation failed with an infrastructure error usually means the user’s only route in is the local Administrators group, and Windows does not present that membership unless the client is elevated. Start SSMS with Run as administrator to get in, then add the Windows user as a login in its own right so it stops depending on the group.

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

Frequently Asked Questions

Where To Go Next