Sql-server – SQL Server – Why am I getting TCP connections without sessions

sql serversql-server-2019ssl

I enabled SSL with Forced Encryption enabled, using a self-signed certificate.

Then I've set up automated monitoring to detect not secured connections (seems redundant, I know, but bear with me here), using the following T-SQL query:

SELECT CONCAT('Not secured connection(s) detected of '
, ISNULL(QUOTENAME(COALESCE(ses.original_login_name, ses.nt_user_name, ses.login_name)), 'an unknown login')
, ' from ', ISNULL(QUOTENAME(client_net_address), 'an unknown address')
, ' ', QUOTENAME(ISNULL(ses.host_name, 'unknown host'), '(')
, ', ', ISNULL(QUOTENAME(ses.program_name), 'unknown program')
, ', to ', ISNULL(QUOTENAME(DB_NAME(ses.database_id)), 'an unknown database')
, ', Session(s): ', COUNT(ses.session_id)
), COUNT(con.connection_id) AS NumberOfConnections
FROM sys.dm_exec_connections AS con
LEFT JOIN sys.dm_exec_sessions AS ses
ON ses.session_id IN (con.session_id, con.most_recent_session_id)
WHERE encrypt_option = 'FALSE'
AND net_transport = 'TCP'
AND client_net_address NOT LIKE '<%'
GROUP BY COALESCE(ses.original_login_name, ses.nt_user_name, ses.login_name)
, client_net_address, ses.host_name, ses.program_name, ses.database_id

Later, I started getting alerts resulting from this query, with output that looks like this:

Not secured connection(s) detected of an unknown login from [x.x.x.x] (unknown host), unknown program, to an unknown database: 0

(the x.x.x.x replaces actual IP addresses that came from one of our customer's networks)

This means that we're detecting TCP connections (in sys.dm_exec_connections) without corresponding sessions (in sys.dm_exec_sessions).

Based on what I found in Microsoft documentation, these scenarios could happen as part of Availability Group, Database Mirroring, or Service Broker architectures… But we're not using any of those in our instance!

This should not be happening… Does anyone have any idea how to explain this? Could this happen due to some kind of port scanner perhaps?

Thanks!

Version details:

Microsoft SQL Server 2019 [Enterprise] (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64)
    Mar 14 2020 16:10:35 
    Copyright (C) 2019 Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Best Answer

I'll assume you've restarted SQL Server after enabling the force encryption option.

In addition to the reasons you mentioned in your question, you may see a connection without an associated session if your run the query while a client is in the process of connecting. The encrypt_option will show FALSE immediately after the physical connection is established but change to TRUE after the client sends a TDS PRELOGIN message as part of the handshake process. The session will be created once the login process completes.

You can filter out in-progress login noise in your monitoring query either by using an inner join instead of outer join to sys.dm_exec_sessions or by adding AND con.protocol_version > 0.

Related Question