利用加密保护SQL Server中的敏感数据

 铃……一阵手机铃声响起，领导打来电话，语气比较沉重，CRM系统所使用的数据库被人无故做了分离以及重新附加的操作。这就意味着此数据库文件存在被复制的可能。这无疑是一个晴天霹雳的坏消息。作为国内比较大的汽车销售公司来说，这些数据一旦失窃或是泄露出来将对企业来说将带来不可估量的损失。第一时间报告上级领导进行监控及审核排查；另一方面要求信息技术部门立即着手加强数据安全管理，还好本单位使用的是SQL Server数据库。从SQL Server2005开始，中有一个令人激动的新特性，内置数据加密的功能。实现了完整的加密基础结构，不需要再借此其他工具就可以实现完整的加密，解密操作。利用好此功能，即使存储设备失窃也不用担心数据泄露的问题。

首先看一下数据库加密体系结构，整个加密体系看起来挺复杂的，但其实很简单，这个加密体系分为多个加密级别，如下图所示：

 

第一个级别：是windows级别。主要使用的是Windows的DPAPI（数据保护API）使用的是用户的凭据，也就是使用用户的密码来对需要保护的对象进行加密的一种方法。在这里主要是使用WINDOWS级别的用户凭据对第二个级别也就是SQL SERVER服务器级别进行加密。

第二个级别：SQL SERVER的服务器实例级别。这一个层次有一个服务主密钥，但是这个服务主密钥必须使用DPAPI（也就是第一个级别的用户凭据）进行加密。而这个服务主密钥又用于对第三个级别：数据库主密钥进行加密。也就是说上层必须为下层服务。

第三个级别：主数据库级别。存在一个数据库主密钥，它可以加密数据库中的其他对象（如非对称，证书进行保护，但不保护对称密钥），对称密钥不使用数据库主密钥加密，我们从图中可以看出来，对称密钥使用证书，非对称密钥或是指定的密码加密，同时又被第二级别服务主密钥所保护。

第四个级别：用户数据库级别。当数据库中有了数据库主密钥后就可以使用数据库主密钥来保护数据库中的证书和非对称密钥，再使用对称密钥来保护其他对称密钥和数据，当然也可以直接使用证书和非对称密钥来保护数据。  

下面咱们来逐个看一下各种密钥：

服务主密钥：在安装SQL SERVER时自动生成的一个密钥，是128BIT的3DES密钥。 作用：直接或者是间接地保护体系中的其他密钥。使用SQL Server的配置管理器更改服务帐户，能够自动完成加密和解密的过程。服务主密钥是安装时自动生成的，所以我们不能创建，但是我们可备份和还原服务主密钥。

备份：Backup service master key to  file=’c:\bak\smk.bak’ encryption by password=’DUfei2008’

备份的时候必须使用一个强密码，因为如果要是没有密码保护的话，那么其他人很容易进行还原，得到你的服务主密钥。

还原：必须给定备份时的密码：Restore service master  key from file=’c:\bak\smk.bak’ decryption by password=’DUfei2008’  

下面咱们来看一下数据库主密钥：

注意：  

1  数据库主密钥不会自己生成，必须需要管理员手动创建

2  由密码保护和服务主密钥同时保护
      3  是128bit的3DES密钥。
     创建语法：Create master key encryption by   password=’DUfei2008’  --由指定的密码和服务主密钥保护。

可以使用密码来解开，如分离后复制到另一个服务器上，因为使用不同的服务帐户，那么服务主密钥不同，所以就不能解密我使用服务主密钥加密的数据库主密钥，
那么这个时候可以使用加密时所提供的密码来解开数据库主密钥。

我们在创建数据库主密钥的时候，会自动使用服务主密钥进行保护，使用时会自动使用服务主密钥解开数据库主密钥，当然我们也可以删除服务主密钥对数据库主密钥的保护，那么在每一次打开的时候必须手动打开数据库主密钥，因为没有了服务主密钥那么就必须指定在加密时所指定的口令。

打开数据库主密钥语法：Open master key decryption by password=’DUfei2008’

使用完毕后关闭数据库主密钥：Close master key  

也可以修改数据库主密钥：重新生成数据库主密钥，这是很耗资源的一个工作，因为旧的数据库主密钥会解密现有数据，然后使用新的数据库主密钥进行加密。管理员可以根据安全性和适合场合来决定是使用服务主密钥还是删除服务主密钥。管理员还可以通过目录视图Sys.symmetric_keys、Sys.databases 查看数据库主密钥状态。  

备份数据库主密钥：Backup master key to file=’c:\bak\dmk.bak’ encryption by password=’DUfei2008’  

还原数据库主密钥：Restore master key from file=’c:\bak\dmk.bak’ decryption by password=’旧密码’ encryption by password=’新密码’  

以上对数据库加密体系进行了一个简单的介绍：下面咱们来看一下使用数据库加密保护数据。要保护数据，必须有一个可以保护数据的密钥，这个密钥有三种，对称密钥，非对称密钥和证书（公钥加密，私钥解密），有了密钥才可能去对数据进行加密，所以我们还得先来看一下如何创建相应的密钥，也就是如何创建证书，创建对称密钥，创建非对称密钥，然后再利用这些密钥对数据进行加密。  

咱们先看一下创建证书，在此我使用的是自签名的证书（也可以使用现有的CA进行证书的颁发）。

创建自签名证书语法：

Create certificate证书名 encryption by password=’密码’   with subject=’主题’，Start_date=’起始日期’ expiry_data=’到期日期’  

说明：其中这个口令用于保护咱们的私钥；主题，就是一个描述信息（无所谓）有效期在这里不是很严格，需要结合程序或者存储过程来进行检查。相关更改证书的操作：更改用于加密私钥的密码或者是删除私钥。第一种情况适合于口令泄露的情况，不安全；第二种情况适合于只加密不解密的情况，很常见如一些注册用户密码，不希望管理看到。看不到原文，但是用户一样可以使用。  

更改私钥的密码语法: alter certificate cert1 with private key  (decryption by password='DUfei2008',encryption by password='DUfei2008')

备份证书（导出证书）:Backup certificate 证书名to file=’c:\bak\mycert.cer’ with private key (decryption by  password=’旧口令’,file=’c:\bak\mycertpvt’,encryption   by password=’私钥的口令’)

还原（导入）: 还原证书在此不用restore而是create导入,create certificate cert1 from  file='c:\bak\mycert.cer' with  private key (file='c:\bak\mypvt'  ,decryption by password='DUfei2008',encryption by password='DUfei2008' )

删除证书的私钥：  Alter certificate证书名remove private key
      增加私钥: alter certificate cert1 with private key (file='c:\bak\mypvt', decryption by  password='DUfei2008',encryption by password='DUfei2008')  

另外还要用到一些与证书相关的函数：  

Encryptbycert（证书ID，加密文本）用于加密；Decryptbycert ( 证书ID，密文，加密口令)用于解密；Cert_id(证书名)得到证书ID，因为加密，解密使用的都是ID。  

下面咱们就看一下具体的实例：

咱们就以dufei数据库为例，先看一下服务主密钥。咱们前面已经介绍了，它不是咱们手动创建出来的，在安装SQL SERVER时就已经存在了。我们做的只能是备份和还原。

备份: backup service master key to  file='c:\bak\smk.bak' encryption by password='DUfei2008'

还原：restore service master key from  file='c:\bak\smk.bak' decryption by password='DUfei2008'

生成新的服务主密钥， 适应于更改服务帐户的情况，一般推荐使用配置管理器更改服务帐户：alter service master key regenerate
      ---------数据库主密钥---

1)为当前dufei数据库创建数据库主密钥

use dufei

create   master key encryption by password='DUfei2008'

2）查看数据库加密状态

select * from sys.databases where  name='dufei'

3）  查看数据库主密钥的信息

select * from sys.symmetric_keys    

4)备份数据库主密钥

backup master key to file='c:\bak\dmk.bak'  encryption by password='DUfei2008'

默认情况下，创建数据库主密钥会被服务主密钥所保护的，但有的时候我们不希望数据库主密钥使用服务主密钥保护，那么就可以删除服务主密钥对数据库主密钥的保护。我们先不删除，我们来利用数据库主密钥创建非对称密钥，可以直接创建。

Create asymmetric key asy_key1 with  algorithm=RSA_1024 --成功

语法：Alter master key drop encryption by service  master key

再次查看数据库的加密状态：Select * from sys.databases where name=’dufei’ 

--主要是看is_master_key_encrypted_by_server  已经是0了。说明不被服务主密钥保护了。

此时我们再创建一个非对称密钥，则不成功，Create asymmetric key asy_key2 with  algorithm=RSA_1024 --失败了，因为这个时候数据库主密钥不会自动打开了，必须打开数据库主密钥再创建。
       Open master key decryption by password=’DUfei2008’

Select * from sys.openkeys  显示打开的数据库主密钥，然后再创建非对称密钥则可以成功，如下：

Create asymmetric key asy_key2 with   algorithm=RSA_1024 ---则显示成功。用户也可以通过图形界面查看一下，非对称密钥中已经出现了我们所创建的密钥。这个实验是告诉大家，如果你删除了服务主密钥对数据库主密钥的保护，那么每次必须手动打开数据库主密钥，挺麻烦的，所以我们还可以恢复服务主密钥对数据库主密钥的保护。

1）   恢复服务主密钥对数据库主密钥的保护

Alter master key add encryption by service  master key

2）   关闭数据库主密钥

Close master key  

下面咱们来看看使用证书对数据进行加密

-----------------证书对数据加密---------------

1)生成自签名证书
      --使用指定口令保护私钥

create certificate cert1 encryption by  password='DUfei2008' with subject='test', start_date='01/01/2009',expiry_date='01/01/2010'

--使用数据库主密钥保护私钥

create certificate cert2 with subject='test',start_date='01/01/2009',expiry_date='01/01/2010'  

2) 查看证书信息

select * from sys.certificates  

3)修改私钥的口令
      alter certificate cert1 with private key  (decryption by password='DUfei2008',encryption by password='DUfei2008')  

4)备份证书

 backup certificate cert1 to  file='c:\bak\mycert.cer' with private key  (decryption by  password='DUfei2008',file='c:\bak\mypvt',encryption by password='DUfei2008')
      5)删除私钥

alter certificate cert1 remove private key 
      6)增加私钥
      alter certificate cert1 with private key (file='c:\bak\mypvt', decryption by password='DUfei2008',encryption  by password='DUfei2008')  

7)删除证书

drop certificate cert1 

8) 还原证书

create certificate cert1 from  file='c:\bak\mycert.cer' with  private key (file='c:\bak\mypvt'  ,decryption by password='DUfei2008',encryption by password='DUfei2008') 
      接下来可以使用下面的小脚本来体验一下加密的结果。
      declare @atext varchar(100),@acipher  varbinary(max)set @atext='dufei hao!'
      set  @acipher=encryptbycert(cert_id('cert1'),@atext)
      select @acipher
      select  cast(decryptbycert(cert_id('cert1'),@acipher,N'DUfei2008')as varchar(200) )

以上介绍的是SQL Server中实现数据加密过程，至于非对称加密、对称加密、混合加密的操作过程基本相同，甚至在操作过程还可以引入验证因子防止绕过数据加密的攻击等，使用好数据加密功能，就不用再担心数据丢失问题了，安全无小事，可不能大意呀！