DBA Scripts: Get Failed Login Summary

Login Failed for User ‘sa’, Hundreds of Times

The error log is full of the same two lines, over and over: Error 18456, State 8, Login failed for user ‘sa’, Password did not match. Sometimes it is sa, sometimes it is an application login, and it is arriving every few seconds. Before you reset anything or call it an attack, you need to know three things: which login, from where, and how often.

Read one failure at a time and it tells you almost nothing. Group them and the cause usually falls out. An internet scan hits sa and other common names from many addresses in bursts. A service or job holding an old password fails from one address, for one login, on a steady beat. A connection string pointing at a dropped database fails with valid credentials, and the reason line says so.

This script reads the current SQL Server error log, groups every login failure by login, client address and reason, and adds the lockout state for each SQL login, so you can tell those apart in one result set.

Why Repeated Failed Logins Matter

• The client only gets a generic Login failed message. The real reason, and the address it came from, are written to the error log and nowhere else by default
• A service retrying an old password locks its own login when the password policy is on, and then the correct password stops working too
• An exposed port with sa enabled is a standing invitation, and the failures in the log are the only record of how often it is being tried
• Thousands of failures bury every other line in the error log, which makes the next real incident harder to read

When to Run This Script

• The error log is filling with Error 18456 and you need to know whether it is one source or many
• An application login keeps getting locked out and nobody knows what is locking it
• After a password rotation, to find anything still connecting with the old one
• Routine SQL Server health checks (it is part of the repo’s health check suite)

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-FailedLoginSummary
Category    : security
Purpose     : Aggregated failed login analysis from the SQL Server error log and current
              lockout state per SQL login. Surfaces brute-force patterns and locked accounts.
              Complements Get-WeakLoginSettings (which checks policy configuration), this
              checks what is actually happening.
              Note: SQL Server 2025 does not write 18456 events to RING_BUFFER_SECURITY_ERROR;
              xp_readerrorlog is the reliable cross-version source for login failures.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-login-security-audit/)
Requires    : VIEW SERVER STATE, sysadmin (for LOGINPROPERTY on other logins), EXECUTE on xp_readerrorlog
HealthCheck : Yes
Notes       : The reason column is the literal text after "Reason: " in the error log entry.
              Read it before the mapped error_description: every one of these entries is logged
              as 18456 and only the reason separates a bad password from a missing default
              database, which the client sees as 4064.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

-- INSERT...EXEC cannot be used inside a CTE so materialise to a temp table first
DROP TABLE IF EXISTS #failed_logins;
CREATE TABLE #failed_logins (
    log_date DATETIME,
    process_info NVARCHAR(100),
    log_text NVARCHAR(MAX)
);

-- Filter to current error log (log# 0), type 1 (SQL Server log), login failure messages only
INSERT INTO #failed_logins
EXEC xp_readerrorlog 0, 1, N'Login failed';

WITH parsed AS (
    SELECT
        -- Login name sits between the first pair of single quotes. Some entries carry no
        -- name at all (18452 "untrusted domain" is logged as "Login failed. The login is
        -- from..."), so guard the SUBSTRING or one such line fails the whole run (Msg 537).
        CASE
            WHEN CHARINDEX('''', log_text) > 0
             AND CHARINDEX('''', log_text, CHARINDEX('''', log_text) + 1) > 0
            THEN SUBSTRING(
                log_text,
                CHARINDEX('''', log_text) + 1,
                CHARINDEX('''', log_text, CHARINDEX('''', log_text) + 1) - CHARINDEX('''', log_text) - 1
            )
            ELSE NULL
        END AS login_name,
        -- Client IP/host is in the trailing [CLIENT: ...] tag
        CASE
            WHEN CHARINDEX('[CLIENT: ', log_text) > 0
             AND CHARINDEX(']', log_text, CHARINDEX('[CLIENT: ', log_text)) > 0
            THEN SUBSTRING(
                log_text,
                CHARINDEX('[CLIENT: ', log_text) + 9,
                CHARINDEX(']', log_text, CHARINDEX('[CLIENT: ', log_text))
                    - CHARINDEX('[CLIENT: ', log_text) - 9
            )
            ELSE NULL
        END AS client_host,
        -- The log's own words: everything after "Reason: " up to the [CLIENT: tag
        CASE
            WHEN CHARINDEX('Reason: ', log_text) > 0
            THEN RTRIM(SUBSTRING(
                log_text,
                CHARINDEX('Reason: ', log_text) + 8,
                CASE
                    WHEN CHARINDEX('[CLIENT: ', log_text) > CHARINDEX('Reason: ', log_text)
                    THEN CHARINDEX('[CLIENT: ', log_text) - CHARINDEX('Reason: ', log_text) - 8
                    ELSE 4000
                END
            ))
            ELSE NULL
        END AS reason,
        -- Map reason text to the canonical error code
        CASE
            WHEN log_text LIKE '%untrusted domain%' THEN 18452
            WHEN log_text LIKE '%only administrators%' THEN 18451
            WHEN log_text LIKE '%account is disabled%' THEN 18470
            WHEN log_text LIKE '%password must be changed%' THEN 18488
            WHEN log_text LIKE '%explicitly specified database%' THEN 4060
            WHEN log_text LIKE '%default database%' THEN 4064
            WHEN log_text LIKE '%specified in the login properties%' THEN 4064
            WHEN log_text LIKE '%password did not match%' THEN 18456
            WHEN log_text LIKE '%error occurred while evaluating the password%' THEN 18456
            WHEN log_text LIKE '%could not find a login%' THEN 18456
            ELSE 18456
        END AS error_code,
        log_date
    FROM #failed_logins
),
aggregated AS (
    SELECT
        login_name,
        client_host,
        error_code,
        reason,
        COUNT(*) AS failure_count,
        MIN(log_date) AS first_failure,
        MAX(log_date) AS last_failure
    FROM parsed
    GROUP BY login_name, client_host, error_code, reason
)
SELECT
    agg.login_name,
    agg.client_host,
    agg.error_code,
    CASE agg.error_code
        WHEN 18456 THEN 'Login failed (bad password or login does not exist)'
        WHEN 18452 THEN 'Login from untrusted domain or cannot use Windows auth'
        WHEN 18451 THEN 'Login failed, only admin connections are allowed'
        WHEN 18470 THEN 'Account is disabled'
        WHEN 18488 THEN 'Password must be changed'
        WHEN 4060  THEN 'Credentials are valid, the requested database could not be opened'
        WHEN 4064  THEN 'Credentials are valid, the default database is not usable'
        WHEN 4818  THEN 'Password does not meet complexity requirements'
        ELSE 'Error ' + CAST(agg.error_code AS VARCHAR(10))
    END AS error_description,
    agg.reason,
    agg.failure_count,
    agg.first_failure AS first_failure_approx,
    agg.last_failure AS last_failure_approx,
    CASE
        WHEN sl.name IS NOT NULL
        THEN CAST(LOGINPROPERTY(sl.name, 'IsLocked') AS BIT)
        ELSE NULL
    END AS is_currently_locked,
    CASE
        WHEN sl.name IS NOT NULL
        THEN CAST(LOGINPROPERTY(sl.name, 'BadPasswordCount') AS INT)
        ELSE NULL
    END AS bad_password_count,
    CASE
        WHEN agg.error_code IN (4060, 4064)
        THEN 'CONFIG - valid credentials, database not usable (' + CAST(agg.failure_count AS VARCHAR) +
             ' in log); fix the database or the default database, not the password'
        WHEN agg.failure_count >= 50
        THEN 'CRITICAL - ' + CAST(agg.failure_count AS VARCHAR) +
             ' failures in error log; likely brute-force or application misconfiguration'
        WHEN agg.failure_count >= 10
        THEN 'WARN - repeated failures for login [' + ISNULL(agg.login_name, '(unknown)') + ']'
        ELSE 'INFO'
    END AS status
FROM aggregated AS agg
LEFT JOIN sys.sql_logins AS sl ON sl.name = agg.login_name
ORDER BY agg.failure_count DESC;

DROP TABLE #failed_logins;

It reads log 0, the current error log, filtered to lines containing “Login failed”. The login name, the [CLIENT: ...] address and the text after Reason: are cut out of each line, the reason is mapped to the error the client actually saw, and the lockout state comes from LOGINPROPERTY for any row that matches a SQL login.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Summarise failed logins from the current error log:
.\run.ps1 Get-FailedLoginSummary

# To run against a remote sql server:
.\run.ps1 Get-FailedLoginSummary -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/security/access/Get-FailedLoginSummary.sql
• powershell/wrappers/security/access/Get-FailedLoginSummary.ps1

Example Output

Real example output from a lab instance, run straight after a throwaway SQL login with the password policy on was sent the wrong password 12 times, about five seconds apart, from one address. The rest of the rows are what the lab’s own error log already held. All 15 rows are shown; error_description, reason and status are left out for width and quoted below.

The second row is the test. 12 failures, one login, one address, 5 to 6 seconds apart, and the login is now locked: is_currently_locked is 1. Its reason reads Password did not match that for the login provided. and its status WARN - repeated failures for login [zz_fl_reportsvc]. That is the shape of a service or a scheduled task holding an old password, not a person mistyping one.

Two details in that row are worth knowing before you trust it. bad_password_count reads 10, not 12: the lab’s Windows lockout threshold is 10, the login locked on the tenth failure, and the counter stopped there. And the two failures after the lock were written to the error log exactly like the ten before it, State 8, Password did not match. In the lab, the error log never said the account was locked. For those failures, only is_currently_locked did.

The top row is a different problem with the same symptom. NT SERVICE\SQLSERVERAGENT failed 16 times between 18:30 and 22:15, every 15 minutes, with the reason Failed to open the explicitly specified database 'WatchtowerMetrics'. The credentials were fine; the database it was asking for was gone. Its status says so: CONFIG - valid credentials, database not usable (16 in log); fix the database or the default database, not the password. Resetting a password there fixes nothing.

The three rows with a NULL login are Error 18452, a Windows login from an untrusted domain. That line carries no login name at all, which is why login_name is empty for it.

Attack, Stale Password or Missing Database

Read the grouped rows against these patterns. The first column is what you see in the output, the last is the next move.

A single internal address can still be hostile, and a scan can come through one NAT address. The pattern is where you start, not a verdict. If the address alone does not tell you which machine or application it is, the Login Activity Session captures logins with the host name and application name behind them.

Understanding the Results

If the script returns nothing while people are reporting failed logins, check two things. Login auditing may be set to none, in which case nothing is written: AuditLevel 2 (failed logins only) is the default, and it was 2 on the lab. Or the connection is failing before authentication, which logs no 18456 at all; that is the pre-login handshake error.

-- 0 none, 1 successful only, 2 failed only (default), 3 both
DECLARE @AuditLevel int;
EXEC master.dbo.xp_instance_regread
     N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'AuditLevel',
     @AuditLevel OUTPUT;
SELECT @AuditLevel AS AuditLevel;

How to Stop the Failures

Fix the source first, then the login. In the lab the login went from working to locked in under 50 seconds of retries, so unlocking it before the source is fixed just buys you another minute.

-- Stale password: once the service, job or connection string has the new password,
-- unlock the login. UNLOCK needs a password, so this is also the reset.
ALTER LOGIN [AppServiceLogin] WITH PASSWORD = N'ReplaceThisPassword!' UNLOCK;

-- Scan against sa: confirm a named sysadmin login exists and works first,
-- then take sa out of play.
ALTER LOGIN [sa] DISABLE;

• Stale password. The client_host names the box. On it, look at services running as that login’s application, Agent jobs and SSIS packages, scheduled tasks, linked servers and connection strings in config files. Change the password there, then unlock.
• A scan. The fix is network first. SQL Server should not be reachable from the internet on its port (see SQL Server Default Ports). Disabling sa turns every guess into a guaranteed failure; check Sysadmin Members before you do it so you are not removing the only way in.
• Lockout as a weapon. If a login with CHECK_POLICY = ON is reachable by an attacker, they can lock it on purpose. An exposed port turns lockout policy into a denial of service against your own application, which is one more reason to close the port rather than rely on lockout.
• Missing database. For 4060 and 4064 rows, the fix is the database name in the job or connection string, or the login’s default database: Set the Default Database for a Login.

For what each 18456 state means, and the login checks for State 8 in particular, see Troubleshoot “Login Failed for User” (Error 18456).

MS Docs covers MSSQLSERVER_18456 and its states, LOGINPROPERTY, password policy and lockout, ALTER LOGIN including UNLOCK, and login auditing in full.

Related Scripts

You may also find these scripts useful:

• Security (hub)
• Login Security Audit, this script alongside login last activity and weak login settings
• Login Activity Session, the host name and application behind each login
• Sysadmin Members, before you disable sa
• Recent Error Log Entries
• Error Log Patterns
• Troubleshoot “Login Failed for User” (Error 18456), every state and its fix
• Login Failed or Permission Denied
• Login Failed: The Login Is From an Untrusted Domain (Error 18452)
• Login Failed: The Account Is Disabled (Error 18470)
• Cannot Open Database Requested by the Login (Errors 4060 and 4064)
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

Hundreds of login failures are rarely a mystery once they are grouped. One login from one address on a steady beat is a stale password. Many addresses trying sa and names that do not exist is a scan. Valid credentials failing on 4060 or 4064 is a missing database. Each needs a different fix, and resetting the password only fixes one of them.

Run this before you touch the login. Read client_host and reason first, check is_currently_locked because a wrong password against a locked login is still logged as Password did not match, and fix the source before you unlock anything.