How to check SSL encryption is enabled on SQL Server with Powershell

Question:  Do you have a powershell script or t-sql  script to find if ssl encryption is enabled on SQL Server?   I don't want to check if connections are SSL enabled but actually check the Force Encryption flag is enabled 

Answer: It is possible to use either Powershell or t-sql to establish if ssl encryption is enabled on SQL Server.   The method you use to collect this information will depend on how you manage your SQL Servers .

Method 1 : Use WMI , through Powershell 

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

sample output

Server Host: MyServer,SQL Server Instance: DBAMGT, ForceEncryption: False

Method 2 : Use t-sql to establish if a SQL Server Instance has SSL enabled 

DECLARE @EncryptionForced INT

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

SELECT CASE WHEN @EncryptionForced = 1 THEN 'Encryption Forced = Yes ' ELSE 'Encryption Forced = No' END

Read more on SSL & SQL Server

How to check a SQL Server connection is encrypted with SSL

How to fix Connection failed - SQL Server Error 772 - TCPIP Socket