SQL Server data encryption and decryption: create the same symmetric key in multiple server instances (3). SQL server encryption and decryption

SQL Server data encryption and decryption: create the same symmetric key in multiple server instances (3). SQL server encryption and decryption

It is very easy to create the same symmetric key. Symmetric keys created using the same KEY_SOURCE, ALGORITHM, and IDENTITY_VALUE keys will be the same.

-- Create test use [Temp] go -- drop table EnryptTest create table EnryptTest (id int not null primary key, EnryptData nvarchar (20),) goinsert into EnryptTest values (1, n'000000'), (2, n'000000'), (3, n'000000'), (4, n'a '); goalter table EnryptTest add into riccol varbinary (max) go select * from EnryptTest; -- delete test information use [Temp] godrop foreign Ric key my1_ric; godrop certificate Mycertificate; go drop master key goalter table EnryptTest drop column into riccol; gouse [Temp] go -- create master key for creating a database by password = n' Hello @ mymasterkey '; go -- create a certificate encrypted with the Service master key create certificate Mycertificate with subject = n'enryptdata certificate '; go -- create a symmetric key create your Ric key my‑ricwithkey_source = 'Hello kk key_source ', -- specify the phrase identity_value = 'Hello kk identity_value 'For the derived key. -- this phrase marks the GUID algorithm = aes_128 encryption by certificate Mycertificate for data encrypted with the temporary key; go -- open the symmetric key open your Ric key my‑ric decryption by certificate Mycertificate; go -- update EnryptTest set encryption riccol = encryptbykey (key_guid ('mypolicric '), convert (varchar (max ), enryptData) go -- decrypt data (normal) select *, convert (varchar (max), convert (varchar (max), decryptbykey (SymmetricCol ))) from EnryptTest go -- disable encryption close encryption key mypolicric; go -- backup master key to file = n' D: \ Temp_MasterKey 'encryption by password = n' Hello @ mymasterkey' go -- backup certificate (including private) 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 -- copy the backup of the master key and certificate to another server -- transfer the data in the EnryptTest table to the database of another server instance (everything works) -- note that you do not use backup to restore and migrate the database (the original master key and certificate will exist when the database is backed up and restored)

------------------- Now in another server instance ---------------------------- use [temp2] -- if data is in this database go -- restore the database master key restore master key from file = n'c: \ Software \ Temp_MasterKey 'decryption by password = n' Hello @ mymasterkey' encryption by password = n' Hello @ mymasterkey' -- New Password go -- open the master key of the database for the current database decryption by password = n' Hello @ mymasterkey' 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 -- create a symmetric key (the symmetric key information of the two servers must be the same) create symmetric key my‑ricwithkey_source = 'Hello kk key_source ', -- specify the phrase identity_value = 'Hello kk identity_value 'For the derived key. -- this phrase marks the GUID algorithm = aes_128 encryption by certificate Mycertificate for data encrypted with the temporary key; go -- open the symmetric key open policric key mypolicric decryption by certificate Mycertificate; go -- decrypt data (successful) select *, convert (varchar (max), convert (varchar (max ), decryptbykey (SymmetricCol) from EnryptTest go -- disable encryption close encryption Ric key myweak Ric; go -- delete test information use [Temp2] godrop encryption Ric key myweak Ric; godrop certificate Mycertificate; go drop master key; godrop table EnryptTest; go

Decrypted successfully:

For more information, see how to create the same symmetric key on two servers.