MSSQL. Настройка шифрования TDE

Сегодня расскажем, как настроить шифрование TDE в базе данных MSSQL.

Для это нужно выполнит следующие действия:

• создание мастер ключа по паролю

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘парольная_фраза’;  
GO

• создание сертификата (новый). Имя и тему можно задать любые (в примере LocalServerCertificate и ‘Local Server Certificate’)

USE master;  
GO  
CREATE CERTIFICATE LocalServerCertificate WITH SUBJECT = ‘Local Server Certificate’;  
GO  

• шифрование базы данных 

USE [local-encrypt-db]
GO  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE LocalServerCertificate;  
GO  
ALTER DATABASE [local-encrypt-db]  
SET ENCRYPTION ON;  
GO  

где local-encrypt-db — это база данных, которую нужно зашифровать

• проверка что сертификат и ключ созданы

SELECT  [name], [principal_id], [algorithm_desc], [create_date]
from master.sys.symmetric_keys

• проверка что база данных зашифрована

SELECT *  
FROM sys.dm_database_encryption_keys  
WHERE encryption_state = 3; 

или

select [name], is_master_key_encrypted_by_server, is_encrypted
from master.sys.databases

• узнать ID базы данных

SELECT DB_ID(‘local-encrypt-db’)

• создание бэкапа сертификата и ключа

use master
GO

BACKUP CERTIFICATE LocalServerCertificate TO FILE = ‘/backup/certs/local-server-certificate.cert’  
    WITH PRIVATE KEY ( FILE = ‘/backup/certs/local-server-certificate.key’ ,   
    ENCRYPTION BY PASSWORD = ‘парольная_фраза’ );  
GO  

• создание бэкапа базы данных c шифрованием

DECLARE @filename NVARCHAR(256)
SET @filename = ‘/backup/local-encrypt-db_’ + FORMAT(GETUTCDATE(), ‘yyyy-MM-dd-HHmmss.bak’)

BACKUP DATABASE  [local-encrypt-db]  
TO DISK = @filename
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = LocalServerCertificate)

• загрузка сертификата и ключа из файлов

USE master;
GO
CREATE CERTIFICATE LocalServerCertificate
  FROM FILE = N’/backup/certs/local-server-certificate.cert’
  WITH PRIVATE KEY ( 
    FILE = N’/backup/certs/local-server-certificate.key’,
 DECRYPTION BY PASSWORD = ‘парольная_фраза’
  );
GO

• проверка, что сертификат и ключ установлены

SELECT  [name], [principal_id], [algorithm_desc], [create_date]
from master.sys.symmetric_keys

• восстановление базы данных из бэкапа

RESTORE DATABASE [local-encrypt-db-BACKUP] FROM DISK = N’/backup/local-encrypt-db_2019-03-16-133308.bak’ with REPLACE

Копирование сертификата и ключа в контейнер

docker cp /backup/certs/local-server-certificate.cert имя_контейнера:/var/opt/mssql/certs/local-server-certificate.cert
docker cp /backup/certs/local-server-certificate.key имя_контейнера:/var/opt/mssql/certs/local-server-certificate.key