创建相同的对称密钥非常容易。使用相同的 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
解密成功: