This website requires JavaScript.

SQL Server:恢复TDE加密的数据库[译]

问题

我用TDE(Transparent Data Encryption )加密SQL Server 数据库,我们知道使用TDE需要先建立一个master key ,然后公国证书对这个master key 进行加密. 那么如果我还原到另外一个数据库的时候用不同的master key 是否可以? 本文将罗列所有还原方式.

解决方案

创建新的数据库并且使用TDE

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
以上整个加密流程,注意我为master key 设置的密码. 根据之前提到过的,我打算用不同的密码来还原到另外一个数据库. 还原并不强制要求密码相同,但是证书必须是一样的.

虽然我们新建的数据库是空的,但的确是加密了的,通过以下语句可以查看加密状态

-- 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,则表明加密是成功的. 接着我们备份以下数据库
-- 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

还原失败 (不还原Key和证书)

如果我们没有master key 和证书,那么数据库根本无法还原,这就是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
执行系统系统会提示找不到证书,无法还原

还原失败 (证书名相同,但是证书不同)

-- 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
虽然建立了master key 和同样的证书名,但是还原依然失败. 注意:虽然我们采用了不同的master key,但是这不是还原失败地方.失败是因为我们没有正确的证书. 错误会和前一个例子一样,提示没有证书

还原失败(正确的证书,但是没有私钥)

第三个是你有同样的证书,但是没有私钥,结果也会失败.做这个测试之前请确保私钥在你另外一台机器上是有权限访问的.

-- 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

成功还原

这次我们附上了私钥,成功进行还原. 只要具备下面两个要素即可

  • master database需要有一个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

深入阅读

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.

原文地址

https://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/

0条评论
avatar