译：如何成功恢复TDE加密过的数据库

原文地址： https://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/

 

问题：

我的任务是在具有敏感信息的SQL Server数据库上设置透明数据加密(TDE)。在我看到的示例中，我知道我需要主数据库中的主密钥，然后需要用该主密钥加密的证书。

一个浮现在脑海中的问题是，如果我选择将数据库恢复到不同的服务器上我的数据库主密钥是否必须具有相同的密码? 我见过的每个例子都包含相同的密码。为了成功还原，我还需要计划些什么?

解决方案：

为SQL Server数据库启用TDE加密是一个简单的过程。它包括:

1，在主数据库中创建数据库主密钥master key。

2，创建由该密钥master key加密的证书。

3，备份证书和证书的私钥。虽然加密数据库不需要这样做，但您希望立即这样做。（译者注：为了在别的机器上还原加密后的数据库，必须要有证书以及证书的私钥）

4，在（用户自定义）数据库中创建由证书加密的密钥。

5，修改数据库以开启加密。

如果您正在阅读本文并且不熟悉版本要求，那么TDE仅适用于Microsoft SQL Server的企业版（译者注：自SQLServer 2017开始，企业版，标准版 web版均支持TDE ）。

 

使用TDE创建新的SQL Server数据库

让我们用下面的代码设置一个示例数据库:

USE [master];
GO 

-- Create the database master key
-- to encrypt the certificate
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'FirstServerPassw0rd!';
GO 

-- Create the certificate we're going to use for TDE
CREATE CERTIFICATE TDECert
  WITH SUBJECT = 'TDE Cert for Test';
GO 

-- Back up the certificate and its private key
-- Remember the password!
BACKUP CERTIFICATE TDECert
  TO FILE = N'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

-- Create our test database
CREATE DATABASE [RecoveryWithTDE];
GO 

-- Create the DEK so we can turn on encryption
USE [RecoveryWithTDE];
GO 

CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO 

-- Exit out of the database. If we have an active 
-- connection, encryption won't complete.
USE [master];
GO 

-- Turn on TDE
ALTER DATABASE [RecoveryWithTDE]
  SET ENCRYPTION ON;
GO 

这将启动数据库上的加密过程。请注意我为数据库主密钥指定的密码。正如所暗示的那样，当我们在第二台服务器上执行恢复时我将使用不同的密码，不需要拥有相同的密码，但需要拥有相同的证书，我们将在查看恢复过程中的“陷阱”时了解这一点。即使在基本为空的数据库上，加密数据库也需要几秒钟的时间。可以通过以下查询查询加密的状态:

-- We're looking for encryption_state = 3
-- Query periodically until you see that state
-- It shouldn't take long
SELECT DB_Name(database_id) AS 'Database', encryption_state 
FROM sys.dm_database_encryption_keys;

正如注释所示，我们希望数据库显示状态为3，这意味着加密已经完成。下面是你应该看到的一个例子:

 当encryption_state显示为3时，您应该对数据库进行备份，因为我们将需要它来恢复到第二台服务器(您的路径可能不同):

-- Now backup the database so we can restore it
-- Onto a second server
BACKUP DATABASE [RecoveryWithTDE]
TO DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak';
GO 

现在我们有了备份，让我们将该备份恢复到SQL Server的另一个实例。

 

Failed Restore - No Key, No Certificate

恢复受TDE保护的数据库的第一种情况是，我们尝试进行恢复，但没有任何加密块被恢复。我们没有数据库主密钥，当然也没有证书。

这就是为什么TDE是伟大的。如果没有这些部分，恢复就无法工作。让我们尝试恢复(注意:您的路径可能不同):

-- Attempt the restore without the certificate installed
RESTORE DATABASE [RecoveryWithTDE]
  FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'
  WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',
       MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';
GO

这将会失败。下面是尝试恢复时应该看到的内容:

当SQL Server尝试恢复时，它会意识到它需要一个证书，一个特定的证书。由于证书不存在，因此恢复失败。

 

Failed Restore - The Same Certificate Name, But Not the Same Certificate

第二种情况是存在数据库主密钥，并且存在与第一个服务器(甚至是相同的主题)名称相同的证书，但它不是来自第一个服务器的证书。让我们设置并尝试恢复:

-- Let's create the database master key and a certificate with the same name
-- But not from the files. Note the difference in passwords
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'SecondServerPassw0rd!';
GO 

-- Though this certificate has the same name, the restore won't work
CREATE CERTIFICATE TDECert
  WITH SUBJECT = 'TDE Cert for Test';
GO 

-- Since we don't have the corrected certificate, this will fail, too.
RESTORE DATABASE [RecoveryWithTDE]
  FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'
  WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',
       MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';
GO

请注意数据库主密钥密码（与第一台机器上的主密钥密码）是的不同，这两个主密钥的密码这是不同的，但这不是我们在恢复中失败的原因。和之前的情况一样，我们没有正确的证书。结果，您将得到与前一种情况相同的错误。

Failed Restore - The Right Certificate, but Without the Private Key

下一个场景是完成证书恢复，但没有私钥。这也将失败。但是，在尝试T-SQL代码之前，可能必须修复证书文件和私钥文件上的文件权限。如果您试图在与第一个实例位于同一系统上的SQL Server实例上执行恢复，则可能会出现这种情况。

为了确保第二个实例可以访问这些文件，请转到文件的位置并调出文件属性(右键单击文件，然后从弹出菜单中选择properties)。单击“安全”选项卡，然后单击“高级”按钮。当SQL Server写这些文件时，它可能破坏了权限的继承，我们将修复这个问题。

 这将出现一个不同的界面，您应该查看权限选项卡。如果您看到它，请单击Continue按钮。如果UAC打开，你会看到它。

 现在您将打继承选项。注意我圈出来的东西。如果框未选中，如下图所示，这意味着取消继承。如果您的文件就是这种情况，请单击复选框，并在每个接口单击OK以重新打开继承。

 现在让我们尝试恢复证书，但故意忘记使用私钥进行恢复。在从文件创建证书之前，必须先删除刚刚创建的证书。

-- Let's drop the certificate and do the restore of it...
-- But without the private key
DROP CERTIFICATE TDECert;
GO 

-- Restoring the certificate, but without the private key.
CREATE CERTIFICATE TDECert
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
GO

-- We have the correct certificate, but not the private key.
-- This should fail as well.
RESTORE DATABASE [RecoveryWithTDE]
  FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'
  WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',
       MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';
GO

我们有正确的证书，但是没有私钥，SQL Server不能用它来解密数据库。结果，我们得到一个不同的错误，告诉我们这个键有问题。错误提示密钥似乎已损坏，但我们知道真正的问题:我们没有恢复密钥。

 

The Successful Restore

为了执行成功的恢复，我们需要主数据库中的数据库主密钥，我们需要恢复用于加密数据库的证书，但我们需要确保使用私钥恢复它。以核对表形式:

1,在主数据库中有一个数据库主密钥master key。

2,用于加密数据库的证书将与其私钥一起恢复（译者注：还原证书时需要与创建证书时的私钥的密码一致，也即 DECRYPTION BY PASSWORD必须与创建证书时一致，否则无法还原证书）。

3,完成数据库恢复。

既然有了数据库主密钥master key，那么让我们执行最后两个步骤。当然，由于我们必须清除以前的证书，我们将在我们发出的命令中有一个删除证书:

-- Let's do this one more time. This time, with everything,
-- Including the private key.
DROP CERTIFICATE TDECert;
GO 

-- Restoring the certificate, but without the private key.
CREATE CERTIFICATE TDECert
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

-- We have the correct certificate and we've also restored the 
-- private key. Now everything should work. Finally!
RESTORE DATABASE [RecoveryWithTDE]
  FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'
  WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',
       MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';
GO

一切就绪，我们终于成功了!

如果在执行恢复时没有看到升级步骤消息，请不要惊慌。

对于这个例子，我在SQL Server 2008 R2实例上创建了初始数据库，并将其恢复到SQL Server 2014实例上。

因此，如果您有关于是否可以将受TDE保护的数据库恢复到不同版本的SQL Server的问题，那么答案是肯定的，只要新实例运行的是企业版，并且新实例具有相同或更高版本的SQL Server(包括服务包、累积更新和任何热修复程序/补丁)。

第二个要求不应该令人惊讶，因为这是将数据库恢复到不同服务器的标准要求。

 

Next Steps

• Read up on some more points you should know about implementing TDE in SQL Server 2008.
• Learn how to configure TDE in SQL Server 2012 when using Availability Groups.
• Understand how to set up encrypted backups in SQL Server 2014 for cases where you can't use TDE.