How Secure Are Your Client Connections?

2018-09-04 1 Comment

First, thanks to Sean McCown (t|w) and Mladen Prajdic (t|w) for their assistance with finding & confirming some of the information below. -Jamie

Encrypting sensitive data (medical, financial, etc) in transit between client and server has been a legal requirement for many years. Websites have been using SSL/TLS certificates (https) to achieve this since the mid-1990s. Beyond enabling data encryption in transit, certificates also provide server authenticity (verifying the server’s identity & ownership to the client) and help protect against man-in-the-middle attacks.

More recently, non-sensitive information has been used to dramatically increase the effectiveness of spear phishing and other social engineering exploits. Additionally, the use of advanced data-mining techniques combined with the increasing availability of Big Data has started to blur the line for determining what is Personally Identifiable Information (PII). This is resulting in users needing to be more aware of what information is ‘private’, along with the source & authenticity of the information that they receive. Many security and privacy experts have begun recommending a ‘Secure Connections Everywhere’ approach to internet activity. Google also has begun strongly advocating for the use of encrypted connections by marking websites as Secure or Not Secure in the Chrome browser.

So, a question that should be asked is: How secure are your client connections? Here are a couple common misconceptions about SQL server client connections.

Misconception: Usernames & passwords (SQL or Windows) are used to connect to SQL server databases, which means the client-server connection is secure.

Explanation
Usernames & passwords are used to control who has what level of permission (read/write/modify) to the data & database. By default, the information being transmitted is not encrypted. As John Martin shows in this article, it is relatively easy for someone with access to a network (wireless access point or LAN connection) to read the unencrypted data that is being sent between a SQL server and client.

Misconception: SQL server client connections that use SSL/TLS certificates are secure.

Explanation
SSL/TLS certificates do not encrypt data. Certificates contain authentication information to validate the server’s ownership, and include the public ‘half’ of the public/private encryption key pair. Certificates are used with an encryption protocol and a cipher to protect the data. SSL & TLS are protocols used to provide the public/private keys to the client. The actual encryption is done using an encryption cipher (RC4, 3DES, Twofish, etc). Several protocols & ciphers have published vulnerabilities and should not be used (SSLv2, SSLv3, TLS1.0, RC4, 3DES, etc). So the connection’s security will depend on whether or not the protocol & cipher being used are vulnerable.

To ensure that client connections are as secure as possible, servers should be updated to support the recommended protocols. Microsoft has published this article with support information on upgrading Windows servers to TLS1.2, which is the currently recommended protocol. Additionally, support for any vulnerable protocols & ciphers should be disabled. On Windows-based SQL servers, the Schannel Security Support Provider (SSP) is used by the system to administer the SSL & TLS protocols, and Schannel registry subkeys are used to enable/disable the protocols & ciphers. Alexander Haas has posted a good article and a PowerShell script (below) that will disable the protocols & ciphers that are currently known to be vulnerable.

Now that we’ve established that SQL server client connections are not secure by default, and that some encryption protocols & ciphers should not be used, here are some directions for enabling encrypted connections and disabling vulnerable protocols & ciphers.

The basic process is:

  1. Generate a new certificate request on the server. (PowerShell cert request script here)
  2. Upload the certificate request to a Certificate Authority (CA).
  3. Download the new certificate from the CA after it has been approved/created.
  4. Import the certificate to the server’s Certificate Store. (PowerShell script)
  5. (optional, but recommended) Export the certificate with a Private Key, so that the cert can be re-imported if the server is rebuilt.
  6. Configure SQL Server to use encrypted client connections.
  7. Disable vulnerable protocols & ciphers. (Alexander Haas script)
  8. Verify that the client connections are now encrypted. (SQL query)

Request and Install a New SSL Certificate

For Windows servers, a new SSL certificate can be requested and installed using IIS or the CertReq utility.

Powershell Certificate Request Script Using CertReq

Param(
[Parameter(Mandatory=$true)][string]$CN,
[switch]$RequestSAN
)
$UID = [guid]::NewGuid()
$files = @{}
$files['settings'] = "$($env:TEMP)\$($UID)-settings.inf";
$files['csr'] = "$($env:TEMP)\$($UID)-csr.req"
$request = @{}
$request['SAN'] = @{}
If ( $CN ) {
$request['CN'] = $CN
} Else {
$request['CN'] = Read-Host "Common Name (ie. FQDN)"
}
$request['O'] = "Organization Name"
$request['OU'] = "Org Unit Name"
$request['L'] = "Locale/City"
$request['S'] = "State"
$request['C'] = "Country"
If ( $RequestSAN ) {
$i = 0
Do {
$i++
$request['SAN'][$i] = read-host "Subject Alternative Name $i (ie. alternate FQDN / leave empty for none)"
if ($request['SAN'][$i] -eq "") {
}
} until ($request['SAN'][$i] -eq "")
# Remove the last in the array (which is empty)
$request['SAN'].Remove($request['SAN'].Count)
}
$settingsInf = "
[Version]
Signature=`"`$Windows NT`$
[NewRequest]
KeyLength = 2048
Exportable = TRUE
MachineKeySet = TRUE
SMIME = FALSE
RequestType = PKCS10
ProviderName = `"Microsoft RSA SChannel Cryptographic Provider`"
ProviderType = 12
HashAlgorithm = sha256
;Variables
Subject = `"CN={{CN}},OU={{OU}},O={{O}},L={{L}},S={{S}},C={{C}}`"
[Extensions]
{{SAN}}
;Certreq info
;http://technet.microsoft.com/en-us/library/dn296456.aspx
;CSR Decoder
;https://certlogik.com/decoder/
;https://ssltools.websecurity.symantec.com/checker/views/csrCheck.jsp
"
$request['SAN_string'] = & {
if ($request['SAN'].Count -gt 0) {
$san = "2.5.29.17 = `"{text}`"
"
Foreach ($sanItem In $request['SAN'].Values) {
$san += "_continue_ = `"dns="+$sanItem+"&`"
"
}
return $san
}
}
$settingsInf = $settingsInf.Replace("{{CN}}",$request['CN']).Replace("{{O}}",$request['O']).Replace("{{OU}}",$request['OU']).Replace("{{L}}",$request['L']).Replace("{{S}}",$request['S']).Replace("{{C}}",$request['C']).Replace("{{SAN}}",$request['SAN_string'])
$settingsInf > $files['settings']
Clear-Host
certreq -new $files['settings'] $files['csr'] > $null
$CSR = Get-Content $files['csr']
Write-Output $CSR
$files.Values | ForEach-Object {
Remove-Item $_ -ErrorAction SilentlyContinue
}

Import SSL Certificate Using CertReq

Once the certificate request has been approved and downloaded, it can be imported using the MMC Certificate snap-in or CertReq.

certreq -accept -machine <path to downloaded certificate>

Enable Encrypted Client Connections

Encrypted client connections can be enabled using PowerShell or the SQL Server Configuration Management (SSCM) utility.

PowerShell Config

Param ([string]$SQLServiceAcct,
        [string]$SSLCertName
);

$certObj = dir Cert:\LocalMachine\My | where {$_.Subject -match $SSLCertName} | sort $_.NotAfter -Descending | Select -First 1 -ErrorAction Stop;
If ($certObj) {
    write-output "Certificate found with thumbprint: "$certObj.Thumbprint;
    $keyName=(((Get-ChildItem Cert:\LocalMachine\My | Where-Object {$_.Thumbprint -like $certObj.Thumbprint}).PrivateKey).CspKeyContainerInfo).UniqueKeyContainerName
    $keyPath = "C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\";
    $fullPath=$keyPath+$keyName;
    $acl=Get-Acl -Path $fullPath;
    $newPermission= $SQLServiceAcct,"Read","Allow";
    $accessRule=new-object System.Security.AccessControl.FileSystemAccessRule $newPermission;
    $acl.AddAccessRule($accessRule);
    Try {
        Set-Acl $fullPath $acl;
        write-output "ACL updated";
        }
    Catch {
        write-output "Error updating ACL";
        Exit;
        }
    write-output "Set Encrypted Connection Certificate"
    $regkey = Get-ChildItem -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server" -Recurse | Where-Object {$_.Name -like "*SuperSocketNetLib"}
    Set-ItemProperty -path $regKey.PSPath -Name Certificate -value ($certObj.Thumbprint).ToLower() ;
    write-output "Enable SSL encryption for SQL client connections."
    $regkey = Get-ChildItem -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server" -Recurse | Where-Object {$_.Name -like "*SuperSocketNetLib"}
    Set-ItemProperty -path $regKey.PSPath -Name ForceEncryption -value "1";
    }
Else {
    write-output "Unable to find certificate for $SSLCertName";
    write-output "Enable SSL encryption for SQL client connections using Self-Signed Certificate.";
    $regkey = Get-ChildItem -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server" -Recurse | Where-Object {$_.Name -like "*SuperSocketNetLib"}
    Set-ItemProperty -path $regKey.PSPath -Name ForceEncryption -value "1";
    Exit;
    }

write-output "SQL service must be restarted before setting will take effect."
Restart-Service MSSQLSERVER -Force;
write-output "SQL Service restarted."

SQL Server Configuration Manager

  1. Open SQL Server Configuration Manager.
  2. Expand the SQL Server Network Configuration section.
  3. Right-click on Protocols for <Instance Name> and select Properties
  4. On the Certificates tab, select the SSL certificate from the drop-down list.
  5. On the Flags tab, set Force Encryption to Yes.
  6. Click OK to complete the configuration (a service restart may be required).

Disable Vulnerable Protocols & Ciphers (Alexander Haas script)

# Copyright 2018, Alexander Hass
# http://www.hass.de/content/setup-your-iis-ssl-perfect-forward-secrecy-and-tls-12
#
# Version 1.11
# - Disabled 3DES to get rid of all ciphers with less than 128bit encryption strength.
# Version 1.10
# - Created PCI DSS 3.1 compatible version.
# Version 1.9
# - Enabled TLS 1.1 and TLS 1.2 for WinHttp client connections.
# - Hardening .NET 3.5 + 4.x client connections.
# - Hardening Diffie-Hellman Key Exchange.
# Version 1.8
# - Windows 2016 powershell 5.1.14393.1532 requires 'else' statements in the same line after to the closing 'if' curly quote.
# Version 1.7
# - Windows Version compare failed. Get-CimInstance requires Windows 2012 or later.
# Version 1.6
# - OS version detection for cipher suites order.
# Version 1.5
# - Enabled ECDH and more secure hash functions and reorderd cipher list.
# - Added Client setting for all ciphers.
# Version 1.4
# - RC4 has been disabled.
# Version 1.3
# - MD5 has been disabled.
# Version 1.2
# - Re-factored code style and output
# Version 1.1
# - SSLv3 has been disabled. (Poodle attack protection)
 
Write-Host 'Configuring IIS with SSL/TLS Deployment Best Practices...'
Write-Host '--------------------------------------------------------------------------------'
 
# Disable Multi-Protocol Unified Hello
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Server' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Server' -name Enabled -value 0 -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Server' -name 'DisabledByDefault' -value 1 -PropertyType 'DWord' -Force | Out-Null
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Client' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Client' -name Enabled -value 0 -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\Multi-Protocol Unified Hello\Client' -name 'DisabledByDefault' -value 1 -PropertyType 'DWord' -Force | Out-Null
Write-Host 'Multi-Protocol Unified Hello has been disabled.'
 
# Disable PCT 1.0
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Server' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Server' -name Enabled -value 0 -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Server' -name 'DisabledByDefault' -value 1 -PropertyType 'DWord' -Force | Out-Null
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Client' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Client' -name Enabled -value 0 -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\PCT 1.0\Client' -name 'DisabledByDefault' -value 1 -PropertyType 'DWord' -Force | Out-Null
Write-Host 'PCT 1.0 has been disabled.'
 
# Disable SSL 2.0 (PCI Compliance)
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server' -name Enabled -value 0 -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server' -name 'DisabledByDefault' -value 1 -PropertyType 'DWord' -Force | Out-Null
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client' -name Enabled -value 0 -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client' -name 'DisabledByDefault' -value 1 -PropertyType 'DWord' -Force | Out-Null
Write-Host 'SSL 2.0 has been disabled.'
 
# NOTE: If you disable SSL 3.0 the you may lock out some people still using
# Windows XP with IE6/7. Without SSL 3.0 enabled, there is no protocol available
# for these people to fall back. Safer shopping certifications may require that
# you disable SSLv3.
#
# Disable SSL 3.0 (PCI Compliance) and enable "Poodle" protection
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server' -name Enabled -value 0 -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server' -name 'DisabledByDefault' -value 1 -PropertyType 'DWord' -Force | Out-Null
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client' -name Enabled -value 0 -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client' -name 'DisabledByDefault' -value 1 -PropertyType 'DWord' -Force | Out-Null
Write-Host 'SSL 3.0 has been disabled.'
 
# Disable TLS 1.0 for client and server SCHANNEL communications
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server' -name 'Enabled' -value '0' -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server' -name 'DisabledByDefault' -value 1 -PropertyType 'DWord' -Force | Out-Null
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client' -name 'Enabled' -value '0' -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client' -name 'DisabledByDefault' -value 1 -PropertyType 'DWord' -Force | Out-Null
Write-Host 'TLS 1.0 has been disabled.'
 
# Add and Enable TLS 1.1 for client and server SCHANNEL communications
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server' -name 'Enabled' -value '0xffffffff' -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server' -name 'DisabledByDefault' -value 0 -PropertyType 'DWord' -Force | Out-Null
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client' -name 'Enabled' -value '0xffffffff' -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client' -name 'DisabledByDefault' -value 0 -PropertyType 'DWord' -Force | Out-Null
Write-Host 'TLS 1.1 has been enabled.'
 
# Add and Enable TLS 1.2 for client and server SCHANNEL communications
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server' -name 'Enabled' -value '0xffffffff' -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server' -name 'DisabledByDefault' -value 0 -PropertyType 'DWord' -Force | Out-Null
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client' -name 'Enabled' -value '0xffffffff' -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client' -name 'DisabledByDefault' -value 0 -PropertyType 'DWord' -Force | Out-Null
Write-Host 'TLS 1.2 has been enabled.'
 
# Re-create the ciphers key.
New-Item 'HKLM:SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers' -Force | Out-Null
 
# Disable insecure/weak ciphers.
$insecureCiphers = @(
  'DES 56/56',
  'NULL',
  'RC2 128/128',
  'RC2 40/128',
  'RC2 56/128',
  'RC4 40/128',
  'RC4 56/128',
  'RC4 64/128',
  'RC4 128/128',
  'Triple DES 168'
)
Foreach ($insecureCipher in $insecureCiphers) {
  $key = (Get-Item HKLM:\).OpenSubKey('SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers', $true).CreateSubKey($insecureCipher)
  $key.SetValue('Enabled', 0, 'DWord')
  $key.close()
  Write-Host "Weak cipher $insecureCipher has been disabled."
}
 
# Enable new secure ciphers.
# - RC4: It is recommended to disable RC4, but you may lock out WinXP/IE8 if you enforce this. This is a requirement for FIPS 140-2.
# - 3DES: It is recommended to disable these in near future. This is the last cipher supported by Windows XP.
# - Windows Vista and before 'Triple DES 168' was named 'Triple DES 168/168' per https://support.microsoft.com/en-us/kb/245030
$secureCiphers = @(
  'AES 128/128',
  'AES 256/256'
)
Foreach ($secureCipher in $secureCiphers) {
  $key = (Get-Item HKLM:\).OpenSubKey('SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers', $true).CreateSubKey($secureCipher)
  New-ItemProperty -path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Ciphers\$secureCipher" -name 'Enabled' -value '0xffffffff' -PropertyType 'DWord' -Force | Out-Null
  $key.close()
  Write-Host "Strong cipher $secureCipher has been enabled."
}
 
# Set hashes configuration.
New-Item 'HKLM:SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes' -Force | Out-Null
New-Item 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\MD5' -Force | Out-Null
New-ItemProperty -path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\MD5' -name Enabled -value 0 -PropertyType 'DWord' -Force | Out-Null
 
$secureHashes = @(
  'SHA',
  'SHA256',
  'SHA384',
  'SHA512'
)
Foreach ($secureHash in $secureHashes) {
  $key = (Get-Item HKLM:\).OpenSubKey('SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes', $true).CreateSubKey($secureHash)
  New-ItemProperty -path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\$secureHash" -name 'Enabled' -value '0xffffffff' -PropertyType 'DWord' -Force | Out-Null
  $key.close()
  Write-Host "Hash $secureHash has been enabled."
}
 
# Set KeyExchangeAlgorithms configuration.
New-Item 'HKLM:SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\KeyExchangeAlgorithms' -Force | Out-Null
$secureKeyExchangeAlgorithms = @(
  'Diffie-Hellman',
  'ECDH',
  'PKCS'
)
Foreach ($secureKeyExchangeAlgorithm in $secureKeyExchangeAlgorithms) {
  $key = (Get-Item HKLM:\).OpenSubKey('SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\KeyExchangeAlgorithms', $true).CreateSubKey($secureKeyExchangeAlgorithm)
  New-ItemProperty -path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\KeyExchangeAlgorithms\$secureKeyExchangeAlgorithm" -name 'Enabled' -value '0xffffffff' -PropertyType 'DWord' -Force | Out-Null
  $key.close()
  Write-Host "KeyExchangeAlgorithm $secureKeyExchangeAlgorithm has been enabled."
}
 
# Microsoft Security Advisory 3174644 - Updated Support for Diffie-Hellman Key Exchange
# https://docs.microsoft.com/en-us/security-updates/SecurityAdvisories/2016/3174644
Write-Host 'Configure longer DHE key shares for TLS servers.'
New-ItemProperty -path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\KeyExchangeAlgorithms\Diffie-Hellman" -name 'ServerMinKeyBitLength' -value '2048' -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\KeyExchangeAlgorithms\Diffie-Hellman" -name 'ClientMinKeyBitLength' -value '2048' -PropertyType 'DWord' -Force | Out-Null
 
# https://support.microsoft.com/en-us/help/3174644/microsoft-security-advisory-updated-support-for-diffie-hellman-key-exc
New-ItemProperty -path "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\KeyExchangeAlgorithms\PKCS" -name 'ClientMinKeyBitLength' -value '2048' -PropertyType 'DWord' -Force | Out-Null
 
# Set cipher suites order as secure as possible (Enables Perfect Forward Secrecy).
$os = Get-WmiObject -class Win32_OperatingSystem
if ([System.Version]$os.Version -lt [System.Version]'10.0') {
  Write-Host 'Use cipher suites order for Windows 2008/2008R2/2012/2012R2.'
  $cipherSuitesOrder = @(
    'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P521',
    'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384',
    'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P256',
    'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P521',
    'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P384',
    'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256',
    'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P521',
    'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P384',
    'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA_P256',
    'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA_P521',
    'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA_P384',
    'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA_P256',
    'TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384_P521',
    'TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384_P384',
    'TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256_P521',
    'TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256_P384',
    'TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256_P256',
    'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384_P521',
    'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384_P384',
    'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256_P521',
    'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256_P384',
    'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256_P256',
    'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA_P521',
    'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA_P384',
    'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA_P256',
    'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA_P521',
    'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA_P384',
    'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA_P256',
    'TLS_RSA_WITH_AES_256_GCM_SHA384',
    'TLS_RSA_WITH_AES_128_GCM_SHA256',
    'TLS_RSA_WITH_AES_256_CBC_SHA256',
    'TLS_RSA_WITH_AES_128_CBC_SHA256',
    'TLS_RSA_WITH_AES_256_CBC_SHA',
    'TLS_RSA_WITH_AES_128_CBC_SHA'
  )
} else {
  Write-Host 'Use cipher suites order for Windows 10/2016 and later.'
  $cipherSuitesOrder = @(
    'TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384',
    'TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256',
    'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384',
    'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256',
    'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA',
    'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA',
    'TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384',
    'TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256',
    'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384',
    'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256',
    'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA',
    'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA',
    'TLS_RSA_WITH_AES_256_GCM_SHA384',
    'TLS_RSA_WITH_AES_128_GCM_SHA256',
    'TLS_RSA_WITH_AES_256_CBC_SHA256',
    'TLS_RSA_WITH_AES_128_CBC_SHA256',
    'TLS_RSA_WITH_AES_256_CBC_SHA',
    'TLS_RSA_WITH_AES_128_CBC_SHA'
  )
}
$cipherSuitesAsString = [string]::join(',', $cipherSuitesOrder)
# One user reported this key does not exists on Windows 2012R2. Cannot repro myself on a brand new Windows 2012R2 core machine. Adding this just to be save.
New-Item 'HKLM:\SOFTWARE\Policies\Microsoft\Cryptography\Configuration\SSL\00010002' -ErrorAction SilentlyContinue
New-ItemProperty -path 'HKLM:\SOFTWARE\Policies\Microsoft\Cryptography\Configuration\SSL\00010002' -name 'Functions' -value $cipherSuitesAsString -PropertyType 'String' -Force | Out-Null
 
# Exchange Server TLS guidance Part 2: Enabling TLS 1.2 and Identifying Clients Not Using It
# https://blogs.technet.microsoft.com/exchange/2018/04/02/exchange-server-tls-guidance-part-2-enabling-tls-1-2-and-identifying-clients-not-using-it/
# New IIS functionality to help identify weak TLS usage
# https://cloudblogs.microsoft.com/microsoftsecure/2017/09/07/new-iis-functionality-to-help-identify-weak-tls-usage/
Write-Host 'Enable TLS 1.2 for .NET 3.5 and .NET 4.x'
New-ItemProperty -path "HKLM:\SOFTWARE\Microsoft\.NETFramework\v2.0.50727" -name 'SystemDefaultTlsVersions' -value 1 -PropertyType 'DWord' -Force | Out-Null
New-ItemProperty -path "HKLM:\SOFTWARE\Microsoft\.NETFramework\v4.0.30319" -name 'SystemDefaultTlsVersions' -value 1 -PropertyType 'DWord' -Force | Out-Null
if (Test-Path 'HKLM:\SOFTWARE\Wow6432Node') {
  New-ItemProperty -path "HKLM:\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v2.0.50727" -name 'SystemDefaultTlsVersions' -value 1 -PropertyType 'DWord' -Force | Out-Null
  New-ItemProperty -path "HKLM:\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319" -name 'SystemDefaultTlsVersions' -value 1 -PropertyType 'DWord' -Force | Out-Null
}
 
# Update to enable TLS 1.1 and TLS 1.2 as a default secure protocols in WinHTTP in Windows
# https://support.microsoft.com/en-us/help/3140245/update-to-enable-tls-1-1-and-tls-1-2-as-a-default-secure-protocols-in
 
# Verify if hotfix KB3140245 is installed.
if ([System.Version](Get-Item $env:windir\system32\Webio.dll).VersionInfo.ProductVersion -lt [System.Version]"6.1.7601.23375" -or [System.Version](Get-Item $env:windir\system32\Winhttp.dll).VersionInfo.ProductVersion -lt [System.Version]"6.1.7601.23375") {
  Write-Host 'WinHTTP: Cannot enable TLS 1.1 and TLS 1.2. Please see https://support.microsoft.com/en-us/help/3140245/update-to-enable-tls-1-1-and-tls-1-2-as-a-default-secure-protocols-in for system requirements.'
} else {
  Write-Host 'WinHTTP: Minimum system requirements are met.'
 
  # DefaultSecureProtocols Value	Decimal value  Protocol enabled
  # 0x00000008                                8  Enable SSL 2.0 by default
  # 0x00000020                               32  Enable SSL 3.0 by default
  # 0x00000080                              128  Enable TLS 1.0 by default
  # 0x00000200                              512  Enable TLS 1.1 by default
  # 0x00000800                             2048  Enable TLS 1.2 by default
  $defaultSecureProtocols = @(
    '512',  # TLS 1.1
    '2048'  # TLS 1.2
  )
  $defaultSecureProtocolsSum = ($defaultSecureProtocols | Measure-Object -Sum).Sum
 
  Write-Host 'WinHTTP: Activate TLS 1.1 and TLS 1.2 only.'
  New-ItemProperty -path 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\WinHttp' -name 'DefaultSecureProtocols' -value $defaultSecureProtocolsSum -PropertyType 'DWord' -Force | Out-Null
  if (Test-Path 'HKLM:\SOFTWARE\Wow6432Node') {
    New-ItemProperty -path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Internet Settings\WinHttp' -name 'DefaultSecureProtocols' -value $defaultSecureProtocolsSum -PropertyType 'DWord' -Force | Out-Null
  }
}
 
Write-Host '--------------------------------------------------------------------------------'
Write-Host 'NOTE: After the system has been rebooted you can verify your server'
Write-Host '      configuration at https://www.ssllabs.com/ssltest/'
Write-Host "--------------------------------------------------------------------------------`n"
 
Write-Host -ForegroundColor Red 'A computer restart is required to apply settings. Restart computer now?'
Restart-Computer -Force -Confirm

Verify Client Connections Are Encrypted

SELECT s.login_name, c.encrypt_option, auth_scheme, c.client_net_address
FROM sys.dm_exec_connections c LEFT JOIN
sys.dm_exec_sessions s ON c.session_id = s.session_id

1 thought on “How Secure Are Your Client Connections?”

  1. Pingback: Encrypting SQL Server Connections – Curated SQL

Leave a Reply

Your email address will not be published. Required fields are marked *