tde数据库加密

In this article, we will review how to enable Transparent Data Encryption (TDE) on a database in SQL Server and move the Transparent Data Encryption (TDE) enabled databases to a different server by restoring the backup.

在本文中，我们将回顾如何在SQL Server中的数据库上启用透明数据加密（TDE），以及如何通过还原备份将启用了透明数据加密（TDE）的数据库移至其他服务器。

Transparent Data encryption (TDE) encrypts data at rest i.e. data and log files. Encryption is done at page level on the database file. When Transparent Data encryption (TDE) is enabled on a database, it reads the page from the data files to buffer pool, encrypts the page and writes back to disk.

透明数据加密（TDE）对静止数据进行加密，即数据和日志文件。 加密是在数据库文件的页面级别完成的。 在数据库上启用透明数据加密（TDE）时，它将从数据文件中读取页面到缓冲池，对页面进行加密并写回到磁盘。

在数据库上启用透明数据加密（TDE） (Enabling Transparent Data Encryption (TDE) on database)

Enabling TDE on a database involves following steps.

在数据库上启用TDE涉及以下步骤。

• Creating a master key
  创建一个主密钥
• Creating a certificate in the master database
  在主数据库中创建证书
• Creating database encryption key (DEK)
  创建数据库加密密钥（DEK）
• Enable encryption on the database
  在数据库上启用加密

Let us go through these steps one by one.

让我们一步一步地完成这些步骤。

创建一个主密钥 (Creating a master key)

We need to create a master key in the master database. creating a master key is performed at the master database level. Execute the following T-SQL script which creates a master key in the master database. Replace it with stong password of yours. This database master key is encrypted by service master key at instance level which is created at the time of SQL Server instance setup.

我们需要在master数据库中创建一个主密钥。 在主数据库级别执行创建主密钥。 执行以下T-SQL脚本，该脚本在master数据库中创建一个主密钥。 将其替换为您的stong密码。 该数据库主密钥由服务主密钥在实例级别进行加密，该实例级别是在SQL Server实例安装时创建的。

USE master  GO  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLshackDemo@*';  GO

在主数据库中创建证书 (Creating a certificate in the master database )

The second step in enabling Transparent Data Encryption (TDE) is creating a certificate in the master database. Once we create a master key, we must create a certificate which is protected by the database master key created in the above step. Execute the following T-SQL script to create a certificate in the master database.

启用透明数据加密（TDE）的第二步是在master数据库中创建一个证书。 一旦创建了主密钥，就必须创建一个证书，该证书受以上步骤中创建的数据库主密钥的保护。 执行以下T-SQL脚本在master数据库中创建证书。

MyProduct_Cert is the name of the certificate. You can input the name and the subject of your choice.

MyProduct_Cert是证书的名称。 您可以输入名称和所选主题。

CREATE CERTIFICATE MyProduct_Cert WITH SUBJECT = 'Used to encrypt MyProduct Database';  go  

创建数据库加密密钥（DEK） (Creating database encryption key (DEK))

Once the certificate is created in the master database, we must create database encryption key (DEK) which is encrypted by the certificate created in the above step. Creation of database encryption key is performed at the user database. Execute the following T-SQL script which creates database encryption key (DEK) in the database called MyProductsDB. Replace the database name with yours.

一旦在master数据库中创建了证书，我们就必须创建数据库加密密钥（DEK），该密钥由在上述步骤中创建的证书进行加密。 数据库加密密钥的创建是在用户数据库上执行的。 执行以下T-SQL脚本，该脚本在名为MyProductsDB的数据库中创建数据库加密密钥（DEK） 。 用您的替换数据库名称。

USE MyProductDBGOCREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_256ENCRYPTION BY SERVER CERTIFICATE MyProduct_Cert;  

在数据库上启用加密 (Enable encryption on the database)

Once the database encryption key (DEK) is created, we must enable transparent data encryption (TDE) on the database. Execute the following T-SQL script by replacing the database name which you are going to encrypt.

创建数据库加密密钥（DEK）后，我们必须在数据库上启用透明数据加密（TDE）。 通过替换要加密的数据库名称，执行以下T-SQL脚本。

ALTER DATABASE MyProductDB    SET ENCRYPTION ON;    GO

We can also enable encryption by setting the value to true for the option “Enabled Encryption” in the database properties.

我们还可以通过将数据库属性中选项“ Enabled Encryption ”的值设置为true来启用加密 。

We can see the status of the encryption using the dynamic management view “sys.dm_database_encryption_keys”. Please use the below script to know the encryption status.

我们可以使用动态管理视图“ sys.dm_database_encryption_keys”查看加密状态。 请使用以下脚本了解加密状态。

SELECT DB_NAME(database_id)  ,encryption_state = CASE     WHEN encryption_state = 1      THEN 'Unencrypted'    WHEN encryption_state = 2      THEN 'Encryption in progress'    WHEN encryption_state = 3      THEN 'Encrypted'    WHEN encryption_state = 4      THEN 'Key change in progress'    WHEN encryption_state = 5      THEN 'Decryption in progress'    WHEN encryption_state = 6      THEN 'Protection change in progress'    WHEN encryption_state = 0      THEN 'No database encryption key present, no encryption'    END  ,create_date  ,encryptor_typeFROM sys.dm_database_encryption_keys

Please refer to the below image. We can see the database MyProductDB is encrypted along with tempdb. The system database tempdb will be automatically encrypted if enable encryption for at least one database in the instance.

请参考下图。 我们可以看到数据库MyProductDB和tempdb一起被加密了。 如果对实例中的至少一个数据库启用加密，系统数据库tempdb将被自动加密。

将启用了透明数据加密（TDE）的数据库备份还原到其他服务器 (Restoring Transparent Data encryption (TDE) enabled database backup to a different server)

When the database is enabled for transparent data encryption, the database backup files are also encrypted. If we try to restore a TDE enabled database backup on a different server it throws error “Cannot find server certificate with thumbprint”.

为数据库启用透明数据加密后，数据库备份文件也会被加密。 如果我们尝试在其他服务器上还原启用TDE的数据库备份，则会引发错误“无法使用指纹找到服务器证书”。

We need the certificate which was used to encrypt the database to restore the backup on a different server.

我们需要用于对数据库进行加密的证书，以在另一台服务器上还原备份。

Following are the steps involved in restoring Transparent Data encryption (TDE) enabled database.

以下是还原启用了透明数据加密（TDE）的数据库所涉及的步骤。

• Backup the certificate on the source server
  在源服务器上备份证书
• Copy the backup file and create a certificate from the file
  复制备份文件并从该文件创建证书
• Restore the database backup
  恢复数据库备份

We will go through these steps one by one.

我们将一步一步地完成这些步骤。

在源服务器上备份证书 (Backup the certificate on the source server)

First, we must back up the certificate that was used to encrypt the database. Execute the following T-SQL script to create the certificate backup and the private key file in the mentioned path. MyProduct_Cert is the name of the certificate. Replace the name of the certificate with yours.

首先，我们必须备份用于加密数据库的证书。 执行以下T-SQL脚本以在提到的路径中创建证书备份和私钥文件。 MyProduct_Cert是证书的名称。 用您的名称替换证书名称。

BACKUP CERTIFICATE MyProduct_Cert     TO FILE = '/var/opt/mssql/data/MyProduct_Cert.cer'    WITH PRIVATE KEY     (        FILE = '/var/opt/mssql/data/MyProduct_Cert.pvk',        ENCRYPTION BY PASSWORD = 'SQLshackDemo@*Backup'    );    GO

从文件创建证书 (Creating the certificate from the file)

Copy the backup file and the private key file to the server where you are going to restore the Transparent data encryption (TDE) enabled database backup.

将备份文件和私钥文件复制到要还原启用了透明数据加密（TDE）的数据库备份的服务器上。

Check if you have a master key on the master database already, create one if you do not have it. In this case, I do not have the master database key on the destination server.

检查主数据库上是否已经有一个主密钥，如果没有，请创建一个。 在这种情况下，我在目标服务器上没有主数据库密钥。

Execute the following script on the destination server to create the master key. replace it with the password of your choice.

在目标服务器上执行以下脚本以创建主密钥。 将其替换为您选择的密码。

USE master  GO  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLshackDemo@*Destination';  GO

Once the master key is created, restore the certificate using backup file and the private key. Execute the following T-SQL script to restore the certificate from the backup file. Please note that the password should be the same which was used to back up the certificate.

创建主密钥后，请使用备份文件和私钥还原证书。 执行以下T-SQL脚本以从备份文件还原证书。 请注意，密码应与用于备份证书的密码相同。

USE master;GOCREATE CERTIFICATE TDECert  FROM FILE = '/var/opt/mssql/data/MyProduct_Cert.cer'  WITH PRIVATE KEY (     FILE = N'/var/opt/mssql/data/MyProduct_Cert.pvk', DECRYPTION BY PASSWORD = 'SQLshackDemo@*Backup'  );GO

Now let us restore the TDE enabled database backup on the destination server. Please refer to the below image that shows the restore backup is successful after restoring the certificate that is used to create the database encryption key.

现在，让我们在目标服务器上还原启用TDE的数据库备份。 请参考下图，该图显示了还原用于创建数据库加密密钥的证书后还原备份成功。

结论 (Conclusion)

In this article, we explored how to enable Transparent Data Encryption (TDE) on a database in SQL Server and move the Transparent Data Encryption (TDE) enabled databases to a different server by restoring the backup. In case you have any questions, please feel free to ask in the comment section below.

在本文中，我们探讨了如何在SQL Server中的数据库上启用透明数据加密（TDE），以及如何通过还原备份将启用了透明数据加密（TDE）的数据库移动到其他服务器。 如果您有任何疑问，请随时在下面的评论部分中提问。

To continue your learning about Transparent Data Encryption (TDE), please refer to the category.

要继续学习透明数据加密（TDE），请参阅类别。

翻译自:

tde数据库加密