詳述SQL Server 2005對稱密鑰加密技術

介紹

    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 = 

   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 = ; -- 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 = ;

-- 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内置了用來加密敏感資料的密鑰、證書等函數。使用這個功能可以極大的增加資料庫和應用的安全性。