介绍
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内置了用来加密敏感数据的密钥、证书等函数。使用这个功能可以极大的增加数据库和应用的安全性。
新闻热点
疑难解答
