Шифрование резервных копий в SQL Server 2014

На этот раз мы поговорим еще об одном улучшении, которое SQL Server 2014 предоставляет в плане создания резервных копий, а именно — о возможности их полноценного шифрования. Возможность защитить резервную копию паролем, чтобы с нее не могли восстановиться неположенные люди, существовала с незапамятных времен, и те, кто достаточно долго имеет дело с SQL Server, должны помнить опцию WITH PASSWORD для команды BACKUP. Однако этот способ не обеспечивал стойкую защиту, и, как отмечалось на mssqltips, Although this does add a level of security if someone really wants to crack the passwords they will find a way, so look for additional ways to secure your data. На практике для защиты резервных копий применялось появившееся в SQL Server 2008 TDE, т.е. база данных прозрачно шифровалась, прежде чем сделать из нее бэкап. Поэтому начиная с SQL Server 2012, параметры PASSWORD и MEDIAPASSWORD не используются при создании резервных копий. Восстановление резервных копий, созданных с применением пароля, остается возможным.

Тем не менее шифрование данных и шифрование резервных копий — это два разных по своему назначению сценария. Очевидно, что при отчуждении резервной копии правилом хорошего тона является ее защитить. Например, если мы переносим базу в другой ЦОД, чтобы исключить утечку в процессе передачи по каналам связи или еще как-либо. Однако шифрование влечет накладные расходы, и если база данных надежно хранится в локальном датацентре, зачем ее шифровать только для того, чтобы сделать бэкап? К счастью, в SQL Server 2014 это стали два независимых процесса. Аналогично шифрованию данных резервную копию можно зашифровать на основе сертификата или асимметричного ключа. Поддерживаются алгоритмы шифрования AES 128, AES 192, AES 256 и Triple DES.

В качестве иллюстрации я создам зашифрованную резервную копию любимой базы AdventureWorks на локальном SQL Server 2014 CTP2 и восстановлюсь с нее в облачной виртуалке.
Для защиты резервной копии требуется создать шифратор: асимметричный ключ или сертификат, — каковой затем передать на целевой SQL Server, где будет происходить восстановление. Для этого шифратор нужно экспортировать из исходного экземпляра SQL Server и импортировать на целевой. С сертификатами в этом плане проблем нет. С асимметричными ключами сложнее. Учитывая, что команды BACKUP ASYMMETRIC KEY до сих пор не появилось, и создать дубликат асимметричного ключа в отличие от симметричного тоже нельзя, единственный разумный способ видится в том, чтобы создать асимметричный ключ вне SQL Server, например, при помощи утилиты sn.exe, затащить его внутрь, как CREATE ASYMMETRIC KEY… FROM FILE = '....snk', зашифровать им бэкап на экземпляре-исходнике, из этого же snk-файла создать асимметричный ключ на экземпляре-назначении, на котором и восстановить зашифрованный бэкап. Чтобы не геморроиться с асимметричными ключами, в данном примере будем использовать сертификат, поскольку идейно это та же пара открытый/закрытый ключ.

Создадим серверный сертификат, который будет использоваться для шифрования бэкапа.

use master
if exists (select 1 from sys.certificates where name = 'СертификатДляБэкапа') drop certificate СертификатДляБэкапа
create certificate СертификатДляБэкапа with subject = 'Это действительно сертификат для бэкапа'

Скрипт 1

Поскольку никакого ENCRYPTION BY мы не указали, это означает, что сертификат будет защищен мастер-ключом базы, что, собственно, и требуется. Только сертификаты, подписаные мастер-ключом, годны для шифрования бэкапов. Если защитить сертификат, например, паролем (ENCRYPTION BY PASSWORD = 'Оч.сложный пароль'),
при попытке зашифровать им бэкап выскочит ошибка Cannot use certificate 'TestCert', because its private key is not present or it is not protected by the database master key.
Зашифрованный бэкап, как и обычный, можно создавать традиционно на диск или в Azure Storage. Чтобы не заморачиваться с передачей файла бэкапа, воспользуемся вторым способом, который мы разбирали в заметке <a href=« habrahabr.ru/company/microsoft/blog/201568/>Создание резервных копий БД SQL Server 2014 CTP2 в Windows Azure.

if exists (select 1 from sys.credentials where name = 'КреденцияДляАзуровскогоСториджа') drop credential КреденцияДляАзуровскогоСториджа
create credential КреденцияДляАзуровскогоСториджа with identity= 'bakstorage'
, secret = '<первичный или вторичный ключ доступа к учетной записи хранения, посмотреть которые можно в ее конфигурации>'

backup database AdventureWorks to url = 'http://bakstorage.blob.core.windows.net/container1/AdventureWorks2.bak'
with credential = 'КреденцияДляАзуровскогоСториджа'
, format, compression, stats = 10
, encryption (algorithm = aes_256, server certificate = СертификатДляБэкапа)

Скрипт 2


Увеличить
Рис.1

Если вы уже делали бэкап с тем же именем в тот же контейнер, вы можете получить при этом ошибку (412) There is currently a lease on the blob and no lease ID was specified in the request… Это происходит потому, что при создании или восстановлении резервной копии Windows Azure выдает SQL Server бесконечную аренду (lease) для блокировки монопольного доступа к блобу. После успешного завершения процесса резервного копирования или восстановления аренда снимается. Но если оно заканчивается неудачей или происходит сбой с сетью или еще что-то пошло не так, аренда остается висеть, препятствуя перезаписи бэкапного блоба или его удаления. Скрипт PowerShell для удаления блоба с активной арендой приводится здесь. Я поступлю проще. Поскольку в содержащем бэкап контейнере больше ничего нет, я удалю и пересоздам контейнер. Если контейнер пересоздается с тем же именем, необходимо иметь в виду, что Windows Azure потребуется пара минут сообразить, что имя освободилось.

То же можно выполнить в графическом интерфейсе SSMS:


Увеличить
Рис.2


Увеличить
Рис.3

По выполнении команды Скрипт 2 в заказанном контейнере образуется блоб с бэкапом:


Увеличить
Рис.4

Разумеется, шифрование бэкапов полностью применимо при создании резервной копии БД не в Облако, а в традиционный локальный файл. Для этого первую строчку команды backup database (Скрипт 2) следует изменить на

backup database AdventureWorks to disk = 'c:\Temp\AdventureWorks.bak' ...

Этот файл должен увидеться с того экземпляра SQL Server, на котором мы его планируем восстановить. В нашем случае это SQL Server на облачной виртуалке. Можно скопировать на нее файл с резервной копией, либо выложить его в Azure Storage, разделив backup database… to url на два шага: backup database… to disk с последующей загрузкой бэкапа в блобовский контейнер. Загрузку можно сделать руками из Visual Studio 2012 | 2013 (Server Explorer), на которую предварительно требуется установить Azure SDK.


Увеличить
Рис.5

Однако необходимо иметь в виду, что команда restore database… from url (непосредственное восстановление базы из Azure Storage) может производиться только если данный бэкап тоже производился в Storage. Если бэкап делался на диск, а затем переносился как облачный блоб, то и на сервере назначения его необходимо будет сначала превратить в файл и восстанавливаться как restore database… from disk.

Теперь независимо от способа создания резервной копии заходим на SQL Server назначения, в нашем случае установленный на виртуальной машине в Windows Azure. Это можно сделать через удаленный рабочий стол или подключиться к нему из локальной SSMS, как описывалось здесь.


Рис.6

Где 5555 — публичный ТСР-порт в конечной точке облачной виртуалки, соответствующий 1433


Увеличить
Рис.7

предварительно открытому в Windows Firewall облачной виртуалки


Увеличить
Рис.8

Повторяем на нем создание креденции для азуровского сториджа аналогично первой части Скрипт 2:

if exists (select 1 from sys.credentials where name = N'КреденцияДляАзуровскогоСториджа') drop credential КреденцияДляАзуровскогоСториджа
create credential КреденцияДляАзуровскогоСториджа with identity= 'bakstorage'   ...

и пытаемся восстановиться

restore database AdventureWorks from url = 'http://bakstorage.blob.core.windows.net/container1/AdventureWorks.bak'
with replace, stats = 10, credential = 'AzureStorageCredential'

Скрипт 4

Напомню, что если этот бэкап делался на диск, а потом загружался в Azure Storage, его необходимо опять представить в виде bak-файла и выполнять restore database AdventureWorks from disk.
В обоих случаях получаем, натурально, отлуп, поскольку бэкап зашифрован
Msg 33111, Level 16, State 3, Line 5 Cannot find server certificate with thumbprint '0х...'. Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE is terminating abnormally.

Кроме бэкапа на сервер назначения требуется передать секрет, при помощи которого он шифровался. Для сертификатов в отличие от асимметричных ключей предусмотрены команды backup/restore certificate. Как и в случае TDE, сертификат бэкапа на исходном экземпляре нужно экспортировать вместе с закрытым ключом, иначе выскочит ошибка:
Msg 15507, Level 16, State 30, Line 5 A key required by this operation appears to be corrupted. Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE is terminating abnormally.

backup certificate СертификатДляБэкапа to file = 'c:\Temp\BackupCert.cer'
with private key (file = 'c:\Temp\BackupCert.pvk', encryption by password = 'Abra@Cadabra')

Скрипт 4

передаем файлы .cer и .pvk на сервер назначения и создаем на нем сертификат для восстановления из бэкапа. Поскольку виртуалка свежая, предварительно требуется создать мастер-ключ БД master. Пароль, которым он защищается, не имеет ничего общего с паролями мастер-ключа на исходном сервере. Чтобы не отвлекаться на рассказ, какие ключи/сертификаты и как передавать с SQL Server на SQL Server при передаче защищенного контента, рекомендую статью Migrating SQL Server Databases that use Database Master Keys.

use master
create master key encryption by password = 'Passw0rd1'

if exists (select 1 from sys.certificates where name = N'СертификатДляБэкапа') drop certificate СертификатДляБэкапа
create certificate СертификатДляБэкапа from file = 'c:\Temp\BackupCert.cer'
with private key (file = 'c:\Temp\BackupCert.pvk', decryption by password = 'Abra@Cadabra')

Скрипт 5

После чего повторяем команду восстановления базы и видим, что теперь она завершается успешно:

restore database AdventureWorks from disk = 'd:\Temp\AdventureWorks.bak'
with move 'AdventureWorks2012_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdvnetureWorks_Data.mdf',
move 'AdventureWorks2012_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdvnetureWorks_Log.ldf',
replace, stats = 10

Скрипт 6


Увеличить
Рис.9