SQLServer 数据加密解密：将 TDE 保护的数据库移到其他实例（二）

--了解透明数据加密 (TDE)--https://technet.microsoft.com/zh-cn/library/bb934049(v=sql.105).aspx“透明数据加密”(TDE) 可对数据和日志文件执行实时 I/O 加密和解密。这种加密使用数据库加密密钥 (DEK)，该密钥存储在数据库引导记录中以供恢复时使用。数据库文件的加密在页级执行。已加密数据库中的页在写入磁盘之前会进行加密，在读入内存时会进行解密。使用TDE操作步骤:1. 创建主密钥2. 创建或获取由主密钥保护的证书3. 创建数据库加密密钥并通过此证书保护该密钥4. 将数据库设置为使用加密--查询相关信息select * from master.sys.key_encryptionsselect * from master.sys.certificatesselect * from sys.dm_database_encryption_keys where database_id=DB_ID('Temp')select name,is_master_key_encrypted_by_server from sys.databases where is_master_key_encrypted_by_server=1

【测试一：分离和附加迁移】

use master;go--将相关信息删除drop certificate  Mycertificate;go  drop master key;go  --创建数据库主密钥create master key encryption by password = N'Hello@MasterKey';go--创建以数据库主密钥加密的证书create certificate Mycertificate with subject = 'Certificate to protect TDE key';go--备份证书backup certificate Mycertificate   to file = N'D:\mycertificate.cer'  with private key (       file = N'D:\mycertificate_saleskey.pvk' ,     encryption by password = N'Hello@Mycertificate' );go  use [Temp];go--创建用于以透明方式加密数据库的加密密钥--https://msdn.microsoft.com/zh-cn/library/bb677241(v=sql.105).aspxcreate database encryption keywith algorithm = aes_128encryption by server certificate Mycertificate;go--启用数据库加密--https://msdn.microsoft.com/zh-cn/library/bb522682.aspxalter database [Temp] set encryption on;gouse master;go--分离数据库exec master.dbo.sp_detach_db @dbname = N'Temp';go--移动数据文件到另一台服务器中:--另一台服务器实例中:use master;go--附加数据库（失败）create database [Temp] on ( filename = N'C:\Database\Temp.mdf' ),( filename = N'C:\Database\Temp_log.ldf' )for attach ;go--创建新的数据库主密钥create master key encryption by password = N'Hello@MasterKey';go--还原证书create certificate Mycertificate from file = N'C:\Software\mycertificate.cer'with private key (    file = N'C:\Software\mycertificate_saleskey.pvk' ,     decryption by password = N'Hello@Mycertificate');go--附加数据库（成功）create database [Temp] on ( filename = N'C:\Database\Temp.mdf' ),( filename = N'C:\Database\Temp_log.ldf' )for attach ;go--删除测试信息use master;godrop certificate  Mycertificate;go  drop master key;go  

【测试二：备份和恢复迁移】

--删除上次测试信息use [Temp]goalter database [Temp] set encryption off;godrop database encryption keygouse master;godrop certificate  Mycertificate;go  drop master key;go  --创建数据库主密钥create master key encryption by password = N'Hello@MasterKey';go--创建以数据库主密钥加密的证书create certificate Mycertificate with subject = 'Certificate to protect TDE key';go--备份证书backup certificate Mycertificate   to file = N'D:\mycertificate.cer'  with private key (       file = N'D:\mycertificate_saleskey.pvk' ,     encryption by password = N'Hello@Mycertificate' );go  use [Temp];go--创建用于以透明方式加密数据库的加密密钥create database encryption keywith algorithm = aes_128encryption by server certificate Mycertificate;go--启用数据库加密alter database [Temp] set encryption on;go--备份数据库backup database [Temp] to disk = N'D:\Temp.bak'--拷贝mycertificate.cer、mycertificate_saleskey.pvk、Temp.bak到另一服务器--在另一台服务器实例中:use master;go--还原数据库(失败!)RESTORE DATABASE [Temp] FROM  DISK = N'C:\Software\Temp.bak' WITH  FILE = 1,  MOVE N'Temp' TO N'C:\Database\Temp.mdf',  MOVE N'Temp_log' TO N'C:\Database\Temp_log.ldf',  NOUNLOAD,  STATS = 10/*消息 33111，级别 16，状态 3，第 1 行找不到指纹为 '0xC1E2F607576904047A0D739588B65B27D8878B92' 的服务器 证书。消息 3013，级别 16，状态 1，第 1 行RESTORE DATABASE 正在异常终止。*/--创建新的数据库主密钥create master key encryption by password = N'Hello@MasterKey';go--还原证书create certificate Mycertificate from file = N'C:\Software\mycertificate.cer'with private key (    file = N'C:\Software\mycertificate_saleskey.pvk' ,     decryption by password = N'Hello@Mycertificate');go--还原数据库(成功)RESTORE DATABASE [Temp] FROM  DISK = N'C:\Software\Temp.bak' WITH  FILE = 1,  MOVE N'Temp' TO N'C:\Database\Temp.mdf',  MOVE N'Temp_log' TO N'C:\Database\Temp_log.ldf',  NOUNLOAD,  STATS = 10--删除数据库加密use [Temp];goalter database [Temp] set encryption off;godrop database encryption keygo

更多参考：将 TDE 保护的数据库移到其他 SQL Server