You are here
SQLServer 数据加密解密：常用的加密解密（一） 有大用
都是基本示例，更多参考官方文档：
1. Transact-SQL 函数
2. 数据库密钥
3. 证书
4. 非对称密钥
5. 对称密钥
-- drop table EnryptTest
create table EnryptTest
(
id int not null primary key,
EnryptData nvarchar(20),
)
insert into EnryptTest
values(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');
select * from EnryptTest;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
【Transact-SQL 函数加密】
/***********************************【Transact-SQL 函数加密】********************************/
-- 使用 TRIPLE DES 算法（128 密钥位长度）的通行短语加密数据。
-- 添加测试列
alter table EnryptTest add PassPhrase varbinary(256)
alter table EnryptTest add PassPhrase2 varbinary(256)--用于验证器验证
-- 加密(EncryptByPassPhrase)
-- https://technet.microsoft.com/zh-cn/library/ms190357%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
update EnryptTest set PassPhrase = EncryptByPassPhrase('Hello.kk',EnryptData)
go
update EnryptTest
set PassPhrase2 = EncryptByPassPhrase
(
'Hello.kk' --用于生成对称密钥的通行短语
, EnryptData --要加密的明文
, 1 --指示是否将验证器与明文一起加密。如果将添加验证器，则为 1
, convert(varbinary,id) --用于派生验证器的数据(如 主键)
)
go
-- 解密(DecryptByPassPhrase)
-- https://technet.microsoft.com/zh-cn/library/ms188910%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
select convert(nvarchar,DecryptByPassphrase('Hello.kk',PassPhrase)) from EnryptTest;
go
select convert(nvarchar,DecryptByPassphrase(
'Hello.kk' --生成解密密钥的通行短语
, PassPhrase2 --要解密的加密文本varbinary
, 1 --添加验证器
, convert(varbinary,id)))--验证器为主键
from EnryptTest;
go
--附：未用验证器的，数据并不安全
--如：把所有id的密码都改为与A一样，其他密码的解密与A一样，别人就有可能登录其他账号
update EnryptTest set PassPhrase = (select PassPhrase from EnryptTest where id=4)
go
select id,EnryptData,convert(nvarchar,DecryptByPassphrase('Hello.kk',PassPhrase))
from EnryptTest;
-- 删除测试列
alter table EnryptTest drop column PassPhrase
alter table EnryptTest drop column PassPhrase2
go
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
DECLARE @ENPWD VARBINARY(MAX)
DECLARE @DEPWD NVARCHAR(100)
DECLARE @ENSTR NVARCHAR(100)
SET @ENSTR = 'hello.KK' --加密密码
--加密
SELECT @ENPWD = ENCRYPTBYPASSPHRASE( @ENSTR, N'13500000000')
SELECT @ENPWD
--解密
SELECT @DEPWD =CAST( DECRYPTBYPASSPHRASE(@ENSTR,@ENPWD) AS NVARCHAR(MAX))
SELECT @DEPWD
go
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
【数据库主密钥】
/***************************************【数据库主密钥】***********************************/
select * from sys.key_encryptions
select * from sys.crypt_properties
-- 创建数据库主密钥
-- https://technet.microsoft.com/zh-cn/library/ms174382(v=sql.105).aspx
create master key encryption by password = N'Hello@MyMasterKey' --必须符合Windows密码策略要求
go
-- 打开当前数据库的数据库主密钥
-- https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspx
open master key decryption by password = N'Hello@MyMasterKey'
go
-- 更改数据库主密钥的属性
-- https://technet.microsoft.com/zh-cn/library/ms186937(v=sql.105).aspx
alter master key regenerate with encryption by password = N'Hello@MyMasterKey'
alter master key add encryption by password = N'Hello@kk'
alter master key drop encryption by password = N'Hello@kk'
alter master key add encryption by service master key
alter master key drop encryption by service master key
-- 导出数据库主密钥
-- https://technet.microsoft.com/zh-cn/library/ms174387(v=sql.105).aspx
backup master key
to file = N'D:\XXDB_MasterKey'
encryption by password = N'Hello@MyMasterKey'
go
-- 从备份文件中导入数据库主密钥
-- https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspx
restore master key
from file = N'D:\XXDB_MasterKey'
decryption by password = N'Hello@MyMasterKey'
encryption by password = N'Hello@MyMasterKey' --New Password
go
-- 从当前数据库中删除主密钥
-- https://technet.microsoft.com/zh-cn/library/ms180071(v=sql.105).aspx
drop master key
go
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
【证书】
/*****************************************【证书】*************************************/
-- 证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据
-- 当使用数据库主密钥对私钥进行加密时，不需要 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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
【非对称密钥】
/***************************************【非对称密钥】*************************************/
-- 默认情况下，私钥受数据库主密钥保护
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('MyAsymmetric'),AsymmetricCol,N'Hello@MyAsymmetric'))
from EnryptTest
go
-- 更改非对称密钥属性
-- https://msdn.microsoft.com/zh-cn/library/ms187311(v=sql.105).aspx
-- 更改私钥密码
alter asymmetric key MyAsymmetric
with private key (
decryption by password = N'Hello@MyAsymmetric',--原私钥密码
encryption by password = N'Hello@MyAsymmetric');--新私钥密码
go
-- 删除私钥,只保留公钥
-- 如果将非对称密钥映射到 EKM 设备上的可扩展密钥管理 (EKM) 密钥并且未指定 REMOVE PROVIDER KEY 选项，
-- 则会从数据库中删除该密钥，但不会从设备上删除它。这时会发出一条警告。
alter asymmetric key MyAsymmetric remove private key;
go
-- 删除非对称密钥
-- https://msdn.microsoft.com/ZH-CN/library/ms188389(v=sql.105).aspx
drop symmetric key MyAsymmetric ;
go
-- 删除测试列
alter table EnryptTest drop column AsymmetricCol
go
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
【对称密钥】
/***************************************【对称密钥】*************************************/
-- 也称为单密钥加密,采用单钥密码系统的加密方法，同一个密钥可以同时用作信息的加密和解密.
-- 非对称密钥消耗多些系统性能，一般使用对称密钥加密数据，使用非对称密钥保护对称密钥
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 sys.symmetric_keys
select * from EnryptTest
-- 添加测试列
alter table EnryptTest add SymmetricCol varbinary(max)
go
-- 创建对称密钥
-- https://msdn.microsoft.com/zh-cn/library/ms188357(v=sql.105).aspx
create symmetric key MySymmetric --以密码加密的对称密钥
with
algorithm=aes_128
encryption by password='Hello@MySymmetric';
go
create symmetric key MySymmetric --以非对称密钥加密的对称密钥
with
algorithm=aes_128
encryption by asymmetric key MyAsymmetric
go
-- 打开对称密钥(打开才能有效使用加密解密函数)
-- https://msdn.microsoft.com/zh-cn/library/ms190499(v=sql.105).aspx
open symmetric key MySymmetric decryption by password='Hello@MySymmetric';
go
open symmetric key MySymmetric decryption by asymmetric key MyAsymmetric with password='Hello@MyAsymmetric';
go
-- 加密数据
-- https://technet.microsoft.com/zh-cn/library/ms174361%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
update EnryptTest set SymmetricCol = encryptbykey(key_guid('MySymmetric'),convert(varchar(max),EnryptData))
go
-- 解密数据
-- https://technet.microsoft.com/zh-cn/library/ms181860(v=sql.105).aspx
select *,convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol)))
from EnryptTest
go
-- 关闭对称密钥，或关闭在当前会话中打开的所有对称密钥
-- https://msdn.microsoft.com/zh-cn/library/ms177938%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
-- close all symmetric keys;
close symmetric key MySymmetric;
go
-- alter symmetric 添加或删除新的加密方式(如添加多多个密码，任何一个密码都可用)
-- https://msdn.microsoft.com/zh-cn/library/ms189440(v=sql.105).aspx
open symmetric key MySymmetric decryption by password='Hello@MySymmetric';
alter symmetric key MySymmetric add encryption by password = 'Hello@kk' --New another Password
close symmetric key MySymmetric;
open symmetric key MySymmetric decryption by password='Hello@kk'; --Use New Password
select convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol))) from EnryptTest
alter symmetric key MySymmetric drop encryption by password = 'Hello@kk'--Drop the new Password
close symmetric key MySymmetric;
go
-- 删除对称密钥
-- https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspx
drop symmetric key MySymmetric;
go
-- 删除测试列
alter table EnryptTest drop column SymmetricCol
go
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
【主密钥证书示例】
-- 测试数据
/*
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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
【证书备份还原示例】
-- 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;
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，可以看到不一样了。
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
加密解密函数： https://msdn.microsoft.com/zh-cn/library/ms173744.aspx
插图2张：
赞
收藏
评论
分享
举报
上一篇：SQLServer 数据加密解密：将 TDE 保护的数据库移到其他实例（二）下一篇：SqlServer 使用脚本创建分发服务及事务复制的可更新
-----------------------------------
©著作权归作者所有：来自51CTO博客作者JeesonHuang的原创作品，请联系作者获取转载授权，否则将追究法律责任
SQLServer 数据加密解密：常用的加密解密（一）
https://blog.51cto.com/hzc2012/6001409