Introduction

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


The reason behind incorporation of TLS into the SigningHub is because it provides the 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 into 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 machine where SQL Server is running.
  • Set Force Encryption to true under SQL Configuration Manager.
  • Select Certificate from certificate tab under SQL Server Configuration Manager.
  • Restart SQL Service
  • Add required parameters under connection string.
  • Run the application after restarting IIS.

Configuration for Certificate Enrollment

To setup TLS configuration Certificate, following are requirements which must be done.


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 the SQL Server. Some important information which must be known while creating a certificate is as follows:

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

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

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


To verify these parameters, double click on certificate and following dialog will appear.


Click ‘Open’ and go to the ‘Details’ Tab and select Subject field. CN must contains FQDN as a parameter value.


DNS (Domain Name System) under the Subject Alternative Name (SAN) property must contain FQDN. As shown in the image below.

The certificate must be meant for server authentication. Select 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 current date.


Once a certificate with above pre-requisites is generated, then certificate needs to import under certificate store of machine where the SQL Server is running.


Installation of SQL Server Certificate Using Microsoft Management Console

If you obtained a certificate which fulfills 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). Press Open button, as shown below.


 

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

 

Select Certificates, click on Add button.

 
Now you are prompted to open the certificates snap-in, select 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, as shown below.


 

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

 

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

 

Import required certificate, that was created using 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 binding 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 to certificate under MMC, and under properties select Enable only the following purposes option. Select Server Authentication option, as shown below.


Post Configurations Steps

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

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

 

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


HKEY_LOCAL_MACHINE -> Software -> Microsoft -> Microsoft SQL Server -> MSSQL13.MSSQLSERVER->MSSQLServer->SueperSocketNetLib->Certificate (Key)


Example Value for Thumbprint: 

It must be without spaces

‎d3 ca 0f 63 f9 05 a7 f5 67 af 6e 10 df 6f be ab 3f 72 14 4e .



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

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


Configurations Required for SigningHub Connection String

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


Two parameters that are required to add under connection string to setup TLS connection for SigningHub with SQL Server.

Encrypt=true;TrustServerCertificate=false



1) Add these parameters under web config of application in following format:

connectionString="data source={%FQDN_SQLSERVER%},1433;initial catalog={%DB_NAME%};user id=sa;password={%PASSWORD%};MultipleActiveResultSets=True;Pooling=true;Encrypt=true;TrustServerCertificate=false"


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

Restart IIS after changes in web configuration.


Verification of TLS Configurations

In order to verify if the connections are being encrypted with SQL Server DB, execute 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 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 Existing SigningHub Instance Connection String Over TLS

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


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


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


See Also