SQLServer 資料加密解密：將 TDE 保護的資料庫移到其他執行個體（二），sqlservertde

SQLServer 資料加密解密：將 TDE 保護的資料庫移到其他執行個體（二），sqlservertde

--瞭解透明資料加密 (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