Nowadays security takes utmost importance in the wake of several data breaches reported in the last few years. These are also enforced by some regulatory authorities (GDPR or Data Protection Act or PCI standards) as compliance.

Particularly the companies involved in processing customer payments have to adhere to PCI DSS (Payment Card Industry Data Security Standard). For PCI compliance, enterprise application need to start using TLS 1.2 (Transport Layer Security) protocol before 30th June 2018.

Prior to TLS 1.2 there were 2 other versions of them 1.1 and 1.0 which were also referred as SSL (Secured Socket Layer) developed by Netscape. Particularly TLS 1.1 was referred by many as SSL V3. The need for switching to the new TLS 1.2 is to address the vulnerabilities posed by the previous protocols.

Further reading: https://www.gracefulsecurity.com/tls-ssl-vulnerabilities/ 

Implement TLS 1.2

To move the applications using SQL Server to new protocol, we would need to involve the application developers along with DBA’s to make the transition.

There are 2 methods of encrypting the connections:

  1. Using self-certificates: prone to MIM (Man-In-Middle) attacks and should be used only in scenarios where all clients reside with the same domain.
  2. CA certificates: These are certificates issued by Certificate Authority and should always be the preferred method

I will try to cover the configuration methods in both the cases.

Pre-Requisites:

Servers with .Net 4.6 framework by default support TLS 1.2 and Windows Server 2016 falls in this category. But if you have a previous version of Windows, goto to the following link below and download appropriate patches required.

https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server

Alternatively, you can download the below powershell scripts to detect the .Net version and also to check if client components are TLS 1.2 compatible.

(Right click on the link and click “save link as” which downloads as .txt file. Change the extension to .ps1, open Windows Powershell and execute them. )

Detect .Net version: DotNetVersion_Detect

TLS1.2 compatibility check: CheckClientUpdates

Self-Signed Certificate Configuration:

Application:

For applications with SQL Server Native Client, Connection string keywords ENCRYPT and TRUSTEDSERVERCERTIFICATE can be used to enforce an encrypted connection. Using these keywords will generate a self-signed certificate for the encryption.

More information can be found here:

https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-2017

SQL Server:

Changes need to be made in SQL Server Configuration Manager. Open Network Configuration and right click on SQL Instance and click on properties. In the Flags tab select “Force Encryption” to “Yes” as shown in the below screenshot

 

 

 

 

 

 

 

 

 

 

 

Now the connections are encrypted for both the Windows and SQL logins. You can also verify the same using the below query in the SQL Server Management Studio

SELECT c.connect_time, c.auth_scheme, s.original_login_name, DB_NAME(s.database_id) AS DBNAME FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
WHERE c.encrypt_option = 'FALSE' -- Change it to TRUE to see all the encrypted connections

Disabling TLS 1.1 and 1.0:

Usually we do not need to disable the older TLS versions on Windows Servers as only the latest protocol will be used by the Server. It could be limited only by the .Net framework capabilities like for example if the server has .Net 4.5 then even if TLS 1.2 is configured it can use TLS 1.1 or earlier.

But for compliance proof if you were required to disable the earlier TLS/SSL versions and to enable TLS 1.2, we would need to modify the registry settings.

Update the registry keywords “Disabled by default” and “Enabled” to 1 for the subkeys below:

HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS1.2\Client

HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\ TLS1.2\Server

 

 

 

 

 

 

(Be sure to backup your registry before making any changes)

This requires a server restart for changes to take effect.

CA Certificate Configuration for SQL Server:

If a CA certificate is being used then your application connection string keyword TRUSTEDSERVERCERTIFICATE should be set to “False”

Installing certificate in SQL Server is usually done by Windows Admin, however it is good to know the process:

  • Using win+R type “mmc” and open the console
  • Click Add/Remove Snap-in
  • From Available snap-ins, double-click Certificates.
  • Select Computer Account.
  • Click Next.
  • Select Local Computer.
  • Click Finish.
  • Click OK.
  • Expand Certificates (Local Computer).
  • Right-Click Personal.
  • Click All Tasks.
  • Click Import…
  • Click Next.
  • Click Browse.
  • Select All Files *.* from the file-types pulldown.
  • Select the certificate
  • Click OK.
  • Enter a private key password, if your private key has one.
  • Click Next.
  • Click Next.
  • Click Finish.
  • Exit
  • Don’t save settings.

 

 

 

 

 

 

 

Configure MS SQL Server to use the certificate.

  • Open SQL Server Configuration Manager.
  • Expand SQL Server Network Configuration.
  • Right-Click Protocols for SQL Instance and select Properties.
  • Select the Certificates Tab.
  • Select certificate from the pulldown list.
    • If the certificate is missing from the list, then:
      • The common name in the certificate is something other than FQDN of the SQL Server
        • The best solution is to generate a new certificate.
  • Click OK.
  • Exit the SQL Server Configuration Manager.

Restart SQL Service

 

 

 

 

 

 

 

Ref:

https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server

https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/using-encryption-without-validation?view=sql-server-2017

https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi

As with anything, make sure you test it in your pre-production environment for a proof of concept

-Hari Mindi

 


Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.