Auditing Failed Logins With the Error Log and Extended Events

The password alert keeps returning even though nobody is at the keyboard. Repeated failed logins can come from an old application secret or an unauthorized attempt. Capture the reason and source before deciding which problem you need to solve.

Start With the Server's Explanation of Failed Logins

Error 18456 says authentication or connection authorization failed. The client message deliberately gives limited detail. The server error log contains a reason and a state that narrow the investigation. Confirm that failed login auditing is enabled for the instance. Without the expected audit setting, a missing entry cannot establish that nobody tried to connect.

I read the server reason before asking someone to reset a password. A login can have a correct password and an inaccessible requested database. Resetting it adds work without repairing the actual failure. Which database, instance, and authentication method does the application request? Save those details with the error, and keep actual secrets out of the investigation notes.

Read the Current and Retained Logs

The first command filters the current SQL Server log for login failures. The second searches a retained archive. Zero selects the current log, and one selects the first archive. The second parameter selects the Database Engine log rather than the Agent log. Review the matching timestamp's surrounding lines to find the state and reason together.

EXEC master.dbo.xp_readerrorlog 0,1,N'Login failed';
EXEC master.dbo.xp_readerrorlog 1,1,N'Login failed';
EXEC master.dbo.xp_readerrorlog 0,1,N'Error: 18456';

Rotation and retention determine how much evidence remains. A restart can place yesterday's entries in an archive. Search the relevant archives, rather than concluding that the current file covers your whole incident. Error log timestamps and Extended Events UTC timestamps also need a consistent time basis when you correlate them. Record the server time zone before merging the two sources.

Use States as a Starting Map

States two and five point toward an invalid login name. State eight indicates a password mismatch. State seven covers a disabled login and password related failure. States eleven and twelve involve server access validation. State eighteen requires a password change. State thirty eight concerns access to the explicitly requested database. Read the accompanying reason rather than relying on a memorized number alone.

The Microsoft Learn page named MSSQLSERVER_18456 documents the broader set of reasons. Contained users, Windows tokens, database availability, and authentication mode add cases beyond this short map. A client reporting state one gives you little diagnostic value. Use the server evidence and identify the intended identity. I also check whether a deployment changed the database name before touching authentication settings.

Capture Failed Logins in a Focused Event File

Use an authorized administrator to create a small server event session. Prepare C:\SqlXE on the server and grant the SQL Server service account appropriate write access. This example records only error 18456 and limits rollover files. Host and application names are supplied by the client and can be empty or misleading. Treat them as clues, not authenticated identity.

CREATE EVENT SESSION FailedLogins ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION(sqlserver.client_hostname,sqlserver.client_app_name,
           sqlserver.username,sqlserver.session_id)
    WHERE(error_number=18456)
)
ADD TARGET package0.event_file
    (SET filename=N'C:\SqlXE\FailedLogins.xel',
         max_file_size=(20),max_rollover_files=(2))
WITH(MAX_DISPATCH_LATENCY=5 SECONDS,STARTUP_STATE=OFF);
ALTER EVENT SESSION FailedLogins ON SERVER STATE=START;

This is a diagnostic collection, not an immutable security audit. Bounded files overwrite older evidence, and ordinary event retention can lose events under pressure. Choose SQL Server Audit and protected collection when policy requires auditable completeness. Set ownership and a removal date for a temporary session. Test a deliberate failed connection from an approved test client before relying on the fields.

Read Events Without Inventing a Login Identity

Run this on the instance that can access the files. The reader returns event XML plus UTC timestamps and file positions. Store those positions when building a collector to avoid recounting events on later reads. The username action captures session context. During a failed authentication it is not guaranteed to contain the attempted login, so retain the event message too.

SELECT f.timestamp_utc AS EventUtc,f.file_name,f.file_offset,
       x.e.value('(event/data[@name="state"]/value)[1]','int') AS ErrorState,
       x.e.value('(event/data[@name="message"]/value)[1]','nvarchar(4000)') AS ErrorMessage,
       x.e.value('(event/action[@name="username"]/value)[1]','nvarchar(256)') AS ContextUser,
       x.e.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(256)') AS ClientHost,
       x.e.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(256)') AS ClientApp
INTO #LoginEvents
FROM sys.fn_xe_file_target_read_file
    (N'C:\SqlXE\FailedLogins*.xel',NULL,NULL,NULL) AS f
CROSS APPLY(VALUES(CONVERT(xml,f.event_data))) AS x(e)
WHERE f.object_name=N'error_reported';
SELECT EventUtc,ErrorState,ErrorMessage,ContextUser,ClientHost,ClientApp
FROM #LoginEvents ORDER BY EventUtc;

Restrict access to the files and extracted records. Login names and source details are operational security information. Current monitoring permissions differ across SQL Server releases, so validate the collector identity's access explicitly. Capture the test error and compare the extracted fields against the raw XML. A field full of empty strings deserves explanation before a dashboard labels it unknown attackers.

Group Failed Logins by Name and Source

For English error messages, the first quoted value normally contains the attempted login. This illustrative parser extracts that value for grouping. It is unsuitable as an authoritative parser for localized messages or login names containing quotes. Keep the raw message, and adapt the extraction to the exact server output before building a permanent report. In my test, one failed attempt raised two events: one with the real state and one with state 1, the generic copy sent to the client. The query skips state 1 so each attempt counts once.

WITH positions AS
(
    SELECT *,CHARINDEX(N'''',ErrorMessage) AS QuoteStart
    FROM #LoginEvents
), names AS
(
    SELECT *,CHARINDEX(N'''',ErrorMessage,QuoteStart+1) AS QuoteEnd
    FROM positions
), parsed AS
(
    SELECT EventUtc,ClientHost,ClientApp,ErrorState,
           CASE WHEN QuoteStart>0 AND QuoteEnd>QuoteStart
                THEN SUBSTRING(ErrorMessage,QuoteStart+1,QuoteEnd-QuoteStart-1)
                ELSE N'(inspect message)' END AS AttemptedLogin
    FROM names
    WHERE ErrorState<>1
)
SELECT AttemptedLogin,ClientHost,ClientApp,ErrorState,COUNT_BIG(*) AS Attempts,
       MIN(EventUtc) AS FirstSeenUtc,MAX(EventUtc) AS LastSeenUtc
FROM parsed
GROUP BY AttemptedLogin,ClientHost,ClientApp,ErrorState
ORDER BY Attempts DESC;

A steady interval from one known application suggests a retry loop or scheduled task. Varied login names and unexpected sources require a security investigation. Neither pattern proves motive. Correlate network records, deployment changes, service configuration, and the application owner. A source label claiming AccountingApp does not come with a notarized certificate of good intentions.

Repair the Cause With the Responsible Owner

For a known application, inspect its active configuration and secret rotation path. Remove retired credentials from services, jobs, and connection pools. Check the requested database and login mapping. Validate a successful connection from the actual application host after the fix. A successful SSMS connection from your workstation tests a different path and identity.

For unexpected sources, follow your incident response process. Restrict exposed access where authorized, preserve retained evidence, and review successful connections around the same time. Do not enable mixed authentication or grant broader rights merely to clear an error. An alert disappearing after granting sysadmin is an especially expensive way to make a chart green.

Verify Quiet for the Right Reason

Collect another agreed observation window after the repair. Compare attempt patterns, not just the latest entry. Check that the application remains functional and the monitoring session remains active. A stopped collector also produces silence. I verify both the positive application test and the absence of the repeated failure before closing an investigation.

Stop a temporary session when its job is finished, then retain the files according to policy. The command leaves the definition available for a reviewed restart. Failed logins are actionable when their evidence leads to a specific owner and cause. Keep the investigation focused enough that a routine configuration mistake and a suspicious source each receive the response they require.

ALTER EVENT SESSION FailedLogins ON SERVER STATE=STOP;

Related reading on this blog: T-SQL Script: How to Search for Multiple Values in ERRORLOG? and FIX Error 18456, Severity: 14, State: 5. Login failed for user.

A failed login is not a diagnosis, it is evidence that needs a source and a reason.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.