SQLServer 資料加密解密：常用的加密解密（一）

都是基本示例，更多參考官方文檔：

1. Transact-SQL 函數

2. 資料庫密鑰

3. 證書

4. 非對稱密鑰

5. 對稱密鑰

[sql]  view plain  copy  

1. --  drop table EnryptTest  
2. create table EnryptTest  
3. (  
4.     id int not null primary key,  
5.     EnryptData nvarchar(20),  
6. )  
7. insert into EnryptTest  
8. values(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');  
9. select * from EnryptTest;  

【Transact-SQL 函數加密】

[sql]  view plain  copy  

1. --  使用 TRIPLE DES 算法（128 密鑰位長度）的通行短語加密資料。  
2. --  添加測試列  
3. alter table EnryptTest add PassPhrase varbinary(256)  
4. alter table EnryptTest add PassPhrase2 varbinary(256)--用于驗證器驗證  
5. --  加密(EncryptByPassPhrase)  
6. --  https://technet.microsoft.com/zh-cn/library/ms190357%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396  
7. update EnryptTest set PassPhrase = EncryptByPassPhrase('Hello.kk',EnryptData)  
8. go  
9. update EnryptTest   
10. set PassPhrase2 = EncryptByPassPhrase  
11.     (  
12.       'Hello.kk'            --用于生成對稱密鑰的通行短語  
13.     , EnryptData            --要加密的明文  
14.     , 1                     --訓示是否将驗證器與明文一起加密。如果将添加驗證器，則為 1  
15.     , convert(varbinary,id) --用于派生驗證器的資料(如 主鍵)  
16.     )  
17. go  
18. --  解密(DecryptByPassPhrase)  
19. --  https://technet.microsoft.com/zh-cn/library/ms188910%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396  
20. select convert(nvarchar,DecryptByPassphrase('Hello.kk',PassPhrase)) from EnryptTest;  
21. go  
22. select convert(nvarchar,DecryptByPassphrase(  
23.       'Hello.kk'    --生成解密密鑰的通行短語  
24.     , PassPhrase2   --要解密的加密文本varbinary   
25.     , 1             --添加驗證器  
26.     , convert(varbinary,id)))--驗證器為主鍵  
27. from EnryptTest;  
28. go  
29. --附：未用驗證器的，資料并不安全  
30. --如：把所有id的密碼都改為與A一樣，其他密碼的解密與A一樣，别人就有可能登入其他賬号  
31. update EnryptTest set PassPhrase = (select PassPhrase from EnryptTest where id=4)  
32. go  
33. select id,EnryptData,convert(nvarchar,DecryptByPassphrase('Hello.kk',PassPhrase))   
34. from EnryptTest;  
35. --  删除測試列  
36. alter table EnryptTest drop column PassPhrase   
37. alter table EnryptTest drop column PassPhrase2  
38. go  

[sql]  view plain  copy  

1. DECLARE @ENPWD VARBINARY(MAX)   
2. DECLARE @DEPWD NVARCHAR(100)  
3. DECLARE @ENSTR NVARCHAR(100)    
4. SET @ENSTR = 'hello.KK' --加密密碼  
5. --加密  
6. SELECT @ENPWD = ENCRYPTBYPASSPHRASE( @ENSTR, N'13500000000')  
7. SELECT @ENPWD  
8. --解密  
9. SELECT @DEPWD =CAST( DECRYPTBYPASSPHRASE(@ENSTR,@ENPWD) AS NVARCHAR(MAX))  
10. SELECT @DEPWD  
11. go  

【資料庫主密鑰】

[sql]  view plain  copy  

1. select * from sys.key_encryptions  
2. select * from sys.crypt_properties  
3. --  建立資料庫主密鑰  
4. --  https://technet.microsoft.com/zh-cn/library/ms174382(v=sql.105).aspx  
5. create master key encryption by password = N'[email protected]' --必須符合Windows密碼政策要求  
6. go  
7. --  打開目前資料庫的資料庫主密鑰  
8. --  https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspx  
9. open master key decryption by password = N'[email protected]'  
10. go  
11. --  更改資料庫主密鑰的屬性  
12. --  https://technet.microsoft.com/zh-cn/library/ms186937(v=sql.105).aspx  
13. alter master key regenerate with encryption by password = N'[email protected]'  
14. alter master key add encryption by password = N'[email protected]'  
15. alter master key drop encryption by password = N'[email protected]'  
16. alter master key add encryption by service master key  
17. alter master key drop encryption by service master key  
18. --  導出資料庫主密鑰  
19. --  https://technet.microsoft.com/zh-cn/library/ms174387(v=sql.105).aspx  
20. backup master key   
21. to file = N'D:\XXDB_MasterKey'   
22. encryption by password = N'[email protected]'  
23. go  
24. --  從備份檔案中導入資料庫主密鑰  
25. --  https://technet.microsoft.com/zh-cn/library/ms186336(v=sql.105).aspx  
26. restore master key   
27.     from file = N'D:\XXDB_MasterKey'  
28.     decryption by password = N'[email protected]'  
29.     encryption by password = N'[email protected]' --New Password  
30. go  
31. --  從目前資料庫中删除主密鑰  
32. --  https://technet.microsoft.com/zh-cn/library/ms180071(v=sql.105).aspx  
33. drop master key  
34. go  

【證書】

[sql]  view plain  copy  

1. --  證書和非對稱密鑰使用資料庫級的内部公鑰加密資料，并且使用資料庫級内部私鑰解密資料  
2. --  當使用資料庫主密鑰對私鑰進行加密時，不需要 ENCRYPTION BY PASSWORD 選項。私鑰使用資料庫主密鑰進行加密  
3. --（有點難了解，最後給出例子）  
4. select * from sys.key_encryptions  
5. select * from sys.crypt_properties  
6. select * from sys.certificates  
7. select * from EnryptTest  
8. --  添加測試列  
9. alter table EnryptTest add CertificateCol varbinary(max)  
10. go  
11. --  建立證書  
12. --  https://technet.microsoft.com/zh-cn/library/ms187798%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396  
13. create certificate Mycertificate  
14. encryption by password = N'[email protected]' --加密密碼  
15. with subject = N'EnryptData certificate',       --證書描述    
16. start_date = N'20150401',   --證書生效日   
17. expiry_date = N'20160401';  --證書到期日    
18. go  
19. --  使用證書的公鑰加密資料  
20. --  https://msdn.microsoft.com/zh-cn/library/ms188061(v=sql.105).aspx  
21. update EnryptTest   
22. set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))  
23. go  
24. --  用證書的私鑰解密資料  
25. --  https://msdn.microsoft.com/zh-cn/library/ms178601(v=sql.105).aspx  
26. select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol,N'[email protected]'))  
27. from EnryptTest;  
28. go  
29. --  修改私鑰密碼   
30. --  https://msdn.microsoft.com/zh-cn/library/ms189511(v=sql.105).aspx  
31. alter certificate mycertificate   
32. with private key (  
33.     decryption by password = N'[email protected]',   
34.     encryption by password = N'[email protected]')  
35. go  
36. --  從證書中删除私鑰   
37. alter certificate mycertificate remove private key  
38. go  
39. --  備份證書  
40. --  https://msdn.microsoft.com/zh-cn/library/ms178578(v=sql.105).aspx  
41. backup certificate mycertificate   
42. to file = N'D:\mycertificate.cer' --用于加密的證書備份路徑  
43. with private key (   
44.     file = N'D:\mycertificate_saleskey.pvk' , --用于解密證書私鑰檔案路徑   
45.     decryption by password = N'[email protected]' ,--對私鑰進行解密的密碼  
46.     encryption by password = N'[email protected]' );--對私鑰進行加密的密碼  
47. go  
48. --  建立/還原證書  
49. create certificate mycertificate   
50. from file = N'D:\mycertificate.cer'   
51. with private key (  
52.     file = N'D:\mycertificate_saleskey.pvk',   
53.     decryption by password = '[email protected]');  
54. go  
55. --  删除對稱密鑰  
56. --  https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspx  
57. drop certificate  Mycertificate;  
58. go  
59. --  删除測試列  
60. alter table EnryptTest drop column CertificateCol;  
61. go  

【非對稱密鑰】

[sql]  view plain  copy  

1. --  預設情況下，私鑰受資料庫主密鑰保護  
2. select * from sys.key_encryptions  
3. select * from sys.crypt_properties  
4. select * from sys.certificates  
5. select * from sys.asymmetric_keys  
6. select * from sys.openkeys  
7. select * from EnryptTest  
8. --  添加測試列  
9. alter table EnryptTest add AsymmetricCol varbinary(max)  
10. go  
11. --  建立非對稱密鑰  
12. --  https://msdn.microsoft.com/zh-cn/library/ms174430(v=sql.105).aspx  
13. create asymmetric key MyAsymmetric   
14. with   
15.     algorithm=rsa_512   
16.     encryption by password='[email protected]';  
17. go    
18. --  加密（EncryptByAsymKey）  
19. --  https://msdn.microsoft.com/ZH-CN/library/ms186950(v=sql.105).aspx  
20. update EnryptTest   
21. set AsymmetricCol = EncryptByAsymKey(asymkey_id ('MyAsymmetric'),convert(varchar(max ),EnryptData))    
22. go  
23. --  解密（DecryptByAsymKey）  
24. --  https://msdn.microsoft.com/ZH-CN/library/ms189507(v=sql.105).aspx  
25. select *,convert(varchar(max),DecryptByAsymKey(asymkey_id('MyAsymmetric'),AsymmetricCol,N'[email protected]'))  
26. from EnryptTest  
27. go  
28. --  更改非對稱密鑰屬性  
29. --  https://msdn.microsoft.com/zh-cn/library/ms187311(v=sql.105).aspx  
30. --  更改私鑰密碼  
31. alter asymmetric key MyAsymmetric   
32.     with private key (  
33.     decryption by password = N'[email protected]',--原私鑰密碼  
34.     encryption by password = N'[email protected]');--新私鑰密碼  
35. go  
36. --  删除私鑰,隻保留公鑰  
37. --  如果将非對稱密鑰映射到 EKM 裝置上的可擴充密鑰管理 (EKM) 密鑰并且未指定 REMOVE PROVIDER KEY 選項，  
38. --  則會從資料庫中删除該密鑰，但不會從裝置上删除它。這時會發出一條警告。  
39. alter asymmetric key MyAsymmetric remove private key;  
40. go  
41. --  删除非對稱密鑰  
42. --  https://msdn.microsoft.com/ZH-CN/library/ms188389(v=sql.105).aspx  
43. drop symmetric key MyAsymmetric ;  
44. go  
45. --  删除測試列  
46. alter table EnryptTest drop column AsymmetricCol  
47. go  

【對稱密鑰】

[sql]  view plain  copy  

1. --  也稱為單密鑰加密,采用單鑰密碼系統的加密方法，同一個密鑰可以同時用作資訊的加密和解密.  
2. --  非對稱密鑰消耗多些系統性能，一般使用對稱密鑰加密資料，使用非對稱密鑰保護對稱密鑰  
3. select * from sys.key_encryptions  
4. select * from sys.crypt_properties  
5. select * from sys.certificates  
6. select * from sys.asymmetric_keys  
7. select * from sys.openkeys  
8. select * from sys.symmetric_keys  
9. select * from EnryptTest  
10. --  添加測試列  
11. alter table EnryptTest add SymmetricCol varbinary(max)  
12. go  
13. --  建立對稱密鑰  
14. --  https://msdn.microsoft.com/zh-cn/library/ms188357(v=sql.105).aspx  
15. create symmetric key MySymmetric    --以密碼加密的對稱密鑰  
16. with   
17.     algorithm=aes_128   
18.     encryption by password='[email protected]';  
19. go  
20. create symmetric key MySymmetric    --以非對稱密鑰加密的對稱密鑰  
21. with   
22.     algorithm=aes_128   
23.     encryption by asymmetric key MyAsymmetric  
24. go  
25. --  打開對稱密鑰(打開才能有效使用加密解密函數)  
26. --  https://msdn.microsoft.com/zh-cn/library/ms190499(v=sql.105).aspx  
27. open symmetric key MySymmetric decryption by password='[email protected]';  
28. go  
29. open symmetric key MySymmetric decryption by asymmetric key MyAsymmetric with password='[email protected]';  
30. go  
31. --  加密資料  
32. --  https://technet.microsoft.com/zh-cn/library/ms174361%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396  
33. update EnryptTest set SymmetricCol = encryptbykey(key_guid('MySymmetric'),convert(varchar(max),EnryptData))  
34. go  
35. --  解密資料  
36. --  https://technet.microsoft.com/zh-cn/library/ms181860(v=sql.105).aspx  
37. select *,convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol)))    
38. from EnryptTest  
39. go  
40. --  關閉對稱密鑰，或關閉在目前會話中打開的所有對稱密鑰  
41. --  https://msdn.microsoft.com/zh-cn/library/ms177938%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396  
42. --  close all symmetric keys;   
43. close symmetric key MySymmetric;  
44. go  
45. --  alter symmetric 添加或删除新的加密方式(如添加多多個密碼，任何一個密碼都可用)  
46. --  https://msdn.microsoft.com/zh-cn/library/ms189440(v=sql.105).aspx  
47. open symmetric key MySymmetric decryption by password='[email protected]';  
48. alter symmetric key MySymmetric add encryption by password = '[email protected]' --New another Password  
49. close symmetric key MySymmetric;  
50. open symmetric key MySymmetric decryption by password='[email protected]'; --Use New Password  
51. select convert(varchar(max ) ,convert (varchar(max ),decryptbykey(SymmetricCol))) from EnryptTest  
52. alter symmetric key MySymmetric drop encryption by password = '[email protected]'--Drop the new Password  
53. close symmetric key MySymmetric;  
54. go  
55. --  删除對稱密鑰  
56. --  https://msdn.microsoft.com/zh-cn/library/ms182698(v=sql.105).aspx  
57. drop symmetric key MySymmetric;  
58. go  
59. --  删除測試列  
60. alter table EnryptTest drop column SymmetricCol  
61. go  

【主密鑰證書示例】

[sql]  view plain  copy  

1. --  測試資料  
2. create table EnryptTest  
3. (  
4.     id int not null primary key,  
5.     EnryptData nvarchar(20),  
6. )  
7. go  
8. insert into EnryptTest  
9. values(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');  
10. go  
11. select * from EnryptTest;  
12. alter table EnryptTest add CertificateCol varbinary(max)  
13. go  
14. --建立主密鑰  
15. create master key encryption by password = N'[email protected]'  
16. go  
17. --建立證書，因為預設使用主密鑰加密，此處不需要密碼  
18. create certificate Mycertificate  
19. with subject = N'EnryptData certificate',  
20. start_date = N'20150401',  
21. expiry_date = N'20160401';  
22. go  
23. --加密解密都自動使用服務主密鑰加密了。即使使用“close master key ”也不起作用  
24. update EnryptTest   
25. set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))  
26. go  
27. select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol))   
28. from EnryptTest;  
29. go  
30. --現在删除“服務主密鑰”  
31. alter master key drop encryption by service master key  
32. go  
33. --再查詢資料，沒有解密出來。不自動使用主密鑰加密解密了  
34. select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol))   
35. from EnryptTest;  
36. go  
37. --這時需要顯式打開主密鑰,使用主密鑰密碼加密解密  
38. open master key decryption by password = N'[email protected]'  
39. go  
40. --再查詢資料，解密出來了。  
41. select *,CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('Mycertificate'),CertificateCol))   
42. from EnryptTest;  
43. go  
44. --最後關閉主密鑰  
45. close master key   
46. go  
47. --檢視主密鑰，少了"ENCRYPTION BY MASTER KEY",沒有了主密鑰進行加密,而是使用密碼進行加密  
48. select * from sys.key_encryptions  
49. --删除測試資料  
50. drop certificate  Mycertificate;  
51. go  
52. drop master key  
53. go  
54. drop table EnryptTest  
55. go  
56. 參考:  
57. 服務主密鑰:https://msdn.microsoft.com/zh-cn/library/ms189060(v=sql.90).aspx  

【證書備份還原示例】

[sql]  view plain  copy  

1. --  drop table EnryptTest    
2. create table EnryptTest    
3. (    
4.     id int not null primary key,    
5.     EnryptData nvarchar(20),    
6. )   
7. go   
8. insert into EnryptTest    
9. values(1,N'888888'),(2,N'888888'),(3,N'123456'),(4,N'A');    
10. go  
11. alter table EnryptTest add CertificateCol varbinary(max)  --證書加密的列  
12. go    
13. select * from EnryptTest;   
14. --将相關資訊删除  
15. drop certificate  Mycertificate;  
16. go    
17. drop master key    
18. go    
19. --  建立以密碼加密的證書  
20. create certificate Mycertificate    
21. encryption by password = N'[email protected]'  
22. with subject = N'EnryptData certificate',   
23. start_date = N'20150401',  
24. expiry_date = N'20160401';   
25. go    
26. --  證書加密資料  
27. update EnryptTest set CertificateCol = EncryptByCert(CERT_ID('Mycertificate'),CONVERT(VARCHAR(MAX),EnryptData))    
28. go  
29. --  解密(正常)  
30. select *,convert(varchar(max ),decryptbycert(cert_id('Mycertificate'),CertificateCol,N'[email protected]'))   
31. from EnryptTest;    
32. go   
33. --  備份證書  
34. backup certificate mycertificate     
35. to file = N'D:\mycertificate.cer'    
36. with private key (     
37.     file = N'D:\mycertificate_saleskey.pvk' ,  
38.     decryption by password = N'[email protected]' ,  
39.     encryption by password = N'[email protected]' );  
40. go  
41. --  删除證書  
42. drop certificate  Mycertificate;  
43. go  
44. --  解密(失敗)  
45. select *,convert(varchar(max ),decryptbycert(cert_id('Mycertificate'),CertificateCol,N'[email protected]'))   
46. from EnryptTest;    
47. go   
48. --  還原證書  
49. create certificate mycertificate     
50. from file = N'D:\mycertificate.cer'     
51. with private key (    
52.     file = N'D:\mycertificate_saleskey.pvk',     
53.     decryption by password = N'[email protected]' ,    
54.     encryption by password = N'[email protected]' );  --新證書密碼  
55. go  
56. --  解密(正常)  
57. select *,convert(varchar(max ),decryptbycert(cert_id('Mycertificate'),CertificateCol,N'[email protected]')) --新證書密碼  
58. from EnryptTest;    
59. go   
60. --  删除測試資料  
61. drop certificate  Mycertificate;  
62. go   
63. drop table EnryptTest   
64. go  
65. 沒有資料庫主密鑰情況下，使用密碼加密的證書。證書直接加密解密資料，備份還原後，對之前的加密資料仍正常解密，因為備用還原都是同一個證書。而使用證書加密的對稱密鑰，對稱密鑰不能備份，删除重建後，key_guid不一樣了，之前使用對稱密鑰加密的資料已經不能使用新的對稱密鑰解密了。檢視select * 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