2

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.

2 Answers 2

5

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
1
  • Ah! There it is. I was looking all over the registry for something like that, but not finding it and figured they stored the value somewhere else. Thanks. You rock! Jul 20, 2021 at 21:02
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.