SQL Server Data encryption decryption: Create the same symmetric key in multiple server instances (iii)

It is very easy to create the same symmetric key. The symmetric keys created using the same key_source, algorithm, and Identity_value key options will be the same.

--Create test use [temp]go--drop table enrypttest CREATE TABLE enrypttest (ID int not NULL PRIMARY key, Enryptdata  nvarchar (+),) Goinsert into enrypttest values (1,n ' 888888 '), (2,n ' 888888 '), (3,n ' 123456 '), (4,n ' A ');  Goalter table Enrypttest add symmetriccol varbinary (max) go select * from Enrypttest;  --Delete test information use [Temp]godrop symmetric key mysymmetric;  Godrop certificate mycertificate;  Go Drop master key goalter table enrypttest drop column symmetriccol; Gouse [temp]go--Creating the Database master key create master keys encryption by password = N ' [email protected] '; go--create a certificate that is encrypted with the service master key creates   Certificate mycertificate with subject = N ' Enryptdata certificate '; go--Creating a symmetric key create symmetric key Mysymmetricwithkey_source = ' Hello kk key_source ',--specifies the passphrase to derive the key from identity_value = ' Hello '  KK Identity_value ',--the phrase marks the GUID of the data encrypted using the temporary key algorithm = aes_128 encryption by certificate Mycertificate;  go--opening the symmetric key open symmetric key Mysymmetric decryption by certificate mycertificate; Go--Encrypt data update EnRypttest Set symmetriccol = EncryptByKey (Key_GUID (' mysymmetric '), CONVERT (varchar (max), enryptdata)) go--Decrypt data (normal) Sele CT *,convert (varchar (max), convert (varchar (max), DecryptByKey (Symmetriccol))) from Enrypttest go--Turn off encryption close Symmet Ric Key mysymmetric;go--back up master key backup master keys to File = N ' D:\Temp_MasterKey ' encryption by password = n ' [email p         Rotected] ' Go--backup certificate (including private) certificate mycertificate to File = N ' D:\mycertificate.cer ' with private key (  FILE = N ' D:\mycertificate_saleskey.pvk ', encryption by password = n ' [email protected] '); go--Copy the master key and the certificate back to the other server--transfer the table enrypttest data to another server instance's database (how to do it)--note that you do not use a backup to restore the migrated database (the original master key and certificate exist when the backup restores the database)

-------------------now----------------------------use [TEMP2] in another server instance--go--Restore the database master key, such as data in this database From file = N ' C:\Software\Temp_MasterKey ' decryption by password = n ' [email protected] ' encryption by PAS Sword = n ' [email protected] '--new Password go--Open the database master key for the current database The Open master key decryption by Password = N ' [email& Nbsp;protected] ' Go--Restore certificate Create certificate mycertificate from file = N ' C:\Software\mycertificate.cer ' with Priv   Ate key (file = N ' C:\Software\mycertificate_saleskey.pvk ', decryption by password = n ' [email protected] '); go--Create a symmetric key (two servers must have the same symmetric key information) create symmetric key Mysymmetricwithkey_source = ' Hello kk Key_source ',-- Specifies the pass phrase from which the key is derived identity_value = ' Hello kk identity_value ',--the phrase marks the GUID of the data encrypted with the temporary key algorithm = aes_128 Encryption b  Y certificate mycertificate;  go--opening the symmetric key open symmetric key Mysymmetric decryption by certificate mycertificate; Go-Decrypt data (success) Select *,convert (varchar (max), ConvERT (varchar (max), DecryptByKey (Symmetriccol))) from Enrypttest go--turn off encryption close symmetric key mysymmetric;go--Delete test information us  e [temp2]godrop symmetric key mysymmetric;  Godrop certificate mycertificate; Go Drop Master key;godrop table Enrypttest;go

Decryption succeeded:

More information: How to create the same symmetric key on two servers

SQL Server Data encryption decryption: Create the same symmetric key in multiple server instances (iii)