都是基本示例，更多参考官方文档：
1. Transact-SQL 函数
2. 数据库密钥
3. 证书
4. 非对称密钥
5. 对称密钥
-- drop table EnryptTestcreate table EnryptTest( id int not null primary key, EnryptData nvarchar(20),)insert into EnryptTestvalues(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');select * from EnryptTest;
【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=-2147217396update EnryptTest set PassPhrase = EncryptByPassPhrase('Hello.kk',EnryptData)goupdate 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=-2147217396select convert(nvarchar,DecryptByPassphrase('Hello.kk',PassPhrase)) from EnryptTest;goselect 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)goselect id,EnryptData,convert(nvarchar,DecryptByPassphrase('Hello.kk',PassPhrase)) from EnryptTest;-- 删除测试列alter table EnryptTest drop column PassPhrase alter table EnryptTest drop column PassPhrase2go【数据库主密钥】
/***************************************【数据库主密钥】***********************************/select * from sys.key_encryptionsselect * from sys.crypt_properties-- 创建数据库主密钥-- https://technet.microsoft.com/zh-cn/library/ms174382(v=sql.105).aspxcreate master key encryption by password = N'[email protected]' --必须符合Windows密码策略要求go-- 打开当前数据库的数据库主密钥-- https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspxopen master key decryption by password = N'[email protected]'go-- 更改数据库主密钥的属性-- https://technet.microsoft.com/zh-cn/library/ms186937(v=sql.105).aspxalter master key regenerate with encryption by password = N'[email protected]'alter master key add encryption by password = N'[email protected]'alter master key drop encryption by password = N'[email protected]'alter master key add encryption by service master keyalter master key drop encryption by service master key-- 导出数据库主密钥-- https://technet.microsoft.com/zh-cn/library/ms174387(v=sql.105).aspxbackup master key to file = N'D:\XXDB_MasterKey' encryption by password = N'[email protected]'go-- 从备份文件中导入数据库主密钥-- https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspxrestore master key from file = N'D:\XXDB_MasterKey' decryption by password = N'[email protected]' encryption by password = N'[email protected]' --New Passwordgo-- 从当前数据库中删除主密钥-- https://technet.microsoft.com/zh-cn/library/ms180071(v=sql.105).aspxdrop master keygo
【证书】
/*****************************************【证书】*************************************/-- 证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据-- 当使用数据库主密钥对私钥进行加密时，不需要 ENCRYPTION BY PASSWORD 选项。私钥使用数据库主密钥进行加密--（有点难理解，最后给出例子）select * from sys.key_encryptionsselect * from sys.crypt_propertiesselect * from sys.certificatesselect * 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=-2147217396create certificate Mycertificateencryption by password = N'[email protected]' --加密密码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).aspxupdate EnryptTest set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))go-- 用证书的私钥解密数据-- https://msdn.microsoft.com/zh-cn/library/ms178601(v=sql.105).aspxselect *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol,N'[email protected]'))from EnryptTest;go-- 修改私钥密码 -- https://msdn.microsoft.com/zh-cn/library/ms189511(v=sql.105).aspxalter certificate mycertificate with private key ( decryption by password = N'[email protected]', encryption by password = N'[email protected]')go-- 从证书中删除私钥 alter certificate mycertificate remove private keygo-- 备份证书-- https://msdn.microsoft.com/zh-cn/library/ms178578(v=sql.105).aspxbackup certificate mycertificate to file = N'D:\mycertificate.cer' --用于加密的证书备份路径with private key ( file = N'D:\mycertificate_saleskey.pvk' , --用于解密证书私钥文件路径 decryption by password = N'[email protected]' ,--对私钥进行解密的密码 encryption by password = N'[email protected]' );--对私钥进行加密的密码go-- 创建/还原证书create certificate mycertificate from file = N'D:\mycertificate.cer' with private key ( file = N'D:\mycertificate_saleskey.pvk', decryption by password = '[email protected]');go-- 删除对称密钥-- https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspxdrop certificate Mycertificate;go-- 删除测试列alter table EnryptTest drop column CertificateCol;go
【非对称密钥】
/***************************************【非对称密钥】*************************************/-- 默认情况下，私钥受数据库主密钥保护select * from sys.key_encryptionsselect * from sys.crypt_propertiesselect * from sys.certificatesselect * from sys.asymmetric_keysselect * from sys.openkeysselect * from EnryptTest-- 添加测试列alter table EnryptTest add AsymmetricCol varbinary(max)go-- 创建非对称密钥-- https://msdn.microsoft.com/zh-cn/library/ms174430(v=sql.105).aspxcreate asymmetric key MyAsymmetric with algorithm=rsa_512 encryption by password='[email protected]';go -- 加密（EncryptByAsymKey）-- https://msdn.microsoft.com/ZH-CN/library/ms186950(v=sql.105).aspxupdate EnryptTest set AsymmetricCol = EncryptByAsymKey(asymkey_id ('MyAsymmetric'),convert(varchar(max ),EnryptData)) go-- 解密（DecryptByAsymKey）-- https://msdn.microsoft.com/ZH-CN/library/ms189507(v=sql.105).aspxselect *,convert(varchar(max),DecryptByAsymKey(asymkey_id('MyAsymmetric'),AsymmetricCol,N'[email protected]'))from EnryptTestgo-- 更改非对称密钥属性-- https://msdn.microsoft.com/zh-cn/library/ms187311(v=sql.105).aspx-- 更改私钥密码alter asymmetric key MyAsymmetric with private key ( decryption by password = N'[email protected]',--原私钥密码 encryption by password = N'[email protected]');--新私钥密码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).aspxdrop symmetric key MyAsymmetric ;go-- 删除测试列alter table EnryptTest drop column AsymmetricColgo
【对称密钥】
/***************************************【对称密钥】*************************************/-- 也称为单密钥加密,采用单钥密码系统的加密方法，同一个密钥可以同时用作信息的加密和解密.-- 非对称密钥消耗多些系统性能，一般使用对称密钥加密数据，使用非对称密钥保护对称密钥select * from sys.key_encryptionsselect * from sys.crypt_propertiesselect * from sys.certificatesselect * from sys.asymmetric_keysselect * from sys.openkeysselect * from sys.symmetric_keysselect * from EnryptTest-- 添加测试列alter table EnryptTest add SymmetricCol varbinary(max)go-- 创建对称密钥-- https://msdn.microsoft.com/zh-cn/library/ms188357(v=sql.105).aspxcreate symmetric key MySymmetric --以密码加密的对称密钥with algorithm=aes_128 encryption by password='[email protected]';gocreate symmetric key MySymmetric --以非对称密钥加密的对称密钥with algorithm=aes_128 encryption by asymmetric key MyAsymmetricgo-- 打开对称密钥(打开才能有效使用加密解密函数)-- https://msdn.microsoft.com/zh-cn/library/ms190499(v=sql.105).aspxopen symmetric key MySymmetric decryption by password='[email protected]';goopen symmetric key MySymmetric decryption by asymmetric key MyAsymmetric with password='[email protected]';go-- 加密数据-- https://technet.microsoft.com/zh-cn/library/ms174361%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396update EnryptTest set SymmetricCol = encryptbykey(key_guid('MySymmetric'),convert(varchar(max),EnryptData))go-- 解密数据-- https://technet.microsoft.com/zh-cn/library/ms181860(v=sql.105).aspxselect *,convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol))) from EnryptTestgo-- 关闭对称密钥，或关闭在当前会话中打开的所有对称密钥-- 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).aspxopen symmetric key MySymmetric decryption by password='[email protected]';alter symmetric key MySymmetric add encryption by password = '[email protected]' --New another Passwordclose symmetric key MySymmetric;open symmetric key MySymmetric decryption by password='[email protected]'; --Use New Passwordselect convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol))) from EnryptTestalter symmetric key MySymmetric drop encryption by password = '[email protected]'--Drop the new Passwordclose symmetric key MySymmetric;go-- 删除对称密钥-- https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspxdrop symmetric key MySymmetric;go-- 删除测试列alter table EnryptTest drop column SymmetricColgo
【示例】
-- 测试数据/*drop certificate Mycertificate;godrop master keygodrop table EnryptTestgo*/create table EnryptTest( id int not null primary key, EnryptData nvarchar(20),)goinsert into EnryptTestvalues(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');goselect * from EnryptTest;alter table EnryptTest add CertificateCol varbinary(max)go--创建主密钥create master key encryption by password = N'[email protected]'go/*key_id thumbprint crypt_type crypt_type_desc crypt_property------ ---------- ---------- --------------------- ------------------101 0x01 ESKM ENCRYPTION BY MASTER KEY 0x7A2FEDA8139F1DE8F3377424C120DBDB8E1F7EAAEC1BBD73E72AC04F5CEECBFAC15FC7E130CA1756281EA0D8E6997F44101 NULL ESKP ENCRYPTION BY PASSWORD 0x61D8F28F12CE4A1247F91E0ED828F2E937206E5D69B0754EE76756567AB428CBD5B54B76BCD1FC15E5E12202DBA4E187*/--创建证书，因为默认使用主密钥加密，此处不需要密码create certificate Mycertificatewith 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))goselect *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol)) from EnryptTest;go--现在删除“服务主密钥”alter master key drop encryption by service master keygo--再查询数据，没有解密出来。不自动使用主密钥加密解密了select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol)) from EnryptTest;go--这时需要显式打开主密钥,使用主密钥密码加密解密open master key decryption by password = N'[email protected]'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;godrop master keygodrop table EnryptTestgo参考:服务主密钥:https://msdn.microsoft.com/zh-cn/library/ms189060(v=sql.90).aspx
插图2张：
