SQL Server data encryption and decryption: migrate the database protected by TDE to another instance (2), sqlservertde

SQL Server data encryption and decryption: migrate the database protected by TDE to another instance (2), sqlservertde

-- Understand TDE-https://technet.microsoft.com/zh-cn/library/bb934049 (v = SQL .105). aspx TDE performs real-time I/O encryption and decryption on data and log files. This encryption uses the database encryption key (DEK), which is stored in the database Boot Record for recovery. Database file encryption is executed on pages. Pages in the encrypted database are encrypted before being written to the disk and decrypted when reading the data into the memory. Steps for using TDE: 1. create a CMK. 2. create or obtain a certificate protected by the CMK. create a database encryption key and use this certificate to protect the key 4. 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 1: Separation and additional migration]

Use master; go -- delete relevant information drop certificate Mycertificate; go drop master key; go -- create master key for creating a database master key encryption by password = n'hello @ masterkey '; go -- create a certificate named 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 an encryption key used to transparently encrypt the database -- 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 database exec master. dbo. sp_detach_db @ dbname = n'temp '; go -- move the data file to another server: -- In another server instance: use master; go -- attach the 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 create master key encryption by password = n' Hello @ masterkey'; go -- restore the certificate 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 -- attach database (succeeded) 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 Mycertificate; go drop master key; go

[Test 2: backup and recovery migration]

-- Delete the previous test information use [Temp] goalter database [Temp] set encryption off; godrop database encryption keygouse master; godrop certificate Mycertificate; go drop master key; go -- create master key for creating a database by password = n' Hello @ masterkey '; go -- create a certificate encrypted with the database master key 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 -- enable database encryption alter database [Temp] set encryption on; go -- backup database [Temp] to disk = n' D: \ Temp. bak '-- Copy mycertificate. cer, mycertificate_sa Leskey. pvk, Temp. bak to another server -- in another server instance: use master; go -- Restore 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/* message 33111, level 16, status 3, line 3 cannot find the server certificate with the fingerprint '0xc1e2f607576904047a0d739588b65b27d8878b92. Message 3013, level 16, status 1, 1st row restore database is terminating abnormally. */-- Create a new database master key create master key encryption by password = n' Hello @ masterkey'; go -- restore the certificate 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 (succeeded) 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 -- delete Database encryption use [Temp]; goalter database [Temp] set encryption off; godrop database encryption keygo

For more information, see migrate TDE-protected databases to other SQL servers.