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 = 'p@ssw0rd';

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