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

Data

...

File

...

Encryption

As described in https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15 the 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 aswellas well. The procedure is as follows:

...

The following example shows the encryption of the yuuvisRAD database the yuuvis® RAD database using a certificate named MyServerCert that's 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 do carry out an encryption scan. The This scan reads each page from the data files into the buffer pool and then writes the encrypted pages back out to a disk.
The progress of the encryption scan can be monitored by the following statement:

SELECTDB_NAME(database_id)ASDatabaseName,encryption_state,
encryption_state_desc=
CASEencryption_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  
FROMsys.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 ALTERDATABASE <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 - 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 MASTERKEY
    TOFILE = '\\SECURE_SERVER\keys\service_master_key'
    ENCRYPTION BYPASSWORD = '<UseStrongPasswordHere>'
GO
BACKUP MASTERKEY
    TOFILE = '\\SECURE_SERVER\keys\master_key'
    ENCRYPTION BYPASSWORD = '<UseStrongPasswordHere>'
GO
BACKUP CERTIFICATE MyServerCert
    TOFILE = '\\SECURE_SERVER\cert\MyServerCert.cer'
    WITHPRIVATEKEY (
                                     FILE = '\\SECURE_SERVER\cert\MyServerCert.key',
                                     ENCRYPTION BYPASSWORD = '<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 the 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 - although , which is not recommended , at least for productive usage - use, you can do it 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.

...

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 yuuvis® RAD core-service and yuuvis® RAD service-manager to use encryption, follow the below 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:

     Code Block
     language xml title 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>

     
     

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

     
     

   • Save the file and start restart the core-service again.
     
     
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:

     Code Block
     language yml title application-dbs.yml snippet
     spring.datasource.url: jdbc:sqlserver://${enaio.db.server};database=${enaio.db.name};encrypt=true;trustServerCertificate=false;

     
     

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

     
     

   • Save the file and start restart the service-manager again.
     
     
3. To check if ecryption is used, you can execute "SELECT * FROM sys.dm_exec_connections" in the sql server management studio and check that 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 in 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 4500 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 itself 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 it fits , this matches the 8% impact of TDE + communication.

It also fits with 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.

...