Как зашифровать бэкапы и не поседеть при восстановлении

Тема внезапно стала мейнстримом: в датацентры иногда прилетает, и тогда очень хочется иметь копию не «где-то там», а на альтернативной площадке. И чтобы её никто не прочитал. Погнали.

Часть 1. Готовим сервер-источник (откуда снимаем бэкап)

1.1. Мастер-ключ базы данных

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ОченьДлинныйПароль';
-- чтобы SQL Server открывал ключ сам, без ручного OPEN MASTER KEY
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

1.2. Сертификат для шифрования бэкапов

USE master;
CREATE CERTIFICATE BackupEncryptCert
    WITH SUBJECT = 'Backup encryption';

1.3. Экспорт сертификата и приватного ключа

Файлы .cer и .pvk — ваша страховка на случай переезда. Без них зашифрованный бэкап — просто красивый файл.

BACKUP CERTIFICATE BackupEncryptCert
    TO FILE = N'F:\backup\BackupEncryptCert.cer' -- здесь и далее вымышленные пути
    WITH PRIVATE KEY (
        FILE = N'F:\backup\BackupEncryptCert_Key.pvk',
        ENCRYPTION BY PASSWORD = 'ЕщёОдинДлинныйПароль');

1.4. Сам бэкап — просто добавить ENCRYPTION

Копия пишется на сетевую шару резервного хранения (альтернативная площадка, доступна по сети из обоих датацентров). MEDIADESCRIPTION и MEDIANAME — чтобы через полгода было понятно, что это за медиасет и зачем его снимали.

BACKUP DATABASE [MyAppDB]
TO DISK = N'\\fileserver\Backup\MyAppDB\..._01.bak',
     DISK = N'\\fileserver\Backup\MyAppDB\..._02.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5,
     MEDIADESCRIPTION = N'Test copy to another site bypassing MSE',
     MEDIANAME = N'20261021_TEST',
     ENCRYPTION (ALGORITHM = AES_256,
                 SERVER CERTIFICATE = BackupEncryptCert);

AES_256 — максимум, что даёт SQL Server. Мелочь, а приятно.

Часть 2. Готовим сервер-приёмник (куда восстанавливаем) — с нуля

Предположим, это чистый SQL Server, на котором ещё ничего не настроено. Сертификата нет, мастер-ключа нет, файлы .cer и .pvk лежат в F:\backup.

2.1. Проверяем, что есть и чего нет

USE master;
SELECT name, key_length, create_date
FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##';   -- пусто? значит DMK нет

SELECT name, subject
FROM sys.certificates
WHERE name = 'BackupEncryptCert';            -- пусто? значит сертификата нет

2.2. Создаём мастер-ключ (если его нет)

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ОченьДлинныйПароль';

-- не забываем про автоматическое открытие
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

Пароль должен совпадать с тем, что использовался на источнике? Нет — DMK на каждом сервере свой. Важно, чтобы вы его помнили и хранили в сейфе. Он нужен, только если SMK окажется недоступен.

2.3. Импортируем сертификат из файлов .cer и .pvk

USE master;
CREATE CERTIFICATE BackupEncryptCert
    FROM FILE = N'F:\backup\BackupEncryptCert.cer'
    WITH PRIVATE KEY (
        FILE = N'F:\backup\BackupEncryptCert_Key.pvk',
        DECRYPTION BY PASSWORD = 'ЕщёОдинДлинныйПароль');

Пароль ЕщёОдинДлинныйПароль — тот самый, которым вы защищали приватный ключ при экспорте. Без него SQL Server не сможет расшифровать .pvk, и импорт упадёт.

2.4. Проверяем, что сертификат встал и имеет приватный ключ

SELECT name, subject, thumbprint, pvt_key_encryption_type_desc
FROM sys.certificates
WHERE name = 'BackupEncryptCert';

Ждём pvt_key_encryption_type_desc = ENCRYPTED_BY_MASTER_KEY. Запоминаем thumbprint — он должен совпасть с EncryptorThumbprint в бэкапе.

2.5. Проверяем, что бэкап читается и зашифрован нашим сертификатом

RESTORE HEADERONLY
FROM DISK = N'\\fileserver\Backup\MyAppDB\..._01.bak',
     DISK = N'\\fileserver\Backup\MyAppDB\..._02.bak';

В выводе EncryptorThumbprint должен быть не NULL и совпадать с thumbprint сертификата из 2.4. Если не совпадает — вы импортировали «не тот» сертификат, и дальше идти бессмысленно.

2.6. Смотрим логические имена файлов (для MOVE)

RESTORE FILELISTONLY
FROM DISK = N'\\fileserver\Backup\MyAppDB\..._01.bak',
     DISK = N'\\fileserver\Backup\MyAppDB\..._02.bak';

2.7. Восстановление поверх существующей базы (или с нуля)

Стенд MyAppDB-STG; данные кладём на локальный диск данных, лог — на диск логов.

ALTER DATABASE [MyAppDB-STG] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE [MyAppDB-STG] 
USE master
RESTORE DATABASE [MyAppDB-STG]
FROM DISK = N'..._01.bak', DISK = N'..._02.bak', DISK = N'..._03.bak'
WITH RECOVERY, REPLACE,
     MOVE N'MyAppDB'     TO N'F:\data\MyAppDB-STG.mdf',
     MOVE N'MyAppDB_log' TO N'G:\log\MyAppDB-STG_log.ldf',
     STATS = 5;

ALTER DATABASE [MyAppDB-STG] SET MULTI_USER;

Проверить, что копия реально зашифрована (повторно, уже после восстановления)

RESTORE HEADERONLY FROM DISK = N'..._01.bak', DISK = N'..._02.bak';
-- EncryptorThumbprint должен быть не NULL и совпадать с thumbprint сертификата

А теперь серьёзно.

Что реально важно

1. Пароли. Три штуки: пароль DMK, пароль приватного ключа и… всё, что вы придумали «по-быстрому» и забыли. Положите их в менеджер паролей / сейф / Vault. И .cer + .pvk тоже. Без них бэкап — это красивый файл на XXX ТБ, который никто не восстановит. Проверьте, что доступ к сейфу есть у дежурной смены, а не только у уволенного год назад администратора.
2. .cer и .pvk храните отдельно от бэкапов. Иначе атакующий, добравшийся до шары с бэкапами, получит и ключ. Тогда это не шифрование, а карго-культ.
3. На приёмнике DMK создаётся свой. Пароль от DMK источника вам не нужен — нужен только пароль от приватного ключа сертификата. Не перепутайте.
4. FCI ≠ Always On. В кластере WSFC база master (а значит, мастер-ключ и сертификат) переезжает вместе с экземпляром — второй узел отдельно настраивать не нужно. С Always On — нужно.
5. Проверяйте на восстановление, а не только на «бэкап создался». RESTORE VERIFYONLY — минимум. Раз в квартал — тестовый restore на отдельный сервер.
6. COPY_ONLY хорош для тестов и разовых копий на другой сайт, но не для регулярной цепочки — иначе дифференциальные бэкапы будут «удивлены».
7. Сертификат лучше назвать осмысленно и одинаково на всех площадках (у нас BackupEncryptCert — так и живём).
8. Убедитесь, что сервисная учётка SQL Server на принимающей стороне имеет доступ к сетевой шаре с бэкапами. «Access is denied» в 3 часа ночи — это отдельный жанр.

И да — если вы прочитали это, чтобы «потом настроить», настройте сейчас. Это может не ждать до понедельника...