SQL Server 'Login failed for user' (error 18456): read the state and fix the cause

#The symptom

An application, job or user cannot connect and reports:

Login failed for user 'app_orders'. (Microsoft SQL Server, Error: 18456)

The message sent to the client is deliberately vague and shows State: 1 whatever the cause, so an attacker learns nothing. The real reason and state are written to the SQL Server error log. Start there, not with a password reset.

#1. Get the real state from the error log

-- Current log, SQL Server log type (1), entries containing 'Login failed'
EXEC sys.xp_readerrorlog 0, 1, N'Login failed';

Each failure produces two lines:

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

The Reason: text and the [CLIENT: ...] address are what you need. If the log was cycled recently, search the previous file too (EXEC sys.xp_readerrorlog 1, 1, N'Login failed';).

On Amazon RDS for SQL Server, read the log with the RDS procedure or from the console:

EXEC rdsadmin.dbo.rds_read_error_log @index = 0, @type = 1;

Or download it with the CLI:

aws rds download-db-log-file-portion --db-instance-identifier prod-sql \
  --log-file-name log/ERROR --output text | grep -A1 "18456"

Tip

If nothing appears in the log, check the login auditing level (server Properties > Security > Login auditing). The default, "Failed logins only", records these events. "None" hides them.

#2. Map the state to the cause

Microsoft does not publish a complete list of states, and new ones appear in newer builds. Trust the Reason: text over this table if they disagree.

Two related errors are often confused with 18456: 18486 means the SQL login is locked out by the Windows password policy, and 18452 means the login is from an untrusted domain (a Kerberos or trust problem, not a SQL permission).

#3. Check the login

SELECT name, is_disabled, default_database_name,
       LOGINPROPERTY(name, 'IsLocked')       AS is_locked,
       LOGINPROPERTY(name, 'IsExpired')      AS is_expired,
       LOGINPROPERTY(name, 'IsMustChange')   AS must_change,
       LOGINPROPERTY(name, 'BadPasswordCount') AS bad_pw_count,
       is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE name = N'app_orders';

For Windows logins and groups, use sys.server_principals (types U and G) and check is_disabled.

Check for a denied connect permission:

SELECT pr.name, pe.permission_name, pe.state_desc
FROM sys.server_permissions pe
JOIN sys.server_principals pr ON pr.principal_id = pe.grantee_principal_id
WHERE pe.permission_name = 'CONNECT SQL'
  AND pr.name = N'app_orders';

#4. Fix by cause

Wrong password (8, 9) or must change (18). Find which client is failing from the [CLIENT: ...] address. It is often an old app server, a scheduled task or a linked server still using the previous password. Fix the secret there. Reset the password only if the owner agrees:

ALTER LOGIN [app_orders] WITH PASSWORD = N'<new strong password>';

Warning

Changing a shared service login's password breaks every other client still using the old one. List all source addresses for that login in the log before you change it.

Disabled (7) or locked out (18486).

ALTER LOGIN [app_orders] ENABLE;
-- Unlock requires setting the password in the same statement
ALTER LOGIN [app_orders] WITH PASSWORD = N'<password>' UNLOCK;

No server access (11, 12).

GRANT CONNECT SQL TO [app_orders];
-- or remove an explicit deny
REVOKE CONNECT SQL FROM [app_orders];
GRANT CONNECT SQL TO [app_orders];

For a Windows user who gets in through a group, confirm the user is still in the group (whoami /groups on the client, or check AD) and that the group login exists on the instance. A user who is a local administrator on the server and relies on BUILTIN\Administrators will fail under UAC unless SSMS is run elevated; give them their own login instead.

Database cannot be opened (38, 40). Check the database state:

SELECT name, state_desc, user_access_desc FROM sys.databases WHERE name = N'Orders';

If the default database is gone, point the login at master:

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

If the database is online, the user may be orphaned after a restore or migration: the database user exists but its SID no longer matches the login. Find and fix it in the database:

USE [Orders];
SELECT dp.name AS user_name, dp.sid
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.type = 'S' AND dp.authentication_type = 1 AND sp.sid IS NULL;

ALTER USER [app_orders] WITH LOGIN = [app_orders];

Windows-only authentication (58). Check the mode:

SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');  -- 1 = Windows only

Change it under server Properties > Security > "SQL Server and Windows Authentication mode", then restart the SQL Server service. The change does not take effect until the restart.

#Logins missing after an AG failover

State 5 straight after an availability group failover means the login was created only on the old primary. Logins live in master and are not part of the AG. Recreate SQL logins on every replica with the same SID and password hash, so the database users match. On the primary:

SELECT name,
       CONVERT(varchar(max), LOGINPROPERTY(name, 'PasswordHash'), 1) AS pw_hash,
       CONVERT(varchar(max), sid, 1) AS sid
FROM sys.sql_logins
WHERE name = N'app_orders';

On each secondary, use the values returned:

CREATE LOGIN [app_orders]
  WITH PASSWORD = 0x0200A1B2... HASHED,
       SID = 0x5C3E...,
       CHECK_POLICY = OFF;

See AG database not synchronizing for the rest of the AG health checks.

#Prevent it next time

• Keep logins in sync across AG replicas with a scheduled script or a tool such as dbatools Copy-DbaLogin, and check for differences after every login change.
• Store application passwords in a secret store (Key Vault, Secrets Manager) and rotate them with a dual-login or overlap window, so no client is left on the old password.
• Alert on bursts of 18456 from one client address. Many failures in a short time are either a broken deployment or a password-guessing attempt; both need attention.
• Do not drop or rename a database that is a login's default; set the default to master for service logins.
• Prefer Windows or Entra ID authentication where possible, so there is no SQL password to drift. See identity and networking for SQL Server on Azure.