SQL Server Data encryption decryption: Moving a TDE-protected database to another instance (ii)

--Understanding transparent Data encryption (TDE)--https://technet.microsoft.com/zh-cn/library/bb934049 (v=sql.105). aspx transparent Data encryption (TDE) Real-time I/O encryption and decryption can be performed on data and log files. This encryption uses a database encryption key (DEK), which is stored in the database boot record for use in recovery. Encryption of the database file is performed at the page level. Pages in an encrypted database are encrypted before they are written to disk and decrypted when they are read into memory. Procedure for using TDE: 1. Create the Master key 2. Create or obtain a certificate protected by the master key 3. Create a database encryption key and protect the key 4 through this certificate. Set the database to use encryption--Query related information 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

"Test one: Detach and attach Migration"

Use master;go--Delete the relevant information drop certificate Mycertificate;go drop Master Key;go--Create the database master key to the creation master key encryption by PA ssWOrd = N ' [email protected] '; go--creates a certificate that is encrypted with the database master key create certificate mycertificate with subject = ' Certificate to Protect TDE key '; go--back up Certificate backup certificate mycertificate to File = N ' D:\mycertificate.cer ' with private key (fi Le = N ' D:\mycertificate_saleskey.pvk ', encryption by password = n ' [email protected] ') and go use [temp];go--created for Encrypted key--https://msdn.microsoft.com/zh-cn/library/bb677241 (v=sql.105). Aspxcreate Database Encryption Keywith algorithm = aes_128encryption by server certificate mycertificate;go--enable database encryption--https://msdn.microsoft.com/ Zh-cn/library/bb522682.aspxalter database [Temp] set encryption On;gouse master;go--detach databases exec master.dbo.sp_detach_db @dbname = N ' Temp '; go--move the data file to another server:--Another server instance: use master;go--Attach Database (failed) create DATABASE [Temp] on (filename = N ' c \ Database\temp.mdf '), (filename = N ' C:\Database\Temp_log.ldf ') ) for attach; go--create a new database master key-creation master key encryption by password = N ' [email protected] '; go--Restore certificate Create Certifica Te mycertificate from file = N ' C:\Software\mycertificate.cer ' with private key (file = N ' C:\Software\mycertificate_sale Skey.pvk ', decryption by password = n ' [email protected] '); go--additional database (successful) Create DATABASE [Temp] on (filename = n ') C:\Database\Temp.mdf '), (filename = N ' C:\Database\Temp_log.ldf ') for attach; go--Delete test information use Master;godrop certificate My   Certificate;go Drop Master Key;go

"Test II: Backup and Recovery Migration"

--Delete last Test information use [Temp]goalter database [Temp] set encryption Off;godrop database encryption Keygouse Master;godrop certific Ate mycertificate;go Drop Master key;go--Create the database master key created master keys encryption by password = N ' [email protected] '; go--creating a certificate that is encrypted with the database master key create certificate mycertificate with subject = ' Certificate to protect TDE key '; go--back up certificate backup Certifi      Cate Mycertificate to File = N ' D:\mycertificate.cer ' with private key (file = N ' D:\mycertificate_saleskey.pvk ',  Encryption by Password = N ' [email protected] '); go using [temp];go--Create an encryption key to transparently encrypt the database create DB encryption  Keywith algorithm = aes_128encryption by server certificate mycertificate;go--enable database encryption the ALTER DATABASE [TEMP] set encryption on;go--backing up databases backup database [Temp] to disk = N ' D:\Temp.bak '--copy mycertificate.cer, MYCERTIFICATE_SALESKEY.PVK, Temp.bak to another server-in another server instance: Use master;go--to restore the database (failed!) 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/* msg 33111, Level 16, State 3, line 1th cannot find the fingerprint as ' 0xc1e 2f607576904047a0d739588b65b27d8878b92 ' server certificate. Msg 3013, Level 16, State 1, line 1th, restore DATABASE is terminating abnormally. */--creating a new Database master key create master keys encryption by password = N ' [email protected] '; go--Restore certificate Create certificate Mycertificate from file = N ' C:\Software\mycertificate.cer ' with private key (file = N ' C:\Software\mycertificate_saleske Y.pvk ', decryption by password = n ' [email protected] '); go--Restore Database (SUCCEEDED) restore DB [Temp] from DISK = N ' C:\So Ftware\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--Delete the database encryption use [Temp];goalter database [Temp] set encryption Off;godrop database encryption Keygo

More information: Move a TDE-protected database to another SQL Server

SQL Server Data encryption decryption: Moving a TDE-protected database to another instance (ii)