SQLServer 数据加密解密

--  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. 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  

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  

2. 数据库密钥

/***************************************【数据库主密钥】***********************************/

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

3. 证书

/*****************************************【证书】*************************************/
--    证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据
--    当使用数据库主密钥对私钥进行加密时，不需要 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

 

4. 非对称密钥

/***************************************【非对称密钥】*************************************/
--    默认情况下，私钥受数据库主密钥保护

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

 

5. 对称密钥

/***************************************【对称密钥】*************************************/
--    也称为单密钥加密,采用单钥密码系统的加密方法，同一个密钥可以同时用作信息的加密和解密.
--    非对称密钥消耗多些系统性能，一般使用对称密钥加密数据，使用非对称密钥保护对称密钥

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