r key
go
【证书】
/*****************************************【证书】*************************************/
-- 证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据
-- 当使用数据库主密钥对私钥进行加密时，不需要 ENCRYPTION BY PASSWORD 选项。私钥使用数据库主密钥进行加密
--（有点难理解，最后给出例子）
select * from sys.key_encryptions
select * from sys.crypt_properties
select * from sys.certificates
select * from EnryptTest
-- 添加测试列
alter table EnryptTest add CertificateCol varbinary(max)
go
-- 创建证书
-- https://technet.microsoft.com/zh-cn/library/ms187798%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
create certificate Mycertificate
encryption by password = N'Hello@Mycertificate' --加密密码
with subject = N'EnryptData certificate', --证书描述
start_date = N'20150401', --证书生效日
expiry_date = N'20160401'; --证书到期日
go
-- 使用证书的公钥加密数据
-- https://msdn.microsoft.com/zh-cn/library/ms188061(v=sql.105).aspx
update EnryptTest
set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))
go
-- 用证书的私钥解密数据
-- https://msdn.microsoft.com/zh-cn/library/ms178601(v=sql.105).aspx
select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol,N'Hello@Mycertificate'))
from EnryptTest;
go
-- 修改私钥密码
-- https://msdn.microsoft.com/zh-cn/library/ms189511(v=sql.105).aspx
alter certificate mycertificate
with private key (
decryption by password = N'Hello@Mycertificate',
encryption by password = N'Hello@Mycertificate')
go
-- 从证书中删除私钥
alter certificate mycertificate remove private key
go
-- 备份证书
-- https://msdn.microsoft.com/zh-cn/library/ms178578(v=sql.105).aspx
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
-- 创建/还原证书
create certificate mycertificate
from file = N'D:\mycertificate.cer'
with private key (
file = N'D:\mycertificate_saleskey.pvk',
decryption by password = 'Hello@Mycertificate');
go
-- 删除对称密钥
-- https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspx
drop certificate Mycertificate;
go
-- 删除测试列
alter table EnryptTest drop column CertificateCol;
go
【非对称密钥】
/***************************************【非对称密钥】*************************************/
-- 默认情况下，私钥受数据库主密钥保护
select * from sys.key_encryptions
select * from sys.crypt_properties
select * from sys.certificates
select * from sys.asymmetric_keys
select * from sys.openkeys
select * from EnryptTest
-- 添加测试列
alter table EnryptTest add AsymmetricCol varbinary(max)
go
-- 创建非对称密钥
-- https://msdn.microsoft.com/zh-cn/library/ms174430(v=sql.105).aspx
create asymmetric key MyAsymmetric
with
algorithm=rsa_512
encryption by password='Hello@MyAsymmetric';
go
-- 加密（EncryptByAsymKey）
-- https://msdn.microsoft.com/ZH-CN/library/ms186950(v=sql.105).aspx
update EnryptTest
set AsymmetricCol = EncryptByAsymKey(asymkey_id ('MyAsymmetric'),convert(varchar(max ),EnryptData))
go
-- 解密（DecryptByAsymKey）
-- https://msdn.microsoft.com/ZH-CN/library/ms189507(v=sql.105).aspx
select *,convert(varchar(max),DecryptByAsymKey(asymkey_id('MyAs