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

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 aswell. 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 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 ALTER DATABASE <db_name> SET ENCRYPTION SUSPEND; and resumed with ALTER DATABASE <db_name> SET ENCRYPTION RESUME;

Communication encryption