In most cases information about failed logins can be found in the errorlog of SQL Server. However, if your SQL Server is working with TLS then if the login fails with a handshake error, you will not find this failed login attempt in the errorlog of SQL Server. You can find these types of login errors in the system_health extended event session.
The connectivity_ring_buffer_recorded extended event is part of the system_health session and it records login information before it gets to the errorlog so it might be a good idea to filter for these events:
DBCC TRACEON (2592, 3656, -1);
select object_name
, CONVERT(XML, event_data) as event_data
, CONVERT(XML, event_data).query('event/data[@name="os_error"]').value(N'(/data/value)[1]',N'nvarchar(max)') as OS_error
, CONVERT(datetime, timestamp_utc) timestamp_utc
FROM sys.fn_xe_file_target_read_file(N'System_health*.xel', NULL, NULL, NULL)
where object_name = 'connectivity_ring_buffer_recorded'
and convert(datetime, timestamp_utc) > GETDATE()-1
DBCC TRACEOFF (2592, 3656, -1);
As I was particularly interested in the OS Error part of the event, I extracted the information from the event_data field of the extended event. The os_error field contains the errorcode from Windows. I got the list of OS Errors from here
Most of the OS error codes are 10054 which means: “An existing connection was forcibly closed by the remote host.\r\n”
So this is pretty normal, we can safely ignore these error codes.
Another error code I saw was 2148074289. This one translates to:
“The client and server cannot communicate, because they do not possess a common algorithm.\r\n”
Now this is something serious. This means that a connection was not successful as the client and server could not agree on a common cipher.
DBCC TRACEON (2592, 3656, -1);
select object_name
, CONVERT(XML, event_data) as event_data
, CONVERT(XML,
event_data).query('event/data[@name="os_error"]').value(N'(/data/value)[1]',N'nvarchar(max)') as OS_error
, CONVERT(datetime, timestamp_utc) timestamp_utc
FROM sys.fn_xe_file_target_read_file(N'System_health*.xel', NULL, NULL, NULL)
where object_name = 'connectivity_ring_buffer_recorded'
and CONVERT(XML,
event_data).query('event/data[@name="os_error"]').value(N'(/data/value)[1]',N'nvarchar(max)') = '2148074289'
DBCC TRACEOFF (2592, 3656, -1);
When opening the xml event_data in SQL Server Management Studio we can see the following information from the connection:
Because I used the Trace Flags 2592 and 3656 (for SQL Server 2019) in my query, the callstacks were translated to the names of the functions used for the login process. Now we can see that the issue really happened at the TLS handshake.
Unfortunately we do not get much information out of this extended event. The remote_host field contains the IP address of the client but as you can see the last part of the IP address is missing. The PID of the client process and the username is also missing.
The above SQLs can be used to look for login errors in SQL Servers which do not appear in the error logs.