Перенос базы с защитой TDE на другой SQL Server

Область применения: SQL Server

В этой статье описывается, как защитить базу данных с помощью прозрачного шифрования данных (TDE), а затем переместить базу данных в другой экземпляр SQL Server с помощью SQL Server Management Studio или Transact-SQL. Функция прозрачного шифрования данных выполняет шифрование и дешифрование ввода-вывода в реальном времени для файлов данных и журналов. При шифровании используется ключ шифрования базы данных (DEK), который хранится в загрузочной записи базы данных, где можно получить к нему доступ при восстановлении. Ключ шифрования базы данных является симметричным ключом, защищенным сертификатом, который хранится в базе данных master на сервере, или асимметричным ключом, защищенным модулем расширенного управления ключами.

Ограничения

• В случае перемещения базы данных, защищаемой прозрачным шифрованием, также необходимо переместить сертификат или асимметричный ключ, который служит для открытия ключа шифрования базы данных. Сертификат или асимметричный ключ должны быть установлены в master базе данных целевого сервера, чтобы SQL Server смог получить доступ к файлам базы данных. Дополнительные сведения см. в разделе "Прозрачное шифрование данных" (TDE).

• Для восстановления сертификата необходимо хранить копии файла сертификата и файла закрытого ключа. Пароль для закрытого ключа не должен совпадать с паролем главного ключа базы данных.

• SQL Server хранит файлы, созданные здесь C:\Program Files\Microsoft SQL Server\MSSQL<xx>.MSSQLSERVER\MSSQL\DATA по умолчанию, где <xx> находится номер версии.

Разрешения

• Требуется CONTROL DATABASE разрешение на master создание главного ключа базы данных.

• Требуется CREATE CERTIFICATE разрешение для master базы данных для создания сертификата, который защищает deK.

• Требуется разрешение CONTROL DATABASE в зашифрованной базе данных и разрешение VIEW DEFINITION на сертификат или асимметричный ключ, используемый для шифрования ключа шифрования базы данных.

Создание базы данных, защищенной прозрачное шифрование данных

В следующих процедурах показано, как создать базу данных, защищенную с помощью TDE, с использованием SQL Server Management Studio и Transact-SQL.

Использование SQL Server Management Studio

1. Создайте главный ключ базы данных и сертификат в master базе данных. Дополнительные сведения см. в разделе "Использование Transact-SQL " далее в этой статье.

2. Создайте резервную копию сертификата сервера в master базе данных. Дополнительные сведения см. в разделе "Использование Transact-SQL " далее в этой статье.

3. В обозревателе объектов щелкните правой кнопкой мыши папку Базы данных и выберите Создать базу данных.

4. В диалоговом окне Создание базы данных в поле Имя базы данных введите имя новой базы данных.

5. В поле Владелец введите имя владельца новой базы данных. Кроме того, выберите многоточие (...), чтобы открыть диалоговое окно "Выбор владельца базы данных". Дополнительные сведения о создании базы данных см. в статье "Создание базы данных".

6. В обозреватель объектов выберите знак "плюс", чтобы развернуть папку "Базы данных".

7. Щелкните правой кнопкой мыши созданную базу данных, выберите Задачи, затем Управление шифрованием базы данных.

   В диалоговом окне Управление шифрованием базы данных доступны следующие параметры.

   Алгоритм шифрования
   Отображает или устанавливает алгоритм для шифрования базы данных. Алгоритм шифрования по умолчанию —AES128 . Это поле не может быть пустым. Дополнительные сведения о алгоритмах шифрования см. в разделе "Выбор алгоритма шифрования".

   Использовать сертификат сервера
   Задает шифрование с защитой сертификатом. Выберите его из списка. Если у вас нет VIEW DEFINITION разрешения на сертификаты сервера, этот список пуст. Если выбран метод шифрования сертификата, это значение не может быть пустым. Дополнительные сведения о сертификатах см. в разделе SQL Server Certificates and Asymmetric Keys.

   Использовать асимметричный ключ сервера
   Задает шифрование с защитой асимметричным ключом. Отображаются только доступные асимметричные ключи. Только асимметричный ключ, защищенный модулем расширенного управления ключами, позволяет шифровать базу данных с помощью прозрачного шифрования данных.

   Задать шифрование базы данных для
   Переключает базу данных между включенным (флажок установлен) или выключенным (флажок снят) режимом прозрачного шифрования данных.

8. По завершении выберите ОК.

Использование Transact-SQL

1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.

2. На стандартной панели выберите пункт Создать запрос.

3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.

   -- Create a database master key and a certificate in the master database.
   USE master;
   GO
   
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
   GO
   
   CREATE CERTIFICATE TestSQLServerCert
       WITH SUBJECT = 'Certificate to protect TDE key'
   GO
   
   -- Create a backup of the server certificate in the master database.
   -- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server
   -- (C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA).
   BACKUP CERTIFICATE TestSQLServerCert TO FILE = 'TestSQLServerCert'
   WITH PRIVATE KEY (
       FILE = 'SQLPrivateKeyFile',
       ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
   );
   GO
   
   -- Create a database to be protected by TDE.
   CREATE DATABASE CustRecords;
   GO
   
   -- Switch to the new database.
   -- Create a database encryption key, that is protected by the server certificate in the master database.
   -- Alter the new database to encrypt the database using TDE.
   USE CustRecords;
   GO
   
   CREATE DATABASE ENCRYPTION KEY
       WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;
   GO
   
   ALTER DATABASE CustRecords
   SET ENCRYPTION ON;
   GO
   

Дополнительные сведения см. в разделе:

• CREATE MASTER KEY (Transact-SQL)
• CREATE CERTIFICATE (Transact-SQL)
• СЕРТИФИКАТ BACKUP (Transact-SQL)
• CREATE DATABASE
• CREATE DATABASE ENCRYPTION KEY (Transact-SQL)
• ALTER DATABASE (Transact-SQL)

Перемещение базы данных, защищенной прозрачное шифрование данных

В следующих процедурах показано, как переместить базу данных, защищенную с помощью TDE, с использованием SQL Server Management Studio и Transact-SQL.

Использование SQL Server Management Studio

1. В обозреватель объектов щелкните правой кнопкой мыши базу данных, зашифрованную ранее, наведите указатель на задачи и выберите "Отсоединить...".

   В диалоговом окне Отсоединение базы данных доступны следующие параметры.

   Базы данных для отсоединения
   Перечисляет базы данных для отсоединения.

   Имя базы данных
   Отображает имя базы данных для отсоединения.

   Удалить соединения
   Завершить соединения с указанной базой данных.

Обновить статистику
По умолчанию при отсоединении сохраняется устаревшая статистика оптимизации. Чтобы обновить ее, установите этот флажок.

Сохранять полнотекстовые каталоги
По умолчанию операция отсоединения сохраняет связанные с базой данных полнотекстовые каталоги. Для удаления этих каталогов сбросьте флажок Сохранять полнотекстовые каталоги . Этот параметр отображается только при обновлении базы данных из SQL Server 2005 (9.x).

Состояние
Отображает одно из следующих состояний: Готов или Не готов.

Сообщение
Столбец Сообщение может отображать сведения о базе данных следующим образом:

• Если база данных участвует в репликации, то ее Состояние имеет значение Не готово , а в столбце Сообщение отображается строка База данных реплицирована.

• Если база данных имеет одно или несколько активных подключений, состояние не готово, а столбец сообщения отображает <number_of_active_connections>активные подключения, например 1 активных подключений. Прежде чем можно будет отсоединить базу данных, необходимо отключить активные соединений, выбрав команду Удалить соединения.

Чтобы получить сведения о сообщении, откройте монитор активности, щелкнув текст с гиперссылкой.

1. Нажмите ОК.

2. В проводнике Windows переместите или скопируйте файлы базы данных с исходного сервера в то же расположение на целевом сервере.

3. В проводнике Windows переместите или скопируйте резервную копию сертификата сервера и файла закрытого ключа с исходного сервера в то же расположение на целевом сервере.

4. Создайте главный ключ базы данных в целевом экземпляре SQL Server. Дополнительные сведения см. в разделе "Использование Transact-SQL " далее в этой статье.

5. Повторно создайте сертификат сервера с помощью файла резервной копии исходного сертификата сервера. Дополнительные сведения см. в разделе "Использование Transact-SQL " далее в этой статье.

6. В обозреватель объектов в SQL Server Management Studio щелкните правой кнопкой мыши папку "Базы данных" и выберите "Подключить...".

7. В диалоговом окне "Присоединение баз данных" в разделе "Базы данных для присоединения" нажмите кнопку "Добавить".

8. В диалоговом окне "Поиск файлов базы данных " server_name" выберите файл базы данных для подключения к новому серверу и нажмите кнопку "ОК".

   В диалоговом окне Присоединение базы данных доступны следующие параметры.

   Базы данных для присоединения
   Отобразятся сведения о выбранных базах данных.

   <без заголовка столбца>
   Отображается значок, указывающий на состояние операции присоединения. Возможные варианты значков перечислены в описании Состояние ниже.

   Расположение файла MDF
   Отображается путь и имя выбранного MDF-файла.

   Имя базы данных
   Отображается имя базы данных.

   Присоединить как
   Необязательный параметр, указывает другое имя, под которым присоединяется база данных.

   Ответственное лицо
   Предоставляет раскрывающийся список возможных владельцев баз данных, из которых можно выбрать другого владельца.

   Состояние
   Отображается состояние базы данных в соответствии со следующей таблицей.

Сообщение
Отображается пустое сообщение или гиперссылка «Файл не найден».

Прибавить
Найдите необходимые основные файлы базы данных. Если пользователь выбирает mdf-файл, необходимые сведения автоматически вводятся в соответствующие поля сетки Базы данных для присоединения .

Удалить
Удаляет выбранный файл из сетки Базы данных для присоединения .

"<database_name>" сведения о базе данных
Отображаются имена файлов, которые необходимо присоединить. Чтобы проверить или изменить путь к файлу, нажмите кнопку Обзор (…).

Имя исходного файла
Отображается имя присоединенного файла, принадлежащего базе данных.

Тип файла
Указывает тип файла, данных или журнала.

Текущий путь к файлу
Отображается путь к выбранному файлу базы данных. Путь может быть изменен вручную.

Сообщение
Отображается пустое сообщение или гиперссылка "Файл не найден".

Использование Transact-SQL

1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.

2. На стандартной панели выберите пункт Создать запрос.

3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.

   -- Detach the TDE protected database from the source server.
   USE master;
   GO
   
   EXEC master.dbo.sp_detach_db @dbname = N'CustRecords';
   GO
   
   -- Move or copy the database files from the source server to the same location on the destination server.
   -- Move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server.
   -- Create a database master key on the destination instance of SQL Server.
   USE master;
   GO
   
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
   GO
   
   -- Recreate the server certificate by using the original server certificate backup file.
   -- The password must be the same as the password that was used when the backup was created.
   CREATE CERTIFICATE TestSQLServerCert
   FROM FILE = 'TestSQLServerCert'
   WITH PRIVATE KEY (
       FILE = 'SQLPrivateKeyFile',
       DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
   );
   GO
   
   -- Attach the database that is being moved.
   -- The path of the database files must be the location where you have stored the database files.
   CREATE DATABASE [CustRecords] ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf'),
       (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF')
   FOR ATTACH;
   GO
   

Дополнительные сведения см. в разделе:

• CREATE MASTER KEY (Transact-SQL)
• CREATE CERTIFICATE (Transact-SQL)
• CREATE DATABASE

Связанный контент

• Отсоединение базы данных и подключение (SQL Server)
• Прозрачное шифрование данных в Базе данных SQL Azure
• sp_detach_db (Transact-SQL)