SQL Server failed login storm: brute force, credential drift, and service-account failures

A failed-login storm fills ERRORLOG with “Login failed for user” entries (Error 18456). Counting them is the first instinct and the wrong one. Aggregate rate cannot tell brute force from a batch job running on rotated secrets, a service account whose password just expired, an application pool recycling, or a SQL Server 2025 transactional replication secondary emitting benign 18456-class audit noise every few minutes (a reported pattern).

Two pieces of context SQL Server records but does not surface in any performance counter do the work: the source IP (or hostname) and the target principal. With those, the picture collapses into one of a handful of patterns, each with a different severity and a different fix.

There is no Failed Logins/sec counter in sys.dm_os_performance_counters. The Logins/sec counter under General Statistics counts every login attempt, including failed ones, so it cannot distinguish success from failure. To see failures, parse the error log, or run SQL Server Audit (FAILED_LOGIN_GROUP) or an Extended Events session on error_reported. This article assumes at least one of those capture paths is available.

Error 18456 state codes

Error 18456 is the canonical login-failure event. The message returned to the client is deliberately unhelpful: state is always reported as 1 to avoid leaking which step of authentication failed. The real state code and reason are written only to the SQL Server error log.

The state code is the fastest triage signal.

State 8 dominates most storms. To distinguish “wrong password” from “locked account” from “real brute force”, you need source IP and target principal.

Ticket on a sustained flood from one source, on service-account failures, or on a rate more than 5x the hourly baseline. Page only with source-aware evidence: privileged-target spray from unexpected hosts, or service-account failures causing an active application outage. Any sa attempt from a non-trusted source is notable on its own.

Common causes

Quick checks

All read-only. Run on the instance being flooded.

-- Read the most recent Login failed entries (newest first)
EXEC xp_readerrorlog 0, 1, 'Login failed', NULL, NULL, NULL, 'DESC';

-- Filter specifically for the 18456 line so you can read the real state code
EXEC xp_readerrorlog 0, 1, '18456';

-- Any sa attempts in the current log? Notable on its own.
EXEC xp_readerrorlog 0, 1, 'Login failed', 'sa';

There is no built-in DMV that returns parsed error-log rows. Stream xp_readerrorlog output into a temp table to bucket by source IP and target principal, or use SQL Server Audit / Extended Events for structured capture.

-- Account lockout state for a suspect login (requires CHECK_POLICY = ON)
SELECT name,
       LOGINPROPERTY(name, 'IsLocked')         AS is_locked,
       LOGINPROPERTY(name, 'BadPasswordCount') AS bad_password_count,
       LOGINPROPERTY(name, 'BadPasswordTime')  AS bad_password_time,
       LOGINPROPERTY(name, 'LockoutTime')      AS lockout_time,
       LOGINPROPERTY(name, 'PasswordLastSetTime') AS password_last_set_time,
       is_policy_checked,
       is_expiration_checked
FROM sys.sql_logins
WHERE name IN ('<suspect_login>', 'sa');

-- Confirm who currently holds sysadmin (post-incident check after a spray)
SELECT sp.name, sp.type_desc, sp.create_date, sp.modify_date
FROM sys.server_principals sp
JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
JOIN sys.server_principals rp    ON srm.role_principal_id = rp.principal_id
WHERE rp.name = 'sysadmin';

# Confirm the SQL Server service account state on the host (Windows).
# MSSQLSERVER is the default instance; named instances use MSSQL$<InstanceName>.
Get-WmiObject Win32_Service -Filter "Name='MSSQLSERVER'" |
    Select-Object Name, StartName, State

# On Linux hosts, confirm the SQL service is up
systemctl status mssql-server

How to diagnose it

1. Confirm the flood is real, not a log-recycle artifact. Cycles of sp_cycle_errorlog and high-volume auditing can make a normal background rate look alarming. Compare current failure count against your hourly baseline.
2. Bucket failures by source IP and target principal. SQL Server Audit’s FAILED_LOGIN_GROUP gives you the application name, client host, and client IP per failure, while an Extended Events session on sqlserver.error_reported (filter [severity]=(14) AND [error_number]=(18456)) gives you client_host_name and nt_username; pick the capture that fits your tooling.
3. Apply the decision tree below.
4. For service-account patterns, check LOGINPROPERTY(name, 'IsLocked'). If locked, find out why before unlocking. A locked SQL login with CHECK_POLICY = ON follows Windows lockout policy: threshold, duration, and reset counter come from the local security policy or the domain GPO.
5. Verify the SQL Server service account itself. If the service account is locked or disabled on the domain controller, every SQL authenticated login with CHECK_POLICY = ON fails until the service account is unlocked. This is one of the most confusing failure modes: the entire instance appears to reject all credentials, but the engine is fine.

flowchart TD
    A[Error 18456 flood] --> B{Single source IP or few?}
    B -- Yes --> C{Targeting sa or privileged?}
    B -- No, many sources --> D{Same target login?}
    C -- Yes --> E[Brute force or spray - PAGE if non-trusted]
    C -- No --> F[Stale app secret - TICKET]
    D -- Service account --> G[Lockout, expiry, or AD unreachable]
    D -- Many logins --> H[Restart storm or replication noise]
    G --> I{SQL service account locked?}
    I -- Yes --> J[Global auth failure - unlock service account]
    I -- No --> K[Rotate secret or fix SPN]

Metrics and signals to monitor

Fixes

Brute force or password spray

Short term: block the source IPs at the network layer (firewall, host-level Windows Firewall, cloud NSG). Do not rely on SQL Server to rate-limit: there is no native brute-force detection feature. Disabling the targeted login usually makes things worse, because the attacker has now achieved a denial of service for you.

Medium term: enforce Windows account lockout policy via CHECK_POLICY = ON on SQL logins so repeated bad passwords auto-lock the account. Disable or rename sa. ALTER LOGIN [sa] DISABLE and ALTER LOGIN [sa] WITH NAME = [dbo_sa] are both safe operations: the engine tracks the sa principal by its SID (0x01), not by the name. Coordinate any rename with app owners first, and note that both are disruptive while they propagate.

Confirm whether the attacker ever succeeded. Check sysadmin role membership and pull security-audit events from the default trace.

Credential drift

Short term: identify the secret that changed and either roll forward the connection strings to the new secret, or roll back the secret to the previous value if the rotation was premature.

Medium term: alert on the start of every secret rotation so on-call can watch the failure rate during the window. The pattern is distinctive once you have source-aware capture: many legitimate hosts, one principal, starting at a rotation timestamp.

Service-account failures

Distinguish two failure classes.

The service account that runs the app is locked or expired. Unlock on AD (Unlock-ADAccount), reset the password, update connection strings. Check whether CHECK_EXPIRATION = ON triggered it.

The SQL Server service account itself is locked. Every SQL login with CHECK_POLICY = ON fails until you unlock it. The engine looks healthy, the network is fine, and yet every connection rejects. This is a documented support scenario (KB925744). Unlock the service account on the DC and SQL auth resumes immediately.

For gMSA-managed service accounts on AG listeners, also check SPN registration against the listener name. A missing or mis-registered listener SPN makes Kerberos authentication fail and fall back to NTLM, which can cause intermittent authentication failures.

Prevention

• Enable SQL Server Audit with a server audit specification capturing FAILED_LOGIN_GROUP. Server-level specs work in all editions. On SQL Server 2022+, readers of sys.fn_get_audit_file need VIEW SERVER SECURITY AUDIT instead of CONTROL SERVER.
• Pair audit with Extended Events on sqlserver.error_reported filtered to severity 14 and error 18456 when you need nt_username; audit records already include application_name and client_host_name.
• Enforce CHECK_POLICY = ON (and CHECK_EXPIRATION = ON where appropriate) on every SQL login. Lockout threshold, duration, and reset counter come from the Windows account policy: set them deliberately via secpol.msc or GPO, not by accident.
• Disable or rename sa. Track any sa attempt from non-trusted sources as a notable event regardless of volume.
• Maintain a source-IP allowlist baseline per principal. Anomaly detection against that baseline is what turns raw failure counts into actionable signal.
• Validate the SQL Server service account state in your host health checks, not just your SQL health checks. A locked service account is invisible to DMVs.
• Document build-specific false positives. Some SQL Server 2025 builds emit benign recurring 18456-class audit events (error 17830 / state 106) on transactional replication secondaries every few minutes. Record the pattern when you see it so future on-call engineers do not page on it.

How Netdata helps

• Per-second metric collection catches the failure-rate slope early, before the error log buries you. Correlate the failure spike with batch requests/sec and user connections to confirm whether the storm is impacting real workload.
• ML-based anomaly detection on login-related log events and connection counts surfaces the deviation from baseline without hand-tuned thresholds.
• Correlated dashboards let you overlay the failure spike against SQL service account state, AG replica health, and application login success rate, so you can tell a global auth failure from an isolated spray in seconds.
• Audit-log and error-log parsing surface the source IP and target principal that aggregate counters cannot, which is exactly the evidence the playbook requires before paging.
• Anomaly retention across restarts preserves the failure pattern even when the engine restarts, which matters for post-incident forensics on credential drift and service-account lockouts.

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

Related guides

• SQL Server AlwaysOn failover readiness: quorum, health checks, and the failover you assume works
• SQL Server Availability Group not synchronizing: NOT_HEALTHY replicas and failover risk
• SQL Server AG send and redo queues growing: replication lag and failover RTO
• SQL Server backup freshness: the recovery point you only discover you lack during an incident
• SQL Server blocking chains: finding the head blocker before workers run out
• SQL Server buffer cache hit ratio low: when the working set no longer fits in memory
• SQL Server user connections climbing: connection pool leaks and retry storms
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server database in SUSPECT or RECOVERY_PENDING: an offline database and how to recover it
• SQL Server Error 1205: transaction was deadlocked and chosen as the deadlock victim
• SQL Server Error 701: there is insufficient system memory to run this query