How to Find Out Failed Login Attempts in SQL Server

SQL Server writes every failed login to its error log by default. The quickest way to see them is:

EXEC sys.sp_readerrorlog 0, 1, N'Login failed';

LogDate                  ProcessInfo  Text
-----------------------  -----------  ---------------------------------------------------------------
2026-10-08 09:14:02.310  Logon        Login failed for user 'app_user'. Reason: Password did not match
                                      that for the login provided. [CLIENT: 10.0.0.25]

Each entry gives you the time, the login name, the reason and the client address. That one command answers most "who is failing to log in?" questions. The rest of this page covers what to do when you need more: per-login counters, the exact cause, or a durable audit trail.

Which method to use

Method	Shows	History	Setup needed
Error log	Login, reason, client IP, time	Until the log files recycle	None
LOGINPROPERTY	Last bad-password time and consecutive count for a SQL login	Latest value only	None
SQL Server Audit	Login, client IP, application, time	As long as you keep the files	Create an audit
Extended Events	Same, plus anything else you capture	As long as you keep the files	Create a session

Read the error log

The four arguments of sp_readerrorlog are the log file number (0 is the current file, 1 the previous one, and so on), the log type (1 for SQL Server, 2 for SQL Server Agent), and up to two search strings that must both match.

To see who is failing most often, load the output into a temporary table and count:

CREATE TABLE #log (
    LogDate     datetime,
    ProcessInfo nvarchar(50),
    LogText     nvarchar(max)
);

INSERT INTO #log
EXEC sys.sp_readerrorlog 0, 1, N'Login failed';

SELECT LogText,
       COUNT(*)     AS Attempts,
       MIN(LogDate) AS FirstSeen,
       MAX(LogDate) AS LastSeen
FROM #log
GROUP BY LogText
ORDER BY Attempts DESC;

DROP TABLE #log;

Because the text includes the login, the reason and the client address, each row of the result is one login failing from one machine for one reason.

A new error log file starts every time the instance restarts, and SQL Server keeps six old files by default. Repeat the call with 1, 2, 3 and so on as the first argument to look further back. On a server that restarts rarely the current file can be very large and slow to search. Many DBAs schedule EXEC sp_cycle_errorlog; weekly and raise the number of retained files in SSMS under Management > SQL Server Logs > Configure.

Permissions: SQL Server 2022 and later require the VIEW ANY ERROR LOG or VIEW SERVER PERFORMANCE STATE server permission. Earlier versions require VIEW SERVER STATE.

If nothing shows up

Check the login auditing level. In SSMS, right-click the server, choose Properties > Security, and look at Login auditing. The options are None, Failed logins only (the default), Successful logins only, and Both. If someone set it to None or to successful logins only, failures are not logged. A change takes effect after the instance restarts.

Avoid "Both" on a busy server unless you need it. Every connection from every pooled application then writes a line, and the failures get buried.

Find the exact reason: error 18456 and its state

Each failure is logged as two lines. The first carries the error number and a state code:

Logon   Error: 18456, Severity: 14, State: 8.
Logon   Login failed for user 'app_user'. Reason: Password did not match that for the login provided. [CLIENT: 10.0.0.25]

The client is never told the real reason. It only sees "Login failed for user 'app_user'" with state 1, so that an attacker cannot learn whether a login name exists. The state in the server's error log is where the diagnosis is:

State	Meaning
2, 5	The login name does not exist on this server
6	A Windows login name was used with SQL Server authentication
7	The login is disabled, and the password was wrong as well
8	The password is wrong
9	The password is not valid
11, 12	The login is valid but has no access to the server. Often a Windows account that only has access through an administrators group and is not running elevated
18	The password must be changed before logging in
38, 46	The database requested in the connection string does not exist or cannot be opened by this login
58	SQL Server authentication was attempted, but the server accepts Windows authentication only

States 38 and 58 account for many "the password is definitely right" cases: the first is a wrong or dropped default database, and the second means the server was never switched to mixed mode.

Check SQL logins with LOGINPROPERTY

LOGINPROPERTY returns password-policy information for a SQL Server login, including the time of the last bad password and the number of consecutive bad attempts:

SELECT name,
       CAST(LOGINPROPERTY(name, 'BadPasswordCount') AS int)      AS BadPasswordCount,
       CAST(LOGINPROPERTY(name, 'BadPasswordTime')  AS datetime) AS BadPasswordTime,
       CAST(LOGINPROPERTY(name, 'IsLocked')         AS int)      AS IsLocked,
       CAST(LOGINPROPERTY(name, 'LockoutTime')      AS datetime) AS LockoutTime
FROM sys.sql_logins
WHERE is_disabled = 0
ORDER BY BadPasswordTime DESC;

This is handy for a quick look across all logins, but know its limits:

• It covers SQL Server logins only. For Windows and Microsoft Entra logins the function returns NULL, because their passwords are checked elsewhere.
• It reports only the most recent bad attempt, with no client address.
• BadPasswordCount counts consecutive failures, so a successful login resets it.
• Attempts against login names that do not exist are not recorded anywhere here.
• Microsoft's documentation says these counters are maintained only when password policy is enforced for the login (CHECK_POLICY = ON). For logins created with CHECK_POLICY = OFF, do not rely on them.
• A BadPasswordTime of 1900-01-01 means no bad attempt has been recorded.

You need VIEW permission on a login to read its properties. Without wider permissions, sys.sql_logins shows only your own login and sa.

If IsLocked is 1, the Windows account lockout policy has locked the login. Unlock it by resetting the password:

ALTER LOGIN [app_user] WITH PASSWORD = N'<new strong password>' UNLOCK;

Keep a durable record with SQL Server Audit

The error log is a troubleshooting tool. For security monitoring, use an audit, which writes to its own files and is available in every edition:

USE master;

CREATE SERVER AUDIT FailedLoginAudit
TO FILE (FILEPATH = N'D:\SqlAudit\', MAXSIZE = 100 MB, MAX_ROLLOVER_FILES = 20)
WITH (ON_FAILURE = CONTINUE);

ALTER SERVER AUDIT FailedLoginAudit WITH (STATE = ON);

CREATE SERVER AUDIT SPECIFICATION FailedLoginSpec
FOR SERVER AUDIT FailedLoginAudit
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON);

The folder must already exist, and the SQL Server service account needs write access to it. Read the results with:

SELECT event_time,              -- UTC
       server_principal_name,
       client_ip,
       application_name,
       statement                -- the failure reason
FROM sys.fn_get_audit_file(N'D:\SqlAudit\FailedLoginAudit*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'LGIF'
ORDER BY event_time DESC;

The client_ip and application_name columns are present in SQL Server 2017 and later. Creating an audit requires the ALTER ANY SERVER AUDIT permission. Reading the files requires CONTROL SERVER, or on SQL Server 2022 and later the narrower VIEW SERVER SECURITY AUDIT permission. To remove the audit, switch each object off before dropping it:

ALTER SERVER AUDIT SPECIFICATION FailedLoginSpec WITH (STATE = OFF);
DROP SERVER AUDIT SPECIFICATION FailedLoginSpec;
ALTER SERVER AUDIT FailedLoginAudit WITH (STATE = OFF);
DROP SERVER AUDIT FailedLoginAudit;

Capture failures with Extended Events

If you want the client host name and application alongside the error, or you already collect other events, an Extended Events session filtered on error 18456 works well:

CREATE EVENT SESSION FailedLogins ON SERVER
ADD EVENT sqlserver.error_reported (
    ACTION (sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.username)
    WHERE error_number = 18456
)
ADD TARGET package0.event_file (SET filename = N'FailedLogins.xel', max_file_size = 50, max_rollover_files = 5)
WITH (STARTUP_STATE = ON);

ALTER EVENT SESSION FailedLogins ON SERVER STATE = START;

Without a path, the .xel files go to the instance's log directory. Open them in SSMS under Management > Extended Events > Sessions, or query them with sys.fn_xe_file_target_read_file. Remove the session with DROP EVENT SESSION FailedLogins ON SERVER;.

Common situations

Thousands of failures for sa or admin from internet addresses. The instance is reachable from the internet and is being scanned. Close the port at the firewall. Disabling or renaming sa and using Windows or Entra authentication reduces the risk, but an exposed port is the actual problem.

One application login failing every few seconds from one internal address. Somewhere a service or scheduled job still has an old password. The [CLIENT: ...] address tells you which machine to look at.

Failures with an empty user name or NT AUTHORITY\ANONYMOUS LOGON. Kerberos delegation is failing between a middle tier and SQL Server. That is a service principal name problem, not a password problem.

Azure SQL Database. There is no error log to read and sp_readerrorlog is not available. Enable auditing on the server or database and query the audit destination for failed authentication events. Azure SQL Managed Instance and SQL Server on Linux both have an error log and support the methods above.

Further reading

• MSSQLSERVER_18456: Login failed for user
• LOGINPROPERTY (Transact-SQL)
• SQL Server Audit (Database Engine)
• sp_readerrorlog (Transact-SQL)