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

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

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

Ограничения

• При перемещении базы данных, защищенной прозрачным шифрованием, также необходимо переместить сертификат или асимметричный ключ, который служит для открытия DEK. Сертификат или асимметричный ключ должны быть установлены в 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.

   • Использовать асимметричный ключ сервера

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

   • Включить шифрование базы данных

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

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 = '<password>';
   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 = '<password>');
   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 ENCRYPTION KEY (Transact-SQL)
• Изменить базу данных (Transact-SQL)

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

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

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

1. Отсоедините базу данных.

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

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

   • Базы данных для отсоединения

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

   • Имя базы данных

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

   • Удалить соединения

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

     Примечание.

     Невозможно отключить базу данных с активными подключениями.

   • Обновить статистику

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

   • Сохранять полнотекстовые каталоги

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

   • Состояние

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

   • Сообщение

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

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

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

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

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

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

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

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

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

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

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

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

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

   • Базы данных для присоединения

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

   • <без заголовка столбца>

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

   • Расположение файла MDF

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

   • Имя базы данных

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

   • Присоединить как

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

   • Ответственное лицо

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

   • Состояние

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

     Значок / Икона	Текст состояния	Описание
     (Нет значка)	(Нет текста)	Операция присоединения не была запущена или может находиться в ожидании для этого объекта. Это состояние по умолчанию при открытии диалогового окна.
     Зеленый, указывающий направо треугольник	В процессе	Операция подключения была запущена, но она не завершена.
     Зеленый флажок	Успех	Объект был успешно присоединен.
     Красный кружок с белым крестом внутри	Ошибка	При выполнении операции присоединения произошла ошибка, и она не завершилась успешно.
     Кружок с двумя черными квадратами (слева и справа) и двумя белыми квадратами (сверху и снизу)	Остановлено	Операция подключения не была выполнена успешно, так как пользователь остановил операцию.
     Кружок, содержащий изогнутую стрелку, указывающую в направлении против часовой стрелки	Выполнен откат	Операция присоединения была успешной, но она была откатена из-за ошибки во время вложения другого объекта.

   • Сообщение

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

   • Прибавить

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

   • Удалить

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

   • Сведения о базе данных "<database_name>"

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

     Примечание.

     Если файл не существует, в столбце сообщения отображается сообщение "Не найдено". Если файл журнала не найден, он существует в другом каталоге или удален. Необходимо или обновить путь файла в сетке Сведения о базе данных таким образом, чтобы этот путь указывал на правильное расположение, или удалить файл журнала из сетки. Если файл данных NDF не найден, необходимо обновить путь в сетке, чтобы указать правильное расположение.

   • Имя исходного файла

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

   • Тип файла

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

   • Текущий путь к файлу

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

   • Сообщение

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

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

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

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

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

   -- Detach the TDE protected database from the source server.
   USE master;
   GO
   
   EXECUTE 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 = '<password>';
   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 = '<password>');
   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)
• СОЗДАТЬ БАЗУ ДАННЫХ

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

• Отсоединение базы данных и подключение (SQL Server)
• Прозрачное шифрование данных для базы данных SQL, SQL Управляемый экземпляр и Azure Synapse Analytics
• sp_detach_db (Transact-SQL)