;
go
-- 备份证书
backup certificate mycertificate
to file = N'D:\mycertificate.cer'
with private key (
file = N'D:\mycertificate_saleskey.pvk' ,
decryption by password = N'Hello@Mycertificate' ,
encryption by password = N'Hello@Mycertificate' );
go
-- 删除证书
drop certificate Mycertificate;
go
-- 解密(失败)
select *,convert(varchar(max ),decryptbycert(cert_id('Mycertificate'),CertificateCol,N'Hello@Mycertificate'))
from EnryptTest;
go
-- 还原证书
create certificate mycertificate
from file = N'D:\mycertificate.cer'
with private key (
file = N'D:\mycertificate_saleskey.pvk',
decryption by password = N'Hello@Mycertificate' ,
encryption by password = N'Hello@Hello.KK' ); --新证书密码
go
-- 解密(正常)
select *,convert(varchar(max ),decryptbycert(cert_id('Mycertificate'),CertificateCol,N'Hello@Hello.KK')) --新证书密码
from EnryptTest;
go
-- 删除测试数据
drop certificate Mycertificate;
go
drop table EnryptTest
go
没有数据库主密钥情况下，使用密码加密的证书。证书直接加密解密数据，备份还原后，对之前的加密数据仍正常解密，因为备用还原都是同一个证书。而使用证书加密的对称密钥，对称密钥不能备份，删除重建后，key_guid不一样了，之前使用对称密钥加密的数据已经不能使用新的对称密钥解密了。查看select * from sys.symmetric_keys，可以看到不一样了。
插图2张：
|