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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.