Home / Guides / Find failed SQL Server logins (error 18456)

Find failed SQL Server logins (error 18456) on all your servers

An application cannot connect, a service account keeps getting locked out, or somebody is guessing the sa password, and the only clue the client gets is "Login failed for user". The real reason is on the server, in a state number the client never sees. This guide covers what each state of error 18456 means, where SQL Server writes failed logins, how to read them with sp_readerrorlog and with Get-WinEvent, a PowerShell script that collects them from many instances into one summary, how to read the pattern, and where these methods stop.

What the state of error 18456 means

For security, SQL Server sends the client the same message for every failed login. The error log entry carries a state number that tells the administrator the real reason. These are the states Microsoft documents on its reference page for the error:

Microsoft adds that other states exist and mean an unexpected internal error. Newer versions also write the reason in words after Reason:, which is often enough on its own. Texts you will see, with the cause Microsoft gives for each:

• "Password did not match that for the login provided." A SQL login with the wrong password.
• "Could not find a login matching the name provided." The login does not exist on this instance, or the connection string points at the wrong server.
• "An attempt to login using SQL authentication failed. Server is configured for Windows authentication only." The instance is not in mixed mode.
• "Failed to open the explicitly specified database '...'." The database in the connection string is misspelled, offline or not available to that login.
• "Failed to open the database '...' specified in the login properties." The login's default database is not available (the client sees error 4064).
• "Token-based server access validation failed with an infrastructure error." A Windows login that has no access of its own (state 11).

A user name of NT AUTHORITY\ANONYMOUS LOGON, an empty name or (null) points to Kerberos, SPN or domain controller problems rather than a wrong password; Microsoft's page for the error has a section for each.

Where failed logins are written

• The SQL Server error log, as the two lines shown above with the process name Logon. It is cycled at every restart and by default only the current log and six older ones are kept; our guide to reading error logs from many instances covers that in detail.
• The Windows Application log, as event ID 18456. Microsoft's documentation of the login audit setting says the audits "are written to the error log and to the Windows Event Viewer". The source is MSSQLSERVER for a default instance and MSSQL$ plus the instance name for a named one. The Application log is not cycled by SQL Server restarts, so it can go further back, depending on its size.
• Only if login auditing is on. The setting is in SSMS: right-click the server, Properties, Security, Login auditing: None, Failed logins only, Successful logins only, or Both. A change needs a restart of the service. With None, no 18456 lines are written at all.

To see the current setting and the authentication mode of an instance:

USE [master];
-- none, success, failure or all (needs CONTROL on master; xp_loginconfig is deprecated)
EXECUTE master.dbo.xp_loginconfig 'audit level';

-- 1 = Windows authentication only (SQL logins then fail with state 58), 0 = mixed mode
SELECT SERVERPROPERTY('ServerName')               AS server_name,
       SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;

audit level returns none, success, failure or all. Microsoft marks xp_loginconfig as a feature that will be removed in a future version, and it needs CONTROL on the master database, so it is a check for an administrator, not for a monitoring login. windows_auth_only is 1 when the instance accepts Windows authentication only; every SQL login then fails with state 58.

If you need more than the error log keeps (the application name and host of each failed attempt, or a tamper-resistant record), SQL Server Audit has a FAILED_LOGIN_GROUP action group that writes to a file, the Application log or the Security log. That is a separate setup on each instance and is not covered here.

Read them on one instance with sp_readerrorlog

sp_readerrorlog takes the log number (0 is the current log), the product (1 for SQL Server) and up to two search strings that a line must both contain:

USE [master];
-- every failed login in the current error log: login, reason and client
EXECUTE sp_readerrorlog 0, 1, N'Login failed for user';

-- the state lines that go with them
EXECUTE sp_readerrorlog 0, 1, N'Error: 18456';

-- one login only, in the log before the last restart
EXECUTE sp_readerrorlog 1, 1, N'Login failed for user', N'app_sales';

-- wrong passwords only
EXECUTE sp_readerrorlog 0, 1, N'Login failed for user', N'Password did not match';

Always pass the product number 1 when you search: with NULL there, the search strings are ignored. The login needs VIEW ANY ERROR LOG (SQL Server 2022 and later), VIEW SERVER STATE or the securityadmin role. The state is on the Error: 18456 line, the name and client on the Login failed line; both have the same time stamp, which is how the script below puts them together.

Read them from the Application log with Get-WinEvent

Get-WinEvent reads the Application log of the local or a remote computer over RPC. Filter on the event ID and the SQL Server sources (MSSQL* covers the default and every named instance; the ProviderName key accepts wildcards) and parse the message with the same pattern as the error log line:

$computers = 'SQL01', 'SQL02', 'SQL03'
$since     = (Get-Date).AddDays(-1)
$pattern   = "^Login failed for user '(?<login>.*?)'\.(?:\s+Reason:\s+(?<reason>.*?))?\s*(?:\[CLIENT:\s*(?<client>[^\]]*)\])?\s*$"
# MSSQL* matches MSSQLSERVER (default instance) and MSSQL$NAME (named instances)
$filter    = @{ LogName = 'Application'; ProviderName = 'MSSQL*'; Id = 18456; StartTime = $since }

$rows = foreach ($computer in $computers) {
    try {
        $events = Get-WinEvent -ComputerName $computer -FilterHashtable $filter -ErrorAction Stop
    }
    catch {
        # "No events were found" is an error in Get-WinEvent; here it only means nothing matched.
        if ($_.FullyQualifiedErrorId -like 'NoMatchingEventsFound*') { continue }
        Write-Warning "${computer}: $($_.Exception.Message)"
        continue
    }
    foreach ($e in $events) {
        $hit = [regex]::Match([string]$e.Message, $pattern)
        [pscustomobject]@{
            Time     = $e.TimeCreated
            Computer = $computer
            Source   = $e.ProviderName          # MSSQLSERVER or MSSQL$<instance>
            Login    = $hit.Groups['login'].Value
            Client   = $hit.Groups['client'].Value
            Reason   = $hit.Groups['reason'].Value
        }
    }
}
$rows | Sort-Object Time -Descending | Format-Table Time, Computer, Source, Login, Client, Reason -AutoSize -Wrap

Reading another computer's log needs your account in that computer's Event Log Readers group (or Administrators) and the Remote Event Log Management firewall rules turned on. Our guide to searching Event Logs on multiple servers has the setup, the errors you will meet and a faster version with Invoke-Command. The loop takes the login, reason and client from the event's message text; for the state number, use the error log, where it has a line of its own.

Collect them from many instances with PowerShell

This script reads the current and the previous error log on every instance you list, pairs each Login failed line with its state, writes every failure to a CSV file and prints one line per instance, login, client and state with the number of failures and the first and last time. It uses Invoke-Sqlcmd from Microsoft's SqlServer module (Install-Module SqlServer once). The search runs on the server, so only matching lines travel back, and an instance that fails is reported at the end instead of stopping the run.

param(
    [string[]]$ServerInstance = @('SQL01', 'SQL02', 'SQL03\SALES'),
    [int[]]$LogNumber         = @(0, 1),               # 0 = current error log, 1 = the one before, ...
    [string]$CsvPath          = "$env:USERPROFILE\Desktop\FailedLogins.csv"
)
Import-Module SqlServer -ErrorAction Stop

# "Login failed for user 'x'. Reason: .... [CLIENT: 10.1.2.3]" (the Reason and CLIENT parts are not always there)
$pattern = "^Login failed for user '(?<login>.*?)'\.(?:\s+Reason:\s+(?<reason>.*?))?\s*(?:\[CLIENT:\s*(?<client>[^\]]*)\])?\s*$"
$found   = [System.Collections.Generic.List[object]]::new()
$failed  = [System.Collections.Generic.List[object]]::new()

foreach ($instance in $ServerInstance) {
    foreach ($n in $LogNumber) {
        try {
            # The message lines, and the "Error: 18456, Severity: 14, State: N." lines written just before them
            $messages = Invoke-Sqlcmd -ServerInstance $instance -Database master -QueryTimeout 300 -ErrorAction Stop `
                                      -Query "EXECUTE sys.sp_readerrorlog $n, 1, N'Login failed for user';"
            $states   = Invoke-Sqlcmd -ServerInstance $instance -Database master -QueryTimeout 300 -ErrorAction Stop `
                                      -Query "EXECUTE sys.sp_readerrorlog $n, 1, N'Error: 18456';"
        }
        catch {
            $failed.Add([pscustomobject]@{ Instance = $instance; Log = $n; Error = $_.Exception.Message })
            continue
        }

        # Pair each message with the state line that has the same time stamp (in order, when several share one)
        $stateAt = @{}
        foreach ($s in $states) {
            if ($s.Text -notmatch 'State:\s*(\d+)') { continue }
            $key = ([datetime]$s.LogDate).Ticks
            if (-not $stateAt.ContainsKey($key)) { $stateAt[$key] = [System.Collections.Generic.Queue[int]]::new() }
            $stateAt[$key].Enqueue([int]$Matches[1])
        }
        foreach ($m in $messages) {
            $hit = [regex]::Match([string]$m.Text, $pattern)
            if (-not $hit.Success) { continue }
            $key   = ([datetime]$m.LogDate).Ticks
            $state = if ($stateAt.ContainsKey($key) -and $stateAt[$key].Count -gt 0) { $stateAt[$key].Dequeue() } else { $null }
            $found.Add([pscustomobject]@{
                Time     = $m.LogDate
                Instance = $instance
                Login    = $hit.Groups['login'].Value
                State    = $state
                Client   = $hit.Groups['client'].Value
                Reason   = $hit.Groups['reason'].Value
            })
        }
    }
}

$found | Sort-Object Time -Descending | Export-Csv -Path $CsvPath -NoTypeInformation -Encoding UTF8

# One line per instance, login, client and state: how often, first and last time
$found | Group-Object Instance, Login, Client, State | Sort-Object Count -Descending | ForEach-Object {
    $times = $_.Group.Time | Sort-Object
    [pscustomobject]@{
        Count    = $_.Count
        Instance = $_.Group[0].Instance
        Login    = $_.Group[0].Login
        Client   = $_.Group[0].Client
        State    = $_.Group[0].State
        First    = $times[0]
        Last     = $times[-1]
        Reason   = $_.Group[0].Reason
    }
} | Format-Table -AutoSize -Wrap

"{0} failed logins from {1} instances written to {2}" -f $found.Count, $ServerInstance.Count, $CsvPath
foreach ($f in $failed) { Write-Warning "$($f.Instance), log $($f.Log): $($f.Error)" }

Save it as C:\Scripts\Find-FailedLogins.ps1 and run it with your own instance names:

C:\Scripts\Find-FailedLogins.ps1 -ServerInstance SQL01, SQL02, 'SQL03\SALES'

# The instance list from a file (one per line), and the last four error logs:
C:\Scripts\Find-FailedLogins.ps1 -ServerInstance (Get-Content C:\Scripts\instances.txt) -LogNumber 0, 1, 2, 3

Tested on Windows 11 with SQL Server 2025 LocalDB (17.0.4025.3): the xp_loginconfig, SERVERPROPERTY and sys.messages queries ran as shown (LocalDB reports failure, mixed mode). LocalDB cannot run sp_readerrorlog ("Failed to open loopback connection"), so its arguments come from Microsoft's documentation, and the script's parsing, state pairing, grouping and error handling were run against sample lines taken from Microsoft's page for error 18456 with Invoke-Sqlcmd replaced by a stub. The Get-WinEvent loop was run read-only on the same PC against its own Application log and an unreachable name; that PC has no SQL Server events, so no real 18456 event was parsed.

Reading the pattern

Once the failures are in one list, a few patterns explain most of them:

• One login, state 8, from several clients. Its password was changed and some application servers or jobs still use the old one. The client column tells you which.
• Many logins, states 5 and 8, from one client. Something is trying names and passwords: a vulnerability scanner you run yourself, or password guessing. sa with state 7 or 8 from outside your network deserves attention the same day.
• State 38 or 46 for a login that used to work. The login is fine; the database in its connection string was renamed, dropped, taken offline or restored without that user.
• State 5 or 8 right after a failover or a migration. The login is missing on the new server, or exists there with a different password. Availability groups do not copy logins; see sync SQL Server logins and Agent jobs across replicas and transfer SQL Server logins with their SID and password. SQLParity compares the logins of the servers you list, with their SIDs and password hashes, so a missing or different login shows up before the failover.
• State 58 for every SQL login. The instance is in Windows authentication mode. Either the application should use Windows authentication, or the instance needs mixed mode (a restart).
• State 11 or 12 for Windows users. The account is valid but has no access of its own: it was removed from the Windows group that had the login, or it only had access through local Administrators.

Common errors

• The script finds nothing, but users complain. Check that login auditing is not set to None, that you read the right log numbers (after a restart the failures are in log 1), and that the failures really reach this instance: an application pointed at a listener or alias may be failing on another server.
• "Caller does not have permissions to execute the stored procedure." The login cannot read the error log. Grant VIEW ANY ERROR LOG (2022 and later) or VIEW SERVER STATE.
• "There is not an event provider on the ... computer that matches "MSSQL*"." We got this from Get-WinEvent on a computer with no SQL Server installed. It is not the "No events were found" error, so the loop reports it: check the computer name.
• "The RPC server is unavailable." The computer could not be reached for a remote Event Log read: the name, DNS, the computer is off, or the Remote Event Log Management firewall rules are off.
• Lines for error 18452 instead. "Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication." is a different error with its own number. The script's pattern only matches "Login failed for user".

Where these methods stop

• Only what is still on disk. The error log keeps the current file and six older ones by default, and a few restarts push last week's failures out. The Application log overwrites its oldest events when it is full.
• A snapshot, not a history. Each run reads the servers again. To see whether failures for a login are new, keep the CSV files and compare them yourself.
• No alert. Nothing here tells you when a burst starts. You find out when you run the script.
• Permissions on every instance and computer. The error log needs a SQL Server permission on each instance, the Application log needs Event Log Readers and firewall rules on each computer.
• No host name or application name. The error log line has the client address only. For the application and host, use SQL Server Audit or an Extended Events session.

Questions

Related

• Read SQL Server error logs from multiple instances at once
• Search Windows Event Logs on multiple servers at once
• Sync SQL Server logins and Agent jobs across replicas
• LogTrawler: all your server logs in one searchable place
• All guides

Sources (Microsoft Learn): MSSQLSERVER_18456, Configure login auditing, Server Properties (Security page), xp_loginconfig, SERVERPROPERTY, sp_readerrorlog, View the Windows application log (event sources), SQL Server Audit action groups (FAILED_LOGIN_GROUP), Get-WinEvent, Get-WinEvent queries with FilterHashtable, Invoke-Sqlcmd.