Systems & Databases

Balazs Berki

  • Home
  • Downloads
  • About

SQL Server Failed logins: TLS Handshake issues

20th January, 2020 · Balazs Berki

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.

Posted in connectivity_ring_buffer_recorded, extended events, SQL Server, TLS Handshake |
« Scheduled tracing with procmon

Leave a comment Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • SQL Server Failed logins: TLS Handshake issues
  • Scheduled tracing with procmon
  • Login timeouts in SQL Server due to delayed SSPI handshakes
  • A lightweight alternative to Query Store
  • Issues with monitoring open transactions: Schema lock

Archives

  • January 2020
  • May 2019
  • March 2019
  • November 2018
  • January 2018
  • April 2017
  • February 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016

Categories

  • ALTER TABLE MOVE
  • Avaloq
  • connectivity_ring_buffer_recorded
  • CPU
  • DATA_COMPRESSION
  • Database Crash
  • dba_users
  • emocmrsp
  • expiry_date
  • extended events
  • Full Table Scan
  • Kerberos
  • Kerberos Keytab
  • Kerberos Ticket
  • ktutil
  • LCK_M_SCH_S
  • logman
  • LogMiner
  • modelcache
  • open transaction
  • ORA-17627
  • ORA-17629
  • ORA-56904
  • Oracle
  • Oracle Client
  • Oracle client Install Script
  • Oracle Database In-Memory
  • Oracle monitoring
  • Oracle Single sign-on
  • Oracle WaitEvents
  • PerfMon
  • Pivot
  • Powershell
  • procmon
  • Python
  • Query Store
  • READ_COMMITTED_SNAPSHOT
  • Scheduled Task
  • Schema Lock
  • SQL Server
  • SSMS
  • TLS Handshake
  • wait_info_external
  • Windows Event Log

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org
© balazsberki.com
  • Downloads
  • About