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

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

--	创建测试
use [Temp]
go

--  drop table EnryptTest  
create table EnryptTest  
(  
    id int not null primary key,  
    EnryptData nvarchar(20),  
)  
go

insert into EnryptTest  
values(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');  
go

alter table EnryptTest add SymmetricCol varbinary(max)  
go  

select * from EnryptTest;  



--	删除测试信息
use [Temp]
go
drop symmetric key MySymmetric;  
go
drop certificate  Mycertificate;  
go  
drop master key  
go
alter table EnryptTest drop column SymmetricCol;  
go

use [Temp]
go
--	创建数据库主密钥
create master key encryption by password = N'Hello@MyMasterKey';
go

--	创建以服务主密钥加密的证书
create certificate Mycertificate with subject = N'EnryptData certificate';   
go

--	创建对称密钥
create symmetric key MySymmetric
with
	key_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 MySymmetric
with
	key_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]
go
drop symmetric key MySymmetric;  
go
drop certificate  Mycertificate;  
go  
drop master key;
go
drop table EnryptTest;
go

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