介紹

　　sql server 2005一個令人激動的特性是內置了加密的功能。在這個新版的sql server中，開發團隊直接在t-sql中加入了加密工具、證書創建和密鑰管理的功能。對于因為法律要求或商業需求而需要加密表中的數據的人來說，這是一個好禮物。對于猶豫是否用加密來保證數據安全的人來說，做決定也更容易了。這篇文章介紹新的加密功能是怎么工作，怎么使用。

　　tsql現在支持使用對稱密鑰和非對稱密鑰，證書和密碼。本文介紹如何創建、管理和使用對稱密鑰和證書。

　　根據涉及的內容，我決定把本文分為三節：

　　第一部分：服務主密鑰和數據庫主密鑰

　　第二部分：證書

　　第三部分：對稱密鑰

　　1. 服務主密鑰和數據庫主密鑰

　　圖：sql server 2005加密層次結構

　　1.1 服務主密鑰

　　當第一次需要使用服務主密鑰對鏈接服務器密碼、憑據或數據庫主密鑰進行加密時，便會自動生成服務主密鑰。服務主密鑰為 sql server 加密層次結構的根。服務主密鑰直接或間接地保護樹中的所有其他密鑰和機密內容。使用本地計算機密鑰和 windows 數據保護 api 對服務主密鑰進行加密。該 api 使用從 sql server 服務帳戶的 windows 憑據中派生出來的密鑰。

　　 因為服務主密鑰是自動生成且由系統管理的，它只需要很少的管理。服務主密鑰可以通過backup service master key語句來備份，格式為：

　　backup service master key to file = 'path_to_file' encryption by password = 'password'

　　'path_to_file' 指定要將服務主密鑰導出到的文件的完整路徑（包括文件名）。此路徑可以是本地路徑，也可以是網絡位置的 unc 路徑。

　　'password' 用于對備份文件中的服務主密鑰進行加密的密碼。此密碼應通過復雜性檢查。

　　應當對服務主密鑰進行備份，并將其存儲在另外一個單獨的安全位置。創建該備份應該是首先在服務器中執行的管理操作之一。

　　如果需要從備份文件中恢復服務主密鑰，使用restore service master key語句。

restore service master key from file = 'path_to_file'
decryption by password = 'password' [force]

　　'path_to_file' 指定存儲服務主密鑰的完整路徑（包括文件名）。path_to_file 可以是本地路徑，也可以是網絡位置的 unc 路徑。

　　password = 'password' 指定對從文件中導入的服務主密鑰進行解密時所需的密碼。

　　force 即使存在數據丟失的風險，也要強制替換服務主密鑰。

　　注：如果你在使用restore service master key時不得不使用force選項，你可能會遇到部分或全部加密數據丟失的情況。

　　如果你的服務主密鑰泄露了，或者你想更改sql server服務帳戶，你可以通過alterservice master key語句重新生成或者恢復服務主密鑰。它的用法請參考聯機叢書。

　　因為服務主密鑰是sql server自動生成的，所以，它沒有對應的create和drop語句。

　　1.2 數據庫主密鑰

　　正如每個sql server有一個服務主密鑰，每個數據庫有自己的數據庫主密鑰。數據庫主密鑰通過create master key語句生成：

　　create master key encryption by password = 'password'

　　這個語句創建數據庫主密鑰，使用指定的密碼加密它，并保存在數據庫中。同時，數據庫主密鑰也被使用服務主密鑰加密之后保存在master數據庫中，這就是所謂的“自動密鑰管理”。這個特性我們待會再講。

　　象服務主密鑰一樣，你可以備份和恢復數據庫主密鑰。使用backup master key備份數據庫主密鑰。語法類似于備份服務主密鑰：

　　backup master key to file = 'path_to_file'

　　encryption by password = 'password'

　　恢復數據庫主密鑰使用restore master key語句，它需要使用decryption by password子句提供備份時指定的加密密碼，還要使用encryption by password子句，sql server使用它提供的密碼來加密數據庫主密鑰之后保存在數據庫中。

restore master key from file = 'path_to_file'
　　decryption by password = 'password'
　　encryption by password = 'password'
　　[ force ]

　　同樣，force表示你將忽略在解密過程中的錯誤。

　　建議你在創建了數據庫主密鑰之后立即備份數據庫主密鑰，并把它保存到一個安全的地方。同樣，使用force語句可能導致已加密數據的丟失。

　　要刪除數據庫主密鑰，使用drop master key語句，它刪除當前數據庫的主密鑰。在執行之前，確定你在正確的數據庫上下文中。

　　1.3 自動密鑰管理

　　當創建數據庫主密鑰時，它被使用提供的密碼加密然后被保存到當前數據庫中。同時，它被使用服務主密鑰加密并保存到master數據庫中。這份保存的數據庫主密鑰允許服務器在需要的時候解密數據庫主密鑰，這就是自動密鑰管理。沒有自動密鑰管理的話，你必須在每次使用證書或密鑰加密或解密數據（它需要使用數據庫主密鑰）時使用open master key語句同時提供加密的密碼。使用自動密鑰管理，你不需要執行open master key語句，也不需要提供密碼。

　　自動密鑰管理的缺點就是每個sysadmin角色的成員都能夠解密數據庫主密鑰。你可以通過alter master key語句的drop encryption by service master key子句，從而不使用自動密鑰管理。alter master key的使用方法參見聯機叢書。

　　 2. 證書

　　2.1 創建證書

　　當你配置好服務主密鑰和數據庫主密鑰后，你就可以創建證書了。sql server可以創建自簽名的x.509證書。使用create certificate語句來創建證書：

create certificate certificate_name [ authorization user_name ]
　　{ from <existing_keys> | <generate_new_keys> }
　　[ active for begin_dialog =　{ on | off } ]
　　
<existing_keys> ::=
　　assembly assembly_name
　　| {
　　　　[ executable ] file = 'path_to_file'
　　　　[ with private key ( <private_key_options> ) ]
　　　}
　　
<generate_new_keys> ::=
　　[ encryption by password = 'password']
　　with subject = 'certificate_subject_name'
　　[ , <date_options> [ ,...n ] ]
　　
<private_key_options> ::=
　　file = 'path_to_private_key'
　　　[ , decryption by password = 'password' ]
　　　[ , encryption by password = 'password' ]
　　
<date_options> ::=
start_date = 'mm/dd/yyyy' | expiry_date = 'mm/dd/yyyy'

　　create certificate語句有這么多的選項，幸運的是，大多數時候只用到很少的選項。下面的語句創建一個使用密碼來保護的證書：

create certificate testcertificate
　 encryption by password = '[email protected]$$w0rd'
　 with subject = 'this is a test certificate',
　 start_date = '1/1/2006',
　 expiry_date = '12/31/2008';

最大的網站源碼資源下載站，

　　如果不使用encryption by password子句，證書將使用數據庫主密鑰來保護。如果不指定start_date子句，將使用執行此命令的日期來填寫證書的start date字段。

　　除了create certificate，sql server還提供了drop certificate,alter certificate,backup certificate語句來管理證書。

　　注：不使用restore語句來恢復證書。使用create certificate語句來恢復已經備份的證書。

　　2.2 使用證書來加密和解密

　　通過內置的函數encryptbycert，decryptbycert和cert_id，可以使用證書來加密和解密數據。cert_id函數得到指定名字的證書的id。格式為：

cert_id ( 'cert_name' )
cert_name為證書的名字。
encryptbycert函數需要證書id，格式為：
encryptbycert ( certificate_id , { 'cleartext' | @cleartext } )

　　certificate_id為通過cert_id函數得到的證書id，cleartext為要加密的明文。類型為 nvarchar、char、varchar、binary、varbinary 或 nchar。encryptbycert函數的返回值是最大大小為 8,000 個字節的 varbinary。

　　decryptbycert語句用來解密先前使用證書加密的數據。格式為：

　　decryptbycert (certificate_id, { 'ciphertext' | @ciphertext } [ , { 'cert_password' | @cert_password } ] )

　　同樣，certificate_id為通過cert_id函數得到的證書id，ciphertext是加密后的數據。如果創建證書時使用了encrypt by password，則cert_password是當時創建時設定的密碼，如果沒有使用encrypt by password，那這里也不需要cert_password。

　　下面的腳本創建數據庫主密鑰，創建一個測試證書，使用證書進行加密、解密。

-- sample t-sql script to demonstrate certificate encryption
　　
-- use the adventureworks database
use adventureworks;
　　
-- create a database master key
create master key encryption by password = '[email protected]';
　　
-- create a temp table
create table person.#temp
(contactid int primary key,
firstname nvarchar(200),
middlename nvarchar(200),
lastname nvarchar(200),
efirstname varbinary(200),
emiddlename varbinary(200),
elastname varbinary(200));
　　
-- create a test certificate, encrypted by the dmk
create certificate testcertificate
with subject = 'adventureworks test certificate',
expiry_date = '10/31/2009';
　　
-- encryptbycert demonstration encrypts 100 names from the person.contact table
insert
into person.#temp (contactid, efirstname, emiddlename, elastname)
select contactid, encryptbycert(cert_id('testcertificate'), firstname),
encryptbycert(cert_id('testcertificate'), middlename),
encryptbycert(cert_id('testcertificate'), lastname)
from person.contact
where contactid <= 100;
　　
-- decryptbycert demonstration decrypts the previously encrypted data
update person.#temp
set firstname = decryptbycert(cert_id('testcertificate'), efirstname),
middlename = decryptbycert(cert_id('testcertificate'), emiddlename),
lastname = decryptbycert(cert_id('testcertificate'), elastname);
　　
-- view the results
select *
from person.#temp;
　　
-- clean up work: drop temp table, test certificate and master key
drop table person.#temp;
drop certificate testcertificate;
drop master key;

　　 3. 對稱密鑰

　　可以使用證書來創建用來在數據庫中進行加密和解密的對稱密鑰。使用create symmetric key語句：

create symmetric key key_name [ authorization owner_name ]
　　with <key_options> [ , ... n ]
　　encryption by <encrypting_mechanism> [ , ... n ]
　　
<encrypting_mechanism> ::=
　　certificate certificate_name |
　　password = 'password' |
　　symmetric key symmetric_key_name |
　　asymmetric key asym_key_name
　　
<key_options> ::=
　　key_source = 'pass_phrase' |
　　algorithm = <algorithm> |
　　identity_value = 'identity_phrase'
　　
<algorithm> ::=
des | triple_des | rc2 | rc4 | desx | aes_128 | aes_192 | aes_256

　　同create certificate語句一樣，create symmetric key語句相當靈活。多數情況下，你只需使用少量的選項。如下例中，創建一個對稱密鑰，它使用前節中創建的證書來加密：

　　create symmetric key testsymmetrickey with algorithm = triple_des encryption by certificate testcertificate;

　　對稱密鑰可以用另一個對稱密鑰、非對稱密鑰、密碼或證書來加密。系統也提供了alter symmetric key和drop symmetric key語句來管理對稱密鑰。這些語句的用法請參考聯機叢書。

　　當刪除密鑰和證書時，刪除的順序很重要。sql server不會允許你刪除還在被用來加密其他密鑰的證書和密鑰。

　　3.1 對稱密鑰加密

　　sql server使用下面的函數來進行對稱密鑰加密：encryptbykey，decryptbykey和key_guid。key_guid返回特定對稱密鑰的guid。語法為：

key_guid( 'key_name' )
encryptbykey的語法為：
encryptbykey( key_guid, { 'cleartext' | @cleartext }
　　　　　　 [ , { add_authenticator | @add_authenticator }
　　　　　　　 , { authenticator | @authenticator } ]
　　　　　　)

　　key_guid是對稱密鑰的guid，cleartext為明文，add_authenticator和authenticator指示是否使用驗證器來禁止對加密字段進行整個值替換。

　　decryptbykey做encryptbykey相反的事情，它解密先前使用encryptbykey加密的數據。語法為：

decryptbykey( { 'ciphertext' | @ciphertext }
　　　　　　　[ , add_authenticator
　　　　　　　　, { authenticator | @authenticator } ]
　　　　　　)

　　ciphertext是密文。add_authenticator，authenticator，如果指定，必須和先前encryptbykey時指定相同的值。decryptbykey不需要你顯示指定對稱密鑰的guid。但使用的對稱密鑰必須已經在當前數據庫中打開。open symmetric key用來打開對稱密鑰。

　　下面的代碼演示使用對稱密鑰來加密和解密。

-- use the adventureworks database
use adventureworks;
　　
-- create a database master key
create master key encryption by password = '[email protected]';
　　
-- create a temp table
create table person.#temp
(contactid　 int primary key,
firstname　 nvarchar(200),
middlename　nvarchar(200),
lastname　　nvarchar(200),
efirstname　varbinary(200),
emiddlename varbinary(200),
elastname　 varbinary(200));
　　
-- create a test certificate
create certificate testcertificate
　 with subject = 'adventureworks test certificate',
　 expiry_date = '10/31/2009';
　　
-- create a symmetric key
create symmetric key testsymmetrickey
　　with algorithm = triple_des
　　encryption by certificate testcertificate;
open symmetric key testsymmetrickey
　 decryption by certificate testcertificate;
　　
-- encryptbykey demonstration encrypts 100 names from the person.contact table
insert
into person.#temp (contactid, efirstname, emiddlename, elastname)
select contactid,
　　encryptbykey(key_guid('testsymmetrickey'), firstname),
　　encryptbykey(key_guid('testsymmetrickey'), middlename),
　　encryptbykey(key_guid('testsymmetrickey'), lastname)
from person.contact
where contactid <= 100;
　　
-- decryptbykey demonstration decrypts the previously encrypted data
update person.#temp
set firstname = decryptbykey(efirstname),
　　middlename = decryptbykey(emiddlename),
　　lastname = decryptbykey(elastname);
　　
-- view the results
select *
from person.#temp;
　　
-- clean up work:　drop temp table, symmetric key, test certificate and master key
drop table person.#temp;
close symmetric key testsymmetrickey;
drop symmetric key testsymmetrickey;
drop certificate testcertificate;
drop master key;

　　4.結論

　　sql server內置了用來加密敏感數據的密鑰、證書等函數。使用這個功能可以極大的增加數據庫和應用的安全性。