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

Source: Internet
Author: User
Tags decrypt

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)

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.