Transport Layer Security with SQL

Introduction

The SigningHub application must be configured over TLS (Transport Layer Security) to connect it with the SQL Server database over a secure connection.

The reason behind the incorporation of TLS into the SigningHub is that it provides data encryption while using the SQL Server. The SQL Server can use Transport Layer Security (TLS) in order to encrypt the data which will be transmitted across the network between an instance of SQL Server and the client application. The basic reason to configure TLS on the SQL server is:

  • Enabling TLS encryption increases the security of data transmitted across networks between instances of SQL Server and applications.

  • TLS performs server validation, for instance, when a client connection will request the encryption, then following procedure will run: If the instance of SQL Server is running on a computer that has been assigned a certificate from a public certification authority, identity of the computer and the instance of SQL Server is guaranteed by the chain of certificates that lead to the trusted root authority. Such server validation will be going to require that the computer on which the client application is running should be configured to trust the root authority of the certificate that is used by the server.

How it works?

To enable TLS configurations for SigningHub following are high-level steps that need to be done.

  • Configure a certificate (where Common Name (CN) must have Fully Qualified Domain Name (FQDN), Enhanced Key Usage must have Server authentication, Subject Alternative Name (SAN) property must have FQDN under DNS)

  • Import this certificate under Microsoft Management Console (MMC) > Trusted Certificates, on the machine where SQL Server is running.

  • Set Force Encryption to true under SQL Configuration Manager.

  • Select Certificate from the Certificate tab under SQL Server Configuration Manager.

  • Restart SQL Service

  • Add required parameters under the connection string.

  • Run the application after restarting IIS.

Configuration for certificate enrollment

To set up the TLS configuration Certificate, the following requirements must be met.

A Certificate is required initially (which can be created under ADSS or any third-party CA). There are certain requirements that a certificate needs to have in order to work seamlessly with SQL Server. Some important information which must be known while creating a certificate is as follows:

  1. CN (Common Name) under the Subject property of the certificate must have a Fully Qualified Domain Name (FQDN) as a parameter of your SQL Server. (For instance, SQLDB.ascertia.com.pk).

  2. CN can be defined as the Subject property on the certificate, which must specify a Common Name (CN) that should be the same as the host name or fully qualified domain name (FQDN) of the SQL Server.

  3. In order to perform Server Authentication without any error, the FQDN for SQL Server must be known.

To verify these parameters, double click on the certificate, and the following dialogue will appear. Click ‘Open’ and go to the ‘Details’ Tab and select the Subject field. CN must contain FQDN as a parameter value.

DNS (Domain Name System) under the Subject Alternative Name (SAN) property must contain an FQDN.

The certificate must be meant for server authentication. Select the Enhanced Key Usage field. The value under this property must contain Server Authentication (1.3.6.1.5.5.7.3.1). The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate, to make sure that the Certificate must be valid, i.e. valid from and valid to values are within the current date.

Once a certificate with the above prerequisites is generated, then the certificate needs to be imported under the certificate store of the machine where the SQL Server is running.

Installation of SQL server certificate using Microsoft Management Console

If you obtained a certificate which fulfils the above requirements, then you are now able to import it to the certificate store on your database server, while following the steps mentioned below.

First, click on the Start menu and write MMC in the search box, and then open the Microsoft Management Console (MMC).

On the File menu, click Add/Remove Snap-in.

Select Certificates, click on the Add button. Now you are prompted to open the certificates snap-in, select the computer account and click Next.

The certificate must be located in either the local computer certificate store or the current user certificate store; thus, select Local computer, and then click Finish.

Click OK in the Add/Remove Snap-in dialogue box. After that, select the Trusted Root Certification Authorities folder in the left pane.

Click on Certificates and right click to select All Tasks->Import.

Import the required certificate, which was created using the above steps.

Now, you must be able to see the certificate in the folder with the fully qualified domain name of your SQL Server.

Configuration of SQL server to use encrypted connections

Once you've successfully installed the certificate, it needs to be bound to the database engine service in SQL Server Configuration Manager.

To configure TLS using the SQL Server Configuration Manager. First, run SQL Server Configuration Manager under the SQL Server service account. The only exception is if the service is running as Local System, Network Service, or Local Service; in this case, you can use an administrative account.

Expand SQL Server Network Configuration and right-click on Protocols for <YourMSSQLServer>, select Properties.

On the Certificate tab, select the certificate you would like to use and click ‘OK’.

On the Flags tab, select ‘Yes’ in the Force Encryption box, then click OK.

Restart the SQL Server service.

Right click on to certificate under MMC, and under properties select the Enable only the following purposes option. Select the Server Authentication option, as shown below.

Post configuration steps

If the certificate is not available in the list, then the following steps are required.

Click Start and write Run, and then type Regedit in the field, and then open it.

Add Thumbprint property value under registry (Thumbprint value can be obtained from the certificate details. Following the path which will be used to find the Certificate Key.

  1. HKEY_LOCAL_MACHINE -> Software -> Microsoft -> Microsoft SQL Server -> MSSQL13.MSSQLSERVER->MSSQLServer->SueperSocketNetLib->Certificate (Key) Example Value for Thumbprint: ‎d3 ca 0f 63 f9 05 a7 f5 67 af 6e 10 df 6f be ab 3f 72 14 4e (It must be without spaces)

Also, add the PFX under the personal store under MMC of SQL DB Server.

  1. The SQL Server Service Account must have Read permissions to access the TLS certificate by SQL Server Configuration Manager.

Configurations required for the SigningHub connection string

To set up the SigningHub application to run over a TLS connection with SQL Server, the connection string for SigningHub must be set as per the following recommendations.

Two parameters are required to add under the connection string to set up a TLS connection for SigningHub with SQL Server.

Encrypt=true;TrustServerCertificate=false

  1. Add these parameters under the web.config of the application in the following format: connectionString="data source=,1433;initial catalog=;user id=sa;password=;MultipleActiveResultSets=True;Pooling=true;Encrypt=true;TrustServerCertificate=false"

  2. Remember to use the FQDN of your SQL Server in the data source under the connection string in order to make TLS work.

Restart IIS after changes in web configuration.

Verification of TLS configurations

To verify if the connections are being encrypted with SQL Server DB, execute the following query on SQL Server.

SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address FROM sys.dm_exec_connections where encrypt_option = 'true'.

It will return all the encrypted connections for the DB Server.

To verify whether all the connections are encrypted or not, execute the following query. This has set three levels, i.e. RED means no encrypted connections, GREEN means all the connections are encrypted, and AMBER shows there were some connections that are not encrypted.

Here is the SQL Query:

WITH CTE_Summary AS
(
SELECT    encrypt_option, COUNT(*) cnt
FROM      sys.dm_exec_connections
GROUP BY  encrypt_option
)
SELECT @@ServerName AS [Server Name],
CASE WHEN COUNT(*)=1 AND MIN(encrypt_option) = 'FALSE' THEN 'RED - no connections are encrypted'
WHEN COUNT(*)=1 AND MIN(encrypt_option) = 'TRUE' THEN 'GREEN - all connections are encrypted'
ELSE 'AMBER - some connections are encrypted'
END AS [Connection Encryption RAG Status]
FROM  CTE_Summary

Update the existing SigningHub instance connection string over TLS

If an existing SigningHub instance has to be configured over TLS, then the connection string has to be updated, and it can be done using the SigningHub installer using the update database credentials option.

Simply execute the install.bat file (Run as Administrator), which is present in the 'Setup' folder under the package installation directory. Select the Change DB Credentials option and provide the connection string as follows.

Click Next, and the database credentials will update. Click on finish to complete the process.

PreviousTransparent Data Encryption with SQL

Last updated

Was this helpful?