This page describes how to encrypt the data files and the communication of the Microsoft SQL Server, and the performance impact to be expected by this.

Data File Encryption

As described in https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15 Microsoft SQL Server supports "Transparent Data Encryption" or TDE to encrypt all data at rest, i.e., data files and log files. This is done on a per page base where the data is encrypted before writing it to the disk and decrypted when loading the page into memory. This can be setup at database creation time or for an existing database as well. The procedure is as follows:

1. Create a master key.

2. Create or obtain a certificate protected by the master key.

3. Create a database encryption key and protect it by using the certificate.

4. Set the database to use encryption.

Source: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15

The following example shows the encryption of the yuuvis® RAD database using a certificate named MyServerCert which is installed on the server.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
GO
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
GO
USE yuuvisRAD;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE yuuvisRAD SET ENCRYPTION ON;
GO

To enable TDE on a database, SQL Server must carry out an encryption scan. This scan reads each page from the data files into the buffer pool and then writes the encrypted pages back out to disk.
The progress of the encryption scan can be monitored by the following statement:

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0'  THEN  'No database encryption key present, no encryption'
         WHEN '1'  THEN  'Unencrypted'
         WHEN '2'  THEN  'Encryption in progress'
         WHEN '3'  THEN  'Encrypted'
         WHEN '4'  THEN  'Key change in progress'
         WHEN '5'  THEN  'Decryption in progress'
         WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
         ELSE 'No Status'
         END,
percent_complete,encryptor_thumbprint, encryptor_type  
FROM sys.dm_database_encryption_keys
source: https://www.sqlshack.com/how-to-monitor-and-manage-transparent-data-encryption-tde-in-sql-server/

Starting with SQL Server 2019 the encryption scan can be paused with ALTER DATABASE <db_name> SET ENCRYPTION SUSPEND; and resumed with ALTER DATABASE <db_name> SET ENCRYPTION RESUME;

To backup the server certificate and its private key execute the following statements after adjusting the paths and passwords to match your individual configuration (see https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/back-up-the-service-master-key?view=sql-server-ver15):

USE master;
GO
BACKUP SERVICE MASTER KEY
    TO FILE = '\\SECURE_SERVER\keys\service_master_key'
    ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'
GO
BACKUP MASTER KEY
    TO FILE = '\\SECURE_SERVER\keys\master_key'
    ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'
GO
BACKUP CERTIFICATE MyServerCert
    TO FILE = '\\SECURE_SERVER\cert\MyServerCert.cer'
    WITH PRIVATE KEY (
                                     FILE = '\\SECURE_SERVER\cert\MyServerCert.key',
                                     ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'
                                   )
GO

Communication Encryption

As described in https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15 Micrososft SQL Server supports communication encryption using SSL (deprecated), TLS, and IPSec. Here, we show how to setup TLS encryption. 

If you want to create and use a self-signed certificate, which is not recommended at least for productive use, you can do this as described in:

• https://docs.microsoft.com/en-us/powershell/module/pki/new-selfsignedcertificate?view=windowsserver2019-ps OR
• Configuring HTTPS for the Gateway OR
• https://www.linode.com/docs/guides/create-a-self-signed-tls-certificate/ 
• Or you can use the Keystore Explorer (https://keystore-explorer.org/) if you prefer a GUI.

Import the certificate to the windows certificate store as described below (taken from https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15):

1. On the Start menu, click Run, and in the Open box, type MMC and click OK.

2. In the MMC console, on the File menu, click Add/Remove Snap-in.

3. In the Add/Remove Snap-in dialog box, click Add.

4. In the Add Standalone Snap-in dialog box, click Certificates, click Add.

5. In the Certificates snap-in dialog box, click Computer account, and then click Finish.

6. In the Add Standalone Snap-in dialog box, click Close.

7. In the Add/Remove Snap-in dialog box, click OK.

8. In the Certificates snap-in, expand Certificates, expand Personal, and then right-click Certificates, point to All Tasks, and then click Import.

9. Right-click the imported certificate, point to All Tasks, and then click Manage Private Keys. In the Security dialog box, add read permission for the user account used by the SQL Server service account (usually this is "NT Service\MSSQLSERVER").

10. Complete the Certificate Import Wizard to add a certificate to the computer, and close the MMC console. 
    (To install a certificate in a failover cluster instance configuration or in an Always On Availability Group configuration see the above link.)

The SQL Server is now ready to accept encrypted connections. To configure the yuuvis® RAD core-service and yuuvis® RAD service-manager to use encryption, follow these steps:

1. yuuvis® RAD core-service
   • Stop the core-service service.
   • Open the file <core-service>\standalone\configuratation\standalone-full-ha.xml
   • Navigate to the line of the JDBC connection-url for the SQL Server

   • Append "encrypt=true;trustServerCertificate=false;" to the end of the connection string. It should now look like this:

     standalone-full-ha.xml snippet

     <datasource jndi-name="java:/JASDB" pool-name="jasdb-ds" enabled="true" use-java-context="true" statistics-enabled="true">
     <connection-url>jdbc:sqlserver://127.0.0.1;databaseName=yuuvisRAD;sendStringParametersAsUnicode=false;lastupdatecount=true;encrypt=true;trustServerCertificate=false;</connection-url>

     Self-signed certificates

     If you are using a self-signed certificate, change the value of "trustServerCertificate" from "false" to "true".

   • Save the file and restart the core-service.
     
     
2. yuuvis® RAD service-manager
   • Stop the service-manager service.
   • Open the file <service-manager\config\application-dbs.yml

   • Append "encrypt=true;trustServerCertificate=false;" to the end of the "spring.datasource.url" parameter value. It should now look like this:

     application-dbs.yml snippet

     spring.datasource.url: jdbc:sqlserver://${enaio.db.server};database=${enaio.db.name};encrypt=true;trustServerCertificate=false;

     Self-signed certificates

     If you are using a self-signed certificate, change the value of "trustServerCertificate" from "false" to "true".

   • Save the file and restart the service-manager.
     
     
3. To check if ecryption is used, you can execute "SELECT * FROM sys.dm_exec_connections" in the sql server management studio and check whether the column "encrypt_option" is "TRUE" for all applicable connections.

Performance Impact

The encryption and decryption of the data and the communication add another step to the processing pipeline and thus come at some performance costs. Since the encrypted data has the same size as the plain data, I/O and memory costs will not be affected but the CPU load will definitely increase. To get an impression of the impact, we tested the following scenarios by creating 4,500 objects in a row:

• unencrypted
• communication encryption only (TLS)
• TDE + communication encryption

As expected, I/O and memory did not show any change, but the CPU load did increase. For the communication encryption only scenario this was an increase of about 3% and for the TDE + communication encryption scenario about 8%.

Microsoft states that TDE comes at about 3-5% of performance costs (see https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/security/transparent-data-encryption#:~:text=TDE%20has%20an%20estimated%20performance,this%20topic%20for%20more%20details). If we add our measured 3% for communication to that, this matches the 8% impact of TDE + communication.

It also matches the results of the more in-depth tests done by https://www.mssqltips.com/sqlservertip/2641/sql-server-transparent-data-encryption-tde-performance-comparison/. They state that in the worst case TDE can come at 10-20% performance costs but in real life scenarios 3-10% are experienced.