This section provides help on information that you need to secure your database in transit (to maintain secure communication channel between your database and ADSS Web RA application) and at rest (to secure your physical data and log file of your database).

Following are two configurations that are required to achieve this database security.


  1. Enable Transparent Data Encryption (TDE)
  2. Enable Transport Layer Security (TLS)


Enable Transparent Data Encryption (TDE)


Transparent Data Encryption can be configured for Web RA, so that the data will be encrypted at rest for ADSS Web RA database, which means the data and log files are protected from any potential threat of hacking or misusing of data.

Transparent Data Encryption (TDE) encrypts SQL Server data files, which are commonly known as encrypting data at rest. The user can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned.

TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module.

It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.


To configure TDE, following are the high level steps that need to done.


  • Create master key. The Server Master Key is created at the time of the initial SQL Server instance setup. The Service Master Key encrypts the database Master Key for the master database.
  • Create certificate protected by master key. The database master key creates a certificate in the master database. Keep in mind that you must create a backup of this certificate. Not only for environmental refreshes but disaster recovery purposes.
  • Enable database encryption. Once Transparent Data Encryption is enabled on the database, you won’t be able to restore or move it to another server unless this same certificate has been installed.
  • Take a  backup of certificate. Keep good (and secure records) of the certificate and password.
  • Verify the TLS configurations by executing queries on SQL Server, which are mentioned in upcoming sections.


Create Master Key


We must first create the master key. It must be created in the master database, so as a precautionary measure, begin this statement with the USE MASTER command. The syntax of the command is:


USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD=’ProvideStringPassword’;
GO


Create Certificate Protected by Master Key


Once the master key is created along with the strong password (that you should remember or save in a secure location), we will go ahead and create the actual certificate. The syntax of command is:


CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO


Create Database Encryption Key


Now, we must utilize our USE command to switch to the database that we wish to encrypt. So, that we can create a connection or association between the certificate that we just created and the actual database. Then, we indicate the type of encryption algorithm we are going to use. In this case it will be AES_256 encryption. The syntax of command is:

USE TDE_TestDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO


Enable Encryption


Finally, we can enable encryption on our database by using the ALTER DATABASE command. The syntax of command is:


ALTER DATABASE TDE_TestDB
SET ENCRYPTION ON;
GO


Once the encryption is turned on, depending on the size of the database, it may take some time to complete. You can monitor the status by querying the sys.dm_database_encryption_keys

Backup Certificate


It’s important to backup the certificate you created and store it in a secure location. If the server ever goes down and you need to restore it elsewhere, you will have to import the certificate to the server. In certain environments, the DR servers are already stood up and on warm/hot standby, so it’s a good idea to just preemptively import the saved certificate to these servers. The syntax of command is:


BACKUP CERTIFICATE TDE_Cert
TO FILE = 'C:\temp\TDE_Cert'
WITH PRIVATE KEY (file='C:\temp\TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='ProvideStrongPasswordHere')


Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, when you restore these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available.


Restore Certificate


In order to restore the certificate, you will once again have to create a service master key on the secondary server.


USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='ProvideStrongPasswordHere';
GO


Once that is done, you must remember where you backed up the certificate and the encryption/decryption password.

USE MASTER
GO
CREATE CERTIFICATE TDECert
FROM FILE = 'C:\Temp\TDE_Cert'
WITH PRIVATE KEY (FILE = 'C:\TDECert_Key.pvk',
DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere' );


Something to note before applying TDE are its drawbacks which Transparent Data Encryption encrypts the underlying database files including the backups. You can’t just take the files and dump them onto another SQL Server without the appropriate encryption keys and certificates. It does NOT allow for granular user level encryption.

Verification of TDE under SQL Server


In order to verify if the TDE has been implemented on a specific database, use following queries:


1) Following query will return the databases list and ‘encryption_state =3 shows the database is actually TDE enabled.
Select * from sys.databases

2) Following query will return the list of certificates and the one that was created above can be looked in the table.
Select * from sys.certificates

3) Following query will return the databases encryption keys details.
Select * from sys.dm_database_encryption_keys

Limitations and Restrictions on TDE


The following operations are not allowed during initial database encryption, key change, or database decryption:


  • Dropping a file from a filegroup in the database
  • Dropping the database
  • Taking the database offline
  • Detaching a database
  • Transitioning a database or filegroup into a READ ONLY state


The operations which are not allowed while performing the processes like CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements are:

  • Dropping a file from a filegroup in the database
  • Dropping the database
  • Taking the database offline
  • Detaching a database
  • Transitioning a database or filegroup into a READ ONLY state
  • Using an ALTER DATABASE command
  • Starting a database or database file backup
  • Starting a database or database file restore
  • Creating a snapshot


The following operations or conditions will prevent the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements:


  • The database is read-only or has any read-only file groups.
  • An ALTER DATABASE command is executing.
  • Any data backup is running.
  • The database is in an offline or restore condition.
  • A snapshot is in progress.
  • Database maintenance tasks.
  • When creating database files, instant file initialization is not available when TDE is enabled.


In order to encrypt the database encryption key with an asymmetric key, the asymmetric key must reside on an extensible key management provider.


All these configurations are verified on SQL Server 2019 Standard Edition for now. TDE is only supported to specific SQL Server versions.



Enable Transparent Layer Security (TLS)


ADSS Web RA 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 ADSS Web RA 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.


To enable TLS configurations for ADSS Web RA 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 Enrolment


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 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). 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 on 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: ‎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 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 ADSS Web RA Connection String


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


Two parameters that are required to add under connection string to setup TLS connection for ADSS Web RA 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 ADSS Web RA Instance Connection String Over TLS


If an existing ADSS Web RA instance has to be configured over the TLS then connection string has to be updated, and it can be done using ADSS Web RA 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.