How to verify Force Encryption is enabled for SQL Server

All we need is an easy explanation of the problem, so here it is.

I’m wondering if anyone knows of a way to query if "Force encryption" is set to true for SQL Server.

I know I can check sys.dm_exec_connections to see if the current connections are encrypted, but that doesn’t really get me what I want. All the connections could still be encrypted even with "Force encryption" set to false if the client requests encryption.

I’m building a report that scans all our SQL servers in our environment to test against our best practice settings so that we can fixed mis-configured systems. Since the report will be run on a schedule, relying on sys.dm_exec_connections could get me a false sense of security, as all the connection could be encrypted when the report was run, but five seconds later something connects unencrypted. Our goal is to try and get all our SQL servers to force encryption and then be able to report on abnormalities.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As an alternative to David Browne’s answer, in case you require a purely T-SQL solution for your report, you can read the value from registry from within each instance of SQL Server:

DECLARE @ForceEncryption INT

EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib', 'ForceEncryption', @ForceEncryption OUTPUT

SELECT CASE WHEN @ForceEncryption = 1 THEN 'Encryption Forced' ELSE 'Encryption Not Enforced' END

Method 2

You can get and set this information from WMI, which is accessible through PowerShell. Something like:

foreach ( $name in ( gwmi -ns 'root\Microsoft\SqlServer' __NAMESPACE | ? {$_.name -match 'ComputerManagement' } | select name ) )
{
   $ns = "root\Microsoft\SqlServer\$($name.name)"
   $wmi = Get-WmiObject -Namespace $ns -Class "ServerSettingsGeneralFlag" | where{$_.FlagName -eq "ForceEncryption" }
   foreach ($setting in $wmi)
   {
      Write-Output "Server $($wmi.__SERVER), Instance $($wmi.InstanceName), $($wmi.FlagName): $($wmi.FlagValue)"
   }
}

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply