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
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
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?
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)