SQLServer 数据加密解密：在多个服务器实例中创建相同对称密钥（三）

创建相同的对称密钥非常容易。使用相同的 KEY_SOURCE、ALGORITHM 和 IDENTITY_VALUE 密钥选项创建的对称密钥将是相同的。

--创建测试use [Temp]go--  drop table EnryptTest  create table EnryptTest  (      id int not null primary key,      EnryptData nvarchar(20),  )  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;  --删除测试信息use [Temp]godrop symmetric key MySymmetric;  godrop certificate  Mycertificate;  go  drop master key  goalter table EnryptTest drop column SymmetricCol;  gouse [Temp]go--创建数据库主密钥create master key encryption by password = N'Hello@MyMasterKey';go--创建以服务主密钥加密的证书create certificate Mycertificate with subject = N'EnryptData certificate';   go--创建对称密钥create symmetric key MySymmetricwithkey_source = 'Hello kk key_source',--指定从中派生密钥的通行短语identity_value = 'Hello kk identity_value', --该短语标记使用临时密钥加密的数据的GUID    algorithm = aes_128       encryption by certificate Mycertificate;  go--打开对称密钥open symmetric key MySymmetric decryption by certificate Mycertificate;  go  --加密数据update EnryptTest set SymmetricCol = encryptbykey(key_guid('MySymmetric'),convert(varchar(max),EnryptData))  go    --  解密数据（正常）select *,convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol)))    from EnryptTest  go  --关闭加密close symmetric key MySymmetric;go--备份主密钥backup master key   to file = N'D:\Temp_MasterKey'   encryption by password = N'Hello@MyMasterKey'  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--把主密钥和证书的备份拷贝到另一台服务器--把表EnryptTest数据传输到另一个服务器实例的数据库中(怎么做都行)--注意不是使用备份还原迁移数据库（备份还原数据库时会存在原有的主密钥和证书）

-------------------现在在另一台服务器实例中----------------------------use [temp2] --如数据在这个数据库中go--  还原数据库主密钥  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 master key decryption by password = N'Hello@MyMasterKey'  go  --  还原证书  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 symmetric key MySymmetricwithkey_source = 'Hello kk key_source',--指定从中派生密钥的通行短语identity_value = 'Hello kk identity_value', --该短语标记使用临时密钥加密的数据的GUID    algorithm = aes_128       encryption by certificate Mycertificate;  go--打开对称密钥open symmetric key MySymmetric decryption by certificate Mycertificate;  go  --  解密数据（成功）select *,convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol)))    from EnryptTest  go  --关闭加密close symmetric key MySymmetric;go--删除测试信息use [Temp2]godrop symmetric key MySymmetric;  godrop certificate  Mycertificate;  go  drop master key;godrop table EnryptTest;go

解密成功：

更多参考：如何在两个服务器上创建相同的对称密钥