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 it.
...
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.
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;
| Warning |
|---|
Backup files for databases that have TDE enabled are also encrypted with the database encryption key. As a result, when you restore these backups, the certificate that protects the database encryption key must be available. Therefore, in addition to backing up the database, make sure to maintain backups of the server certificates. Data loss results if the certificates are no longer available. For more information, see SQL Server Certificates and Asymmetric Keys. |
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.
...