This page describes how to encrypt the data-files and the communication of the SQL Server.

...

The following example shows encryption of the yuuvisRAD database using a certificate named MyServerCert that's 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 an encryption scan. The scan reads each page from the data files into the buffer pool and then writes the encrypted pages back out to disk.
Starting with SQL Server 2019 this 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 - after adjusting the paths and passwords to 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 not recommended, at least for productive usage - you can do it 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 service-manager to use encryption, follow the below steps:

1. 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 the core-service again.
     
     
2. 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 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 the column "encrypt_option" is "TRUE" for all applicable connections.