都是基本示例，更多参考官方文档：
1. Transact-sql 函数
2. 数据库密钥
3. 证书
4. 非对称密钥
5. 对称密钥
[sql] view plain copy -- drop table EnryptTest create table EnryptTest ( ID int not null primary key, EnryptData nvarchar(20), ) insert into EnryptTest values(1,N'888888'),(2,(3,N'123456'),(4,N'A'); select * from EnryptTest;
【Transact-sql 函数加密】
copy /***********************************【Transact-SQL 函数加密】********************************/ -- 使用 TRIPLE DES 算法（128 密钥位长度）的通行短语加密数据。 -- 添加测试列 alter table EnryptTest add PassPhrase varbinary(256) 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) --用于派生验证器的数据(如 主键) ) -- 解密(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; 'Hello.kk' --生成解密密钥的通行短语 --要解密的加密文本varbinary --添加验证器 --验证器为主键 --附：未用验证器的，数据并不安全 --如：把所有id的密码都改为与A一样，其他密码的解密与A一样，别人就有可能登录其他账号 set PassPhrase = (select PassPhrase from EnryptTest where id=4) select id,EnryptData, EnryptTest; -- 删除测试列 drop column PassPhrase column PassPhrase2 go
copy 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
【数据库主密钥】
copy /***************************************【数据库主密钥】***********************************/ from sys.key_encryptions 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密码策略要求 -- 打开当前数据库的数据库主密钥 -- https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspx open master key decryption password = N'Hello@MyMasterKey' go -- 更改数据库主密钥的属性 -- https://technet.microsoft.com/zh-cn/library/ms186937(v=sql.105).aspx alter master key regenerate with encryption password = N'Hello@MyMasterKey' key add encryption password = N'Hello@kk' drop encryption password = N'Hello@kk' by service master key key -- 导出数据库主密钥 -- https://technet.microsoft.com/zh-cn/library/ms174387(v=sql.105).aspx backup master key to file = N'D:\XXDB_MasterKey' encryption -- 从备份文件中导入数据库主密钥 restore master key from file = N'D:\XXDB_MasterKey' decryption encryption --New Password -- 从当前数据库中删除主密钥 -- https://technet.microsoft.com/zh-cn/library/ms180071(v=sql.105).aspx drop master go
【证书】
copy /*****************************************【证书】*************************************/ -- 证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据 -- 当使用数据库主密钥对私钥进行加密时，不需要 ENCRYPTION BY PASSWORD 选项。私钥使用数据库主密钥进行加密 --（有点难理解，最后给出例子） from sys.key_encryptions from sys.crypt_properties from sys.certificates from EnryptTest -- 添加测试列 add CertificateCol varbinary(max) -- 创建证书 -- https://technet.microsoft.com/zh-cn/library/ms187798%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396 create certificate Mycertificate encryption password = N'Hello@Mycertificate' --加密密码 with subject = N'EnryptData certificate', --证书描述 start_date = N'20150401', --证书生效日 expiry_date = N'20160401'; --证书到期日 -- 使用证书的公钥加密数据 -- https://msdn.microsoft.com/zh-cn/library/ms188061(v=sql.105).aspx set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),147); background-color:inherit">CONVERT(VARCHAR(MAX),EnryptData)) -- 用证书的私钥解密数据 -- https://msdn.microsoft.com/zh-cn/library/ms178601(v=sql.105).aspx select *,153); background-color:inherit; font-weight:bold">MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol,N'Hello@Mycertificate')) -- 修改私钥密码 -- https://msdn.microsoft.com/zh-cn/library/ms189511(v=sql.105).aspx alter certificate mycertificate with private key ( password = N'Hello@Mycertificate', password = N'Hello@Mycertificate') -- 从证书中删除私钥 alter certificate mycertificate remove private -- 备份证书 -- https://msdn.microsoft.com/zh-cn/library/ms178578(v=sql.105).aspx backup certificate mycertificate to file = N'D:\mycertificate.cer' --用于加密的证书备份路径 key ( file = N'D:\mycertificate_saleskey.pvk' , --用于解密证书私钥文件路径 password = N'Hello@Mycertificate' ,--对私钥进行解密的密码 password = N'Hello@Mycertificate' );--对私钥进行加密的密码 -- 创建/还原证书 create certificate mycertificate from file = N'D:\mycertificate.cer' file = N'D:\mycertificate_saleskey.pvk',248); line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important; list-style-position:outside!important"> decryption password = 'Hello@Mycertificate'); -- 删除对称密钥 -- https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspx drop certificate Mycertificate; column CertificateCol; 【非对称密钥】
copy /***************************************【非对称密钥】*************************************/ -- 默认情况下，私钥受数据库主密钥保护 from sys.asymmetric_keys from sys.openkeys add AsymmetricCol varbinary(-- 创建非对称密钥 -- https://msdn.microsoft.com/zh-cn/library/ms174430(v=sql.105).aspx create asymmetric key MyAsymmetric with algorithm=rsa_512 encryption 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'),147); background-color:inherit">convert(varchar(max ),EnryptData)) -- 解密（DecryptByAsymKey） -- https://msdn.microsoft.com/ZH-CN/library/ms189507(v=sql.105).aspx max),DecryptByAsymKey(asymkey_id('MyAsymmetric'),AsymmetricCol,N'Hello@MyAsymmetric')) from EnryptTest -- 更改非对称密钥属性 -- https://msdn.microsoft.com/zh-cn/library/ms187311(v=sql.105).aspx -- 更改私钥密码 alter asymmetric key ( password = N'Hello@MyAsymmetric',0); background-color:inherit">--原私钥密码 password = N'Hello@MyAsymmetric');--新私钥密码 -- 删除私钥,只保留公钥 -- 如果将非对称密钥映射到 EKM 设备上的可扩展密钥管理 (EKM) 密钥并且未指定 REMOVE PROVIDER KEY 选项， -- 则会从数据库中删除该密钥，但不会从设备上删除它。这时会发出一条警告。 key MyAsymmetric remove private key; -- 删除非对称密钥 -- https://msdn.microsoft.com/ZH-CN/library/ms188389(v=sql.105).aspx drop symmetric key MyAsymmetric ; -- 删除测试列 column AsymmetricCol go
【对称密钥】
copy /***************************************【对称密钥】*************************************/ -- 也称为单密钥加密,采用单钥密码系统的加密方法，同一个密钥可以同时用作信息的加密和解密. -- 非对称密钥消耗多些系统性能，一般使用对称密钥加密数据，使用非对称密钥保护对称密钥 from sys.certificates from sys.asymmetric_keys from sys.openkeys from sys.symmetric_keys add SymmetricCol varbinary(-- 创建对称密钥 -- https://msdn.microsoft.com/zh-cn/library/ms188357(v=sql.105).aspx create symmetric key MySymmetric --以密码加密的对称密钥 algorithm=aes_128 password='Hello@MySymmetric'; --以非对称密钥加密的对称密钥 with algorithm=aes_128 by asymmetric key MyAsymmetric -- 打开对称密钥(打开才能有效使用加密解密函数) -- https://msdn.microsoft.com/zh-cn/library/ms190499(v=sql.105).aspx open symmetric key MySymmetric decryption key MyAsymmetric with -- 加密数据 -- https://technet.microsoft.com/zh-cn/library/ms174361%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396 set SymmetricCol = encryptbykey(key_guid('MySymmetric'),0); background-color:inherit">-- 解密数据 -- https://technet.microsoft.com/zh-cn/library/ms181860(v=sql.105).aspx max ) ,147); background-color:inherit">convert ( -- 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; -- alter symmetric 添加或删除新的加密方式(如添加多多个密码，任何一个密码都可用) -- https://msdn.microsoft.com/zh-cn/library/ms189440(v=sql.105).aspx password='Hello@MySymmetric'; alter symmetric key MySymmetric password = 'Hello@kk' --New another Password password='Hello@kk'; --Use New Password password = 'Hello@kk'--Drop the new Password key MySymmetric; column SymmetricCol go
【主密钥证书示例】
copy -- 测试数据 /* table EnryptTest */ into EnryptTest 'A'); --创建主密钥 /* 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 expiry_date = N'20160401'; --加密解密都自动使用服务主密钥加密了。即使使用“close master key ”也不起作用 --现在删除“服务主密钥” --再查询数据，没有解密出来。不自动使用主密钥加密解密了 --这时需要显式打开主密钥,使用主密钥密码加密解密 --再查询数据，解密出来了。 --最后关闭主密钥 close master --查看主密钥，少了"ENCRYPTION BY MASTER KEY",没有了主密钥进行加密,而是使用密码进行加密 key_id thumbprint crypt_type crypt_type_desc crypt_property 101 PASSWORD 0xFB7D746C3CF0471147ECD710B1173B69A966421089FBB813BCF108E1ED90574F5C5F0998BA44F48869E05E9093BC59E6 --删除测试数据 参考: 服务主密钥:https://msdn.microsoft.com/zh-cn/library/ms189060(v=sql.90).aspx
【证书备份还原示例】
copy -- drop table EnryptTest table EnryptTest ( ) go into EnryptTest 'A'); max) --证书加密的列 from EnryptTest; --将相关信息删除 drop certificate Mycertificate; go key -- 创建以密码加密的证书 create certificate Mycertificate password = N'Hello@Mycertificate' start_date = N'20150401',108); list-style-type:decimal-leading-zero; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important; list-style-position:outside!important"> expiry_date = N'20160401'; -- 证书加密数据 -- 解密(正常) 'Mycertificate'),N'Hello@Mycertificate')) from EnryptTest; go -- 备份证书 backup certificate mycertificate to file = N'D:\mycertificate.cer' key ( password = N'Hello@Mycertificate' ); -- 删除证书 -- 解密(失败) -- 还原证书 create certificate mycertificate from file = N'D:\mycertificate.cer' key ( file = N'D:\mycertificate_saleskey.pvk', password = N'Hello@Hello.KK' ); --新证书密码 'Hello@Hello.KK')) -- 删除测试数据 table EnryptTest 没有数据库主密钥情况下，使用密码加密的证书。证书直接加密解密数据，备份还原后，对之前的加密数据仍正常解密，因为备用还原都是同一个证书。而使用证书加密的对称密钥，对称密钥不能备份，删除重建后，key_guid不一样了，之前使用对称密钥加密的数据已经不能使用新的对称密钥解密了。查看from sys.symmetric_keys，可以看到不一样了。
加密解密函数：https://msdn.microsoft.com/zh-cn/library/ms173744.aspx
插图2张：
附录：
DECRYPTBYCERT (Transact-sql) 其他版本
用证书的私钥解密数据。
Transact-SQL 语法约定 语法
DecryptByCert ( certificate_ID,{ 'ciphertext' | @ciphertext } [,{ 'cert_password' | @cert_password } ] ) 参数 certificate_ID 数据库中证书的 ID。certificate_ID 的数据类型为 int。
ciphertext已用证书的公钥加密的数据的字符串。
@ciphertext包含已使用证书进行加密的数据的 varbinary 类型变量。
cert_password用来加密证书私钥的密码。必须为 Unicode 字符。
@cert_password类型为 nchar 或 nvarchar 的变量，其中包含用来加密证书私钥的密码。必须为 Unicode 字符。
返回类型最大大小为 8,000 个字节的 varbinary。
注释 此函数用证书的私钥解密数据。使用非对称密钥进行的加密转换会消耗大量资源。因此，EncryptByCert 和 DecryptByCert 不适合用于对用户数据的例行加密。 权限 需要对证书具有 CONTROL 权限。 示例 下面的示例从 [AdventureWorks2008R2].[ProtectedData04] 中选择标记为 data encrypted by certificate JanainaCert02 的行。此示例使用证书 JanainaCert02 的私钥对密码进行解密，首次解密时使用的是证书的密码 pGFD4bb925DGvbd2439587y。解密后的数据将从 varbinary 转换为 nvarchar。SELECT convert(nvarchar(max),DecryptByCert(Cert_ID('JanainaCert02'),ProtectedData,N'pGFD4bb925DGvbd2439587y'))FROM [AdventureWorks2008R2].[ProtectedData04] WHERE Description = N'data encrypted by certificate '' JanainaCert02''';GO 总结 以上是内存溢出为你收集整理的SQLServer 数据加密解密：常用的加密解密（一）全部内容，希望文章能够帮你解决SQLServer 数据加密解密：常用的加密解密（一）所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）