column SymmetricCol
go
【主密钥证书示例】
-- 测试数据
/*
drop certificate Mycertificate;
go
drop master key
go
drop table EnryptTest
go
*/
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
select * from EnryptTest;
alter table EnryptTest add CertificateCol varbinary(max)
go
--创建主密钥
create master key encryption by password = N'Hello@MyMasterKey'
go
/*
key_id thumbprint crypt_type crypt_type_desc crypt_property
------ ---------- ---------- --------------------- ------------------
101 0x01 ESKM ENCRYPTION BY MASTER KEY 0x7A2FEDA8139F1DE8F3377424C120DBDB8E1F7EAAEC1BBD73E72AC04F5CEECBFAC15FC7E130CA1756281EA0D8E6997F44
101 NULL ESKP ENCRYPTION BY PASSWORD 0x61D8F28F12CE4A1247F91E0ED828F2E937206E5D69B0754EE76756567AB428CBD5B54B76BCD1FC15E5E12202DBA4E187
*/
--创建证书，因为默认使用主密钥加密，此处不需要密码
create certificate Mycertificate
with subject = N'EnryptData certificate',
start_date = N'20150401',
expiry_date = N'20160401';
go
--加密解密都自动使用服务主密钥加密了。即使使用“close master key ”也不起作用
update EnryptTest
set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))
go
select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol))
from EnryptTest;
go
--现在删除“服务主密钥”
alter master key drop encryption by service master key
go
--再查询数据，没有解密出来。不自动使用主密钥加密解密了
select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol))
from EnryptTest;
go
--这时需要显式打开主密钥,使用主密钥密码加密解密
open master key decryption by password = N'Hello@MyMasterKey'
go
--再查询数据，解密出来了。
select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol))
from EnryptTest;
go
--最后关闭主密钥
close master key
go
--查看主密钥，少了"ENCRYPTION BY MASTER KEY",没有了主密钥进行加密,而是使用密码进行加密
select * from sys.key_encryptions
/*
key_id thumbprint crypt_type crypt_type_desc crypt_property
------ ---------- ---------- --------------------- ------------------
101 NULL ESKP ENCRYPTION BY PASSWORD 0xFB7D746C3CF0471147ECD710B1173B69A966421089FBB813BCF108E1ED90574F5C5F0998BA44F48869E05E9093BC59E6
*/
--删除测试数据
drop certificate Mycertificate;
go
drop master key
go
drop table EnryptTest
go
参考:
服务主密钥:https://msdn.microsoft.com/zh-cn/library/ms189060(v=sql.90).aspx
【证书备份还原示例】
-- 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 CertificateCol varbinary(max) --证书加密的列
go
select * from EnryptTest;
--将相关信息删除
drop certificate Mycertificate;
go
drop master key
go
-- 创建以密码加密的证书
create certificate Mycertificate
encryption by password = N'Hello@Mycertificate'
with subject = N'EnryptData certificate',
start_date = N'20150401',
expiry_date = N'20160401';
go
-- 证书加密数据
update EnryptTest set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))
go
-- 解密(正常)
select *,convert(varchar(max ),decryptbycert(cert_id('Mycertificate'),CertificateCol,N'Hello@Mycertificate'))
from EnryptTest