$ guides / microsoft-sql-server / microsoft-sql-server-error-18456-login-failed ▌

Operations Guides

SQL Server Error 18456: login failed for user, and what the state code means

SQL Server Error 18456 is the universal “Login failed for user X” message. It is deliberately vague: every client, from sqlcmd to the application’s connection pool, sees the same string with severity 14 and state 1. The client never learns whether the password was wrong, the login does not exist, the database is offline, or the account is disabled. That information lives only in the SQL Server error log, encoded as a state code.

Triage on the state code. State 5, state 8, and state 38 are completely different incidents even though the application logs them identically. Treating 18456 as a single error class leads to wasted time chasing the wrong cause, and ignoring failed logins entirely means missing both credential drift (the steady hum of misconfigured apps and rotating secrets) and brute-force attacks (the flood from an unexpected host).

There is no Failed Logins/sec performance counter. Logins/sec under General Statistics counts every login attempt, including failed ones, so it cannot distinguish success from failure. The only authoritative sources for failed login activity are the error log (via xp_readerrorlog) and SQL Server Audit. Anything else is inferred from connection count churn or application retry behavior.

What this means

A 18456 event means the engine rejected an authentication attempt before the session could do any work. The connection is torn down. From the client’s perspective it is a hard error: connection refused, must reconnect. If the client is a pooled application with retry logic, a single misconfigured credential can generate a retry storm that looks like a traffic spike on User Connections while Batch Requests/sec drops, because no session ever makes it past authentication.

The client message and the error log entry differ on purpose. Microsoft masks the reason to avoid leaking information that would help an attacker (e.g., distinguishing “this username exists, this password is wrong” from “this username does not exist at all”). The cost of that protection is that the operator must look at the server side to triage.

Severity is always 14 for 18456. It is not a criticality hint by itself; the impact depends on which principal is failing, from where, and at what rate.

Common causes

Cause	What it looks like	First thing to check
Wrong password	State 8, single login or service account, often after a credential rotation	Error log entry: state code
Login does not exist	State 5, application migrated or principal renamed	`sys.server_principals` for the login
Default database not accessible	State 40, or state 38 if explicit DB in connection string, after a DB goes offline or is restored	`sys.databases.state_desc`
Login disabled	State 7 (also wrong password) or error 18470 if password correct but account disabled	`sys.sql_logins.is_disabled`
Password must change	State 18 (legacy; modern SQL Server raises 18488 instead)	`sys.sql_logins.is_policy_checked`, `must_change`
Permission to connect	State 146-149 (SQL 2016+) or state 11/12 (older), missing CONNECT SQL	`sys.server_permissions` for the login
Brute force or password spray	Sustained flood of failures from one or few source IPs targeting many principals or `sa`	Source IP distribution in error log

Quick checks

-- Find failed login entries in the current error log, newest first
EXEC xp_readerrorlog 0, 1, 'Login failed', NULL, NULL, NULL, 'DESC';

-- Filter to just 18456 entries; the State: N appears in the line
EXEC sp_readerrorlog 0, 1, '18456';

-- Inspect the state of the named database (suspect state 38 or 40)
SELECT name, state_desc, user_access_desc, is_read_only
FROM sys.databases
ORDER BY name;

-- Check whether the login exists, is enabled, and is locked out
SELECT name, is_disabled, is_policy_checked, is_expiration_checked,
       loginproperty(name, 'isLocked') AS is_locked,
       loginproperty(name, 'PasswordHash') IS NOT NULL AS has_sql_password
FROM sys.sql_logins
WHERE name = '<login>';

All of the above are read-only.

On Linux, you can also tail the log file directly:

tail -n 200 /var/opt/mssql/log/errorlog | grep -i 'Login failed'

How to diagnose it

Get the exact error log entry. The client-side error does not help. Pull the entry with xp_readerrorlog 0, 1, 'Login failed' and note the state number, the login name, the source IP, and the timestamp.
Map the state to a category. Use the state table below. The state is the single most useful piece of information in the entry.
Identify the scope. Is it one login, one service account, or many principals from one source? One login failing across many application pods is credential drift. Many logins failing from one source is a password spray.
Check for application impact. A burst of 18456 events typically coincides with a drop in Batch Requests/sec (sessions never reach the engine) and a spike in User Connections if the client retries aggressively. If you see both, you have reconnect churn.
Decide on severity. Sporadic failures from known hosts are TICKET: credential drift, expired secret, stale connection string. A sustained flood from unexpected hosts targeting privileged principals is PAGE: this looks like an attack and you need to contain it.

The state table below covers the states you will see in practice. Not every state is documented by Microsoft; the well-known operator reference is the community list maintained by Aaron Bertrand. States 146-149 replaced older states in SQL Server 2016 for more granular permission diagnosis.

State	Meaning	Operator action
1	State used in the client message. Also raised server-side when the engine masks the real reason for security. Treat as “go read the log.”	Look at the same entry in the error log, not the client.
5	Login does not exist.	Check `sys.server_principals`; possible rename or wrong instance.
6	Attempt to use a Windows login name with SQL Authentication.	Fix the connection string auth mode.
7	Login is disabled AND password is incorrect. If the password is correct but the login is disabled, you get error 18470 with state 1, not 18456.	Re-enable the login (if intended) and correct the password.
8	Wrong password. The most common state.	Rotate or correct the credential; check for typos, expired secrets, special character handling (see Docker note below).
11 / 12	Valid login but server access validation failed (older versions; SQL 2016+ raises 146-149 for more detail).	Check CONNECT SQL permission and endpoint access.
18	Password must be changed. Modern SQL Server typically raises error 18488 for this case; state 18 is effectively legacy.	Force a password change.
38	Database named in the connection string is not accessible (offline, restoring, suspect, dropped).	Check `sys.databases.state_desc` for the named DB. Common after AG failover or restore.
40	Default database for the login is not accessible.	Use `ALTER LOGIN ... WITH DEFAULT_DATABASE = ...` to repoint. Do not use `sp_defaultdb`; it is deprecated.
58	Attempt to log in using SQL Server Authentication while the server is configured for Windows Authentication mode only.	Check `SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')`. Either enable mixed mode or update the connection string to use Windows auth (e.g., `Trusted_Connection=True`).
102-111	Azure Active Directory authentication failures (later versions).	Check AAD/Entra configuration, token, tenant.
132-133	Azure Active Directory authentication failures (later versions).	Check AAD/Entra configuration, token, tenant.
146	Valid Windows auth login but missing CONNECT SQL permission (replaces state 11/12 on SQL 2016+).	Grant CONNECT SQL.
147	Valid SQL auth login but missing CONNECT SQL permission.	Grant CONNECT SQL.
148	Valid Windows auth login but missing connect endpoint permission.	Grant CONNECT on the endpoint.
149	Valid SQL auth login but missing connect endpoint permission.	Grant CONNECT on the endpoint.

States 16 and 27 only occur prior to SQL Server 2008; in SQL Server 2008 and later, state 16 was replaced by state 40 (default DB) and state 27 was replaced by state 38 (explicit DB).

Metrics and signals to monitor

There is no native counter for failed logins. The signals below require correlation.

Signal	Why it matters	Warning sign
18456 entries in error log	The only authoritative record of failed logins	Any sustained rate above baseline, or any failures for `sa` or service accounts
State code distribution	Maps the failure to a cause category	A sudden shift from state 8 (typo) to state 5 (missing principal) indicates a deployment changed something
Source IP in 18456 entry	Distinguishes credential drift from brute force	Repeated failures from IPs outside the expected app subnet
`User Connections` counter	Reconnect churn from failed logins shows as elevated connection count	Climbing connections with flat or dropping `Batch Requests/sec`
`Batch Requests/sec`	Failed logins cannot submit batches	Sudden drop without other explanation, alongside 18456 entries
Database state (`sys.databases`)	State 38 / state 40 root cause	Any production database not in ONLINE state
SQL Server Audit (`LOGIN_FAILED`)	Structured capture for compliance and alerting	New audit records when no application change is expected

On SQL Server 2022 and later, reading audit files via fn_get_audit_file requires VIEW SERVER SECURITY AUDIT permission. Earlier versions required CONTROL SERVER. Plan your monitoring principal accordingly.

Fixes

Group fixes by root cause, not by state code, because several states share underlying fixes.

Credential drift and wrong passwords (state 8)

The most common case. A service rotated its secret, an app pool was not updated, or a connection string was copy-pasted from the wrong environment.

Identify the credential from the error log entry. The login name is in the line.
Confirm the correct current value with the secret manager or the owning team.
Update the application configuration and cycle the affected pods or workers.
Verify with a single test connection from the same host the application uses.
For Docker containers, avoid special characters in SA_PASSWORD. Characters such as $ can fail to parse silently; the container starts but the SA login never works. Stick to alphanumeric characters plus the safe symbols # and *.

The login was renamed, dropped, or never created on this instance. Common after migrations, cross-environment restores, or a connection string pointing at the wrong server.

Confirm the login is missing: SELECT * FROM sys.server_principals WHERE name = '<login>';
If the login exists at a different scope (e.g., a contained database user), the connection may need to specify the database explicitly. Do not use “Browse server” in SSMS for contained databases; it can authenticate against the server-level principal first and fail with state 65 when the passwords differ.
If this is post-AG-failover, remember that server-level logins are not replicated. The database-level users exist on the secondary but the matching server logins may be missing or have different SIDs. Re-link with ALTER USER ... WITH LOGIN = ... in the database context.

Database not accessible (states 38 and 40)

The login is valid, the password is correct, but the database in the connection string (state 38) or the login’s default database (state 40) cannot be opened. Common during restores, after an AG failover, or when a DB has been set offline.

Check sys.databases.state_desc for the named database.
If the database is RECOVERY_PENDING or SUSPECT, do not just re-point the login. Fix the database. See the related guide on suspect and recovery-pending databases.
For state 40, repoint the default database with ALTER LOGIN [<login>] WITH DEFAULT_DATABASE = [<accessible_db>];. Do not use sp_defaultdb; it is deprecated.
For state 38, fix the connection string to point at a database that is actually online, or fix the database.

Disabled logins (state 7 and error 18470)

State 7 specifically means the login is disabled AND the password is wrong (password validation runs first). If the password is correct but the login is disabled, SQL Server raises error 18470 instead, also with state 1. The two errors together tell you the account state.

Check sys.sql_logins.is_disabled.
Re-enable if intended: ALTER LOGIN [<login>] ENABLE;
Reset the password if state 7.

Password must change (state 18 / error 18488)

State 18 is the legacy form. Modern SQL Server typically raises error 18488 instead. Either way, the login was created with MUST_CHANGE and the password has never been changed from the initial value.

Have the principal, or a DBA, set a new password.
If this is a service account, reconsider whether MUST_CHANGE and CHECK_EXPIRATION are appropriate. They usually are not for service accounts.

Brute force or password spray

The defining signal is a sustained flood of failures from one or a small number of source IPs, often targeting many principals or specifically sa. This is PAGE territory.

Capture the source IPs from the error log entries.
Block at the network edge (firewall, NSG, load balancer) rather than at SQL Server.
Disable sa if it is enabled, and rename it if your policy allows.
Confirm SQL Server is fully patched. Authentication-related CVEs are rare but they do happen and can affect login behavior.
Review whether mixed-mode authentication is required. If Windows-only auth is acceptable, switching removes the entire SQL-auth attack surface.
Enable SQL Server Audit for LOGIN_FAILED if you have not already. The error log is hard to alert on at scale; audit gives you structured records.

Prevention

Treat connection strings as code. The most common 18456 storm is a misdeployed connection string or a stale secret. The error log tells you which login failed; the deploy log tells you what changed.
Centralize service account credentials in a secret manager and rotate on a schedule, with the application config update automated. Manual rotation is where drift enters.
Pre-stage databases for failover. State 38 after an AG failover is almost always because the secondary is not in the expected state or the connection string points at the wrong database. Validate failover readiness regularly, not just at install time.
Keep server-level logins synchronized across AG replicas. The login SID must match the database user SID on every replica, or you get state 5 / state 38 after failover.
Enable login auditing at the server level. Failed logins only is the default; both successes and failures is noisier but more forensic.
Configure SQL Server Audit for LOGIN_FAILED and ship the records to a SIEM or log pipeline that can alert on rate and source IP.
Patch. Authentication-related CVEs in SQL Server are rare but they happen. A fully patched instance is a precondition for trusting your 18456 analysis.

How Netdata helps

Netdata collects per-second SQL Server performance counters that give you the context to triage a 18456 burst without leaving the dashboard:

User Connections and Batch Requests/sec at per-second resolution reveal reconnect churn: connections climbing while batch requests drop is the fingerprint of a failed-login retry loop.
Database state changes (ONLINE to OFFLINE/SUSPECT/RECOVERY_PENDING) in the same time window as a burst of 18456 events point directly at state 38 or 40.
AG replica health and worker thread utilization alongside connection metrics let you distinguish credential drift from a failed-over database from thread exhaustion.

For the 18456 entries themselves, the error log remains authoritative. Use xp_readerrorlog or SQL Server Audit to get the state code, then correlate with Netdata’s per-second metrics to confirm application impact.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics and ML anomaly detection.

The Netdata solution

Microsoft SQL Server monitoring with Netdata

Netdata monitors SQL Server with per-second metrics, pre-built dashboards, and ML-powered anomaly detection. Correlate wait statistics, blocking chains, transaction-log and TempDB pressure, Page Life Expectancy, memory grants, per-file I/O stalls, and AlwaysOn send/redo queues against the rest of your stack so you catch the incidents in these runbooks before they page anyone.

See SQL Server monitoring → Start monitoring free