Finding Unencrypted SQL Server Connections, and the Instances That Accept Them
An application team upgrades its data access driver, and the first deployment fails with “The certificate chain was issued by an authority that is not trusted.” Someone adds Encrypt=False to the connection string, the application connects, and the ticket closes. Nothing in that sequence tells the DBA team that the application’s traffic to SQL Server now crosses the network without encryption, or that the instance accepts unencrypted connections at all.
SQL Server records the encryption state of every connection, and the instance setting that decides whether unencrypted connections are allowed lives in the registry. I tested both on two local instances with three client drivers, and wrote a query to find the unencrypted sessions on any instance.
Where SQL Server Records It
sys.dm_exec_connections returns one row per connection. encrypt_option says whether encryption is enabled for the connection, and net_transport is the physical transport. auth_scheme is the authentication scheme. protocol_type and protocol_version describe the payload protocol.[1] This is the query I ran from inside each test connection:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE [master]; SELECT [c].[net_transport] , [c].[protocol_type] , CONVERT(varchar(10), CONVERT(varbinary(4), [c].[protocol_version]), 1) AS [protocol_version_hex] , [c].[encrypt_option] , [c].[auth_scheme] , [c].[client_net_address] FROM [sys].[dm_exec_connections] AS [c] WHERE [c].[session_id] = @@SPID; |
protocol_version is an integer, and it’s easier to read in hex. Every TCP connection in my tests reported 0x74000004. The TDS specification lists that value for SQL Server 2012 through SQL Server 2025, and for 2022 and 2025 only in the TDS 7.x flow.[2] auth_scheme was NTLM for every Windows-authenticated test connection, including the ones over TCP. All of them came from the same machine as the server, so I didn’t see what a remote client would negotiate.
Shared Memory Isn’t TCP
My first check came back FALSE, from a sqlcmd -S .\SQL2019 -E session. That session used the shared memory transport, and client_net_address showed
Shared memory connections can still be encrypted. With ODBC Driver 17 and Encrypt=yes;TrustServerCertificate=yes over the lpc: prefix, encrypt_option was TRUE on both instances, and net_transport still said Shared memory. Without Encrypt, it was FALSE. A FALSE on a shared memory row isn’t traffic on the wire, so I leave those rows out of the fleet query below.
The Test
I used two instances on one Windows 11 workstation: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer). Neither instance has a certificate configured, so each uses the self-signed certificate that SQL Server generates at startup.[4] Both ERRORLOGs show it at the last service start: “A self-generated certificate was successfully loaded for encryption.” Neither has Force Encryption turned on.
Each test opened a TCP connection to 127.0.0.1 on the instance’s port, with Windows authentication, and ran the query above. The clients were ODBC Driver 17 (17.11.1.1) and ODBC Driver 18 (18.7.1.1) through System.Data.Odbc in PowerShell 7.6. I also used System.Data.SqlClient 4.9.1, the copy that ships with PowerShell 7.6, and Microsoft.Data.SqlClient 6.1.4 in a small .NET 8 console program. The ODBC version of the probe looks like this:
|
1 2 3 4 5 6 7 8 |
$cs = 'Driver={ODBC Driver 18 for SQL Server};Server=tcp:127.0.0.1,1433;' + 'Trusted_Connection=Yes;Encrypt=no;APP=sat_enc_probe;' $conn = [System.Data.Odbc.OdbcConnection]::new($cs) $conn.Open() $cmd = $conn.CreateCommand() $cmd.CommandText = 'SELECT [encrypt_option] FROM [sys].[dm_exec_connections] WHERE [session_id] = @@SPID;' $cmd.ExecuteScalar() $conn.Dispose() |
The results were the same on both instances:
| Client | Connection string setting | Result | encrypt_option |
|---|---|---|---|
| ODBC Driver 17 | Encrypt not set | connected | FALSE |
| ODBC Driver 17 | Encrypt=yes, TrustServerCertificate=yes | connected | TRUE |
| ODBC Driver 18 | Encrypt not set | certificate chain not trusted | (no connection) |
| ODBC Driver 18 | Encrypt not set, TrustServerCertificate=yes | connected | TRUE |
| ODBC Driver 18 | Encrypt=no | connected | FALSE |
| ODBC Driver 18 | Encrypt=strict, with or without TrustServerCertificate | closed by the remote host | (no connection) |
| System.Data.SqlClient 4.9.1 | Encrypt not set | connected | FALSE |
| System.Data.SqlClient 4.9.1 | Encrypt=True, TrustServerCertificate=True | connected | TRUE |
| Microsoft.Data.SqlClient 6.1.4 | Encrypt not set | certificate chain not trusted | (no connection) |
| Microsoft.Data.SqlClient 6.1.4 | Encrypt not set, TrustServerCertificate=True | connected | TRUE |
| Microsoft.Data.SqlClient 6.1.4 | Encrypt=False | connected | FALSE |
| Microsoft.Data.SqlClient 6.1.4 | Encrypt=Strict | closed by the remote host | (no connection) |
With Force Encryption off, the client decides. The older drivers don’t encrypt unless they’re asked to. The newer drivers encrypt by default, and they fail when they can’t validate the server’s certificate.
Microsoft’s docs say the login packet, which carries the credentials, is always encrypted. The data packets after the login are what FALSE leaves unencrypted.[4] The TDS spec has an exception for clients that say they can’t encrypt at all. If the client reports that encryption isn’t available and the server isn’t set to require it, the spec’s table says nothing is encrypted, including the login.[5] I didn’t capture any packets, so I haven’t checked which case my test connections fell into.
The Driver Defaults Changed
The failures in rows three and nine come from a change in defaults. Microsoft.Data.SqlClient 4.0 changed Encrypt to default to true.[6] ODBC Driver 18.0 made the same change, listed in its release notes as a breaking change (“Default Encrypt to Yes/Mandatory”). The same release changed certificate validation, so the driver validates whenever encryption is negotiated, from either side.[7] An empty SqlConnectionStringBuilder in my console program reported Encrypt as True.
That leaves the application team from the opening with three choices. Encrypt=False brings back the old behaviour, and the session shows up as FALSE. TrustServerCertificate=True keeps encryption but skips certificate validation, which Microsoft describes as not recommended.[8] Without validation, the client can’t tell the real server from a man in the middle, and the same page gives that attack as the reason strict encryption doesn’t allow the setting.[8] The third choice is a certificate on the server that the client can validate, and it’s the only one that needs a change on the server. Connections made with the first choice are the ones the fleet query below lists.
Strict Encryption and TDS 8.0
Encrypt=Strict failed on both instances, and each instance logged a different reason. TDS 8.0, the protocol version that strict encryption uses, applies to SQL Server 2022 and later, so the 2019 instance doesn’t support it.[8] The 2025 instance does, but strict encryption needs a TLS certificate installed on the server.[9] My instances have only the self-signed fallback certificate. xp_readerrorlog shows these entries at 12:09, the time of the strict attempts, one per attempt:
|
1 2 3 |
SQL2019: Error: 17832, Severity: 20, State: 2. SQL2019: The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 127.0.0.1] SQL2025: A valid TLS certificate is not configured to accept strict (TDS 8.0 and above) connections. The connection has been closed. |
In both cases the server closed the connection, and the client reported “An existing connection was forcibly closed by the remote host.” The server closed it before the client validated any certificate, so adding TrustServerCertificate changed nothing. It wouldn’t help with a certificate installed either, because under strict the client can’t use TrustServerCertificate to bypass validation.[8] I didn’t install a certificate, so I haven’t seen a strict connection succeed here, and I can’t say what protocol_version reports for one.
With TDS 8.0 the TLS handshake comes before any TDS message, including the prelogin. With TDS 7.x the prelogin is cleartext, and data after the login may or may not be encrypted.[8] Starting with SQL Server 2022, there’s also a Force Strict Encryption option on the Flags tab of the TCP/IP properties in SQL Server Configuration Manager.[9]
Which Instances Accept Unencrypted Connections
The Force Encryption setting is on the Flags tab of the protocol properties in SQL Server Configuration Manager, and changing it requires a service restart.[4] If it’s set to Yes, all data sent across the network between SQL Server and the client is encrypted.[4] I wanted to read the setting with T-SQL and leave it alone.
sys.dm_server_registry returns network configuration values for the instance.[10] On both of my builds it returned 107 rows, including the Sm, Np and Tcp subkeys under SuperSocketNetLib. It didn’t return the SuperSocketNetLib key itself, which is where ForceEncryption and Certificate are stored. A filter on value_name LIKE N'%Encrypt%' returned no rows on either instance.
The undocumented extended procedure xp_instance_regread can read the value. It maps the generic MSSQLServer path to the instance’s own key. Because it’s undocumented, its behaviour and permissions can change without notice. I ran it as sysadmin:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
USE [master]; DECLARE @force_encryption int; DECLARE @force_strict int; DECLARE @certificate nvarchar(128); EXEC [master].[sys].[xp_instance_regread] N'HKEY_LOCAL_MACHINE' , N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib' , N'ForceEncryption' , @force_encryption OUTPUT; EXEC [master].[sys].[xp_instance_regread] N'HKEY_LOCAL_MACHINE' , N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib' , N'ForceStrict' , @force_strict OUTPUT; EXEC [master].[sys].[xp_instance_regread] N'HKEY_LOCAL_MACHINE' , N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib' , N'Certificate' , @certificate OUTPUT; SELECT @@SERVERNAME AS [server_name] , CONVERT(nvarchar(128), SERVERPROPERTY('ProductVersion')) AS [product_version] , @force_encryption AS [force_encryption] , @force_strict AS [force_strict] , COALESCE(NULLIF(@certificate, N''), N'(none configured)') AS [certificate_thumbprint]; |
|
1 2 3 4 |
server_name product_version force_encryption force_strict certificate_thumbprint ---------------- --------------- ---------------- ------------ ---------------------- SQLDEV01\SQL2019 15.0.4500.2 0 NULL (none configured) SQLDEV01\SQL2025 17.0.5015.4 0 0 (none configured) |
On the 2019 instance the ForceStrict value doesn’t exist. The procedure printed “RegQueryValueEx() returned error 2” and left the variable NULL. Reading the same keys directly with Get-ItemProperty on the workstation gave the same values. The registry value is what the next service start will use. If someone changed it without a restart, it may not match what the running instance is doing.
sys.dm_exec_connections also answers the question, without the registry. If any TCP row shows encrypt_option = FALSE, the instance accepted an unencrypted connection. If no rows come back, the instance may still accept them, because no current session has asked for one.
A Query for the Fleet
This lists every unencrypted session that isn’t on shared memory, with the login, host and program. On my instances that meant TCP. Named pipes was disabled on both, so I didn’t test a named pipes connection. The query needs VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.[1] It’s a single SELECT, so it can go to a Central Management Server group or any tool that sends one query to many instances. I didn’t try it through a Central Management Server. I ran it only on my two local instances:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
USE [master]; SELECT @@SERVERNAME AS [server_name] , [s].[session_id] , [s].[login_name] , [s].[host_name] , [s].[program_name] , [c].[client_net_address] , [c].[net_transport] , [c].[auth_scheme] , [c].[encrypt_option] , CONVERT(varchar(10), CONVERT(varbinary(4), [c].[protocol_version]), 1) AS [protocol_version_hex] , CONVERT(varchar(19), [c].[connect_time], 120) AS [connect_time] FROM [sys].[dm_exec_connections] AS [c] INNER JOIN [sys].[dm_exec_sessions] AS [s] ON [s].[session_id] = [c].[session_id] WHERE [c].[encrypt_option] = N'FALSE' AND [c].[net_transport] <> N'Shared memory' AND [c].[parent_connection_id] IS NULL ORDER BY [s].[host_name] , [s].[program_name] , [s].[login_name]; |
The parent_connection_id filter drops the extra rows that Multiple Active Result Sets (MARS) adds for a single logical session.[1] With one unencrypted ODBC Driver 17 connection and one encrypted ODBC Driver 18 connection held open, each instance returned one row. This is the 2019 row, with names changed:
|
1 2 3 |
server_name session_id login_name host_name program_name client_net_address net_transport auth_scheme encrypt_option protocol_version_hex connect_time ---------------- ---------- -------------- --------- ------------------------- ------------------ ------------- ----------- -------------- -------------------- ------------------- SQLDEV01\SQL2019 74 CONTOSO\hannah SQLDEV01 sat_enc_probe_unencrypted 127.0.0.1 TCP NTLM FALSE 0x74000004 2026-10-30 12:10:12 |
host_name and program_name are values the client sends. My probes set program_name to whatever I put in APP=, so treat both as hints. client_net_address is the address of the connection itself. A loopback address such as 127.0.0.1 is TCP traffic that stays on the server, so you may want to filter it out as well. The DMV shows sessions connected right now. An application that connects once a night won’t appear in an afternoon check, so run it on a schedule and keep the results if you need a full list.
Limits of This Test
- I tested one build of each version, on one workstation, with every connection coming from the same machine. None of the traffic crossed a physical network.
- Neither instance had Force Encryption on, and I didn’t turn it on, because it needs a restart. I didn’t test what each driver does against a forced instance.
- Neither instance had a CA-issued certificate, so I didn’t see a successful strict connection or a validated one.
- I didn’t test Linux, Azure SQL Database or Managed Instance, the OLE DB driver, JDBC, or named pipes.
The certificate side of this is covered in Inspecting SQL Server TLS Certificates Without Credentials, which reads the certificate an instance presents without logging in. If you’re about to replace the self-signed fallback, Can You Delete That Certificate? The Store Will Not Tell You. shows how to see which certificates are in use before you remove one.
Related: Auditing Linked Server Security: Who Connects as Whom.
If you’ve run a query like this across a fleet and found something you didn’t expect, I’d like to hear about it. Find me on Bluesky or LinkedIn.
References
- sys.dm_exec_connections (Transact-SQL) - Microsoft Learn. Column definitions for encrypt_option, net_transport, auth_scheme, protocol_type, protocol_version and parent_connection_id, and the permissions needed on SQL Server 2022 and later and on earlier versions. ↩
- [MS-TDS]: Appendix A: Product Behavior - Microsoft Open Specifications. The TDS version table lists 0x74000004 for SQL Server 2012 through SQL Server 2025, with 2022 and 2025 noted as “In TDS 7.x flow”. ↩
- Shared Memory Properties - Microsoft Learn. States that clients using shared memory can only connect to an instance on the same computer. ↩
- Encrypt Connections by Importing a Certificate - Microsoft Learn. The Force Encryption setting and its restart requirement, the self-signed fallback certificate, login packets always being encrypted, and the verification query that excludes shared memory. ↩
- [MS-TDS]: PRELOGIN - Microsoft Open Specifications. The encryption negotiation tables for TDS 7.x: a client that sends ENCRYPT_OFF to a server set to ENCRYPT_OFF encrypts the login packet only, and a client that sends ENCRYPT_NOT_SUP gets no encryption unless the server requires it, in which case the connection is terminated. ↩
- Introduction to the Microsoft.Data.SqlClient namespace - Microsoft Learn. States that version 4.0 changed Encrypt to default to true, and version 5.0 added Encrypt=Strict for TDS 8.0. ↩
- Release notes for ODBC Driver for SQL Server on Windows - Microsoft Learn. The 18.0 entry lists “Default Encrypt to Yes/Mandatory” as a breaking change, along with the change to certificate validation. ↩
- TDS 8.0 - Microsoft Learn. TDS 8.0 support from SQL Server 2022, the connection sequence for TDS 7.x and 8.0, the Encrypt and TrustServerCertificate keywords, and the rule that strict encryption can’t bypass certificate validation. ↩
- Connect to SQL Server with strict encryption - Microsoft Learn. Prerequisites for strict encryption, including a TLS certificate on the server, and the Force Strict Encryption option in SQL Server Configuration Manager. ↩
- sys.dm_server_registry (Transact-SQL) - Microsoft Learn. Describes the view as returning registry configuration for the instance, including network configuration values. ↩