How to verify Force Encryption is enabled for SQL Server

Question

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.

Answer 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

Answer 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)"
   }
}