SQL Server数据库加密

参考文献：

细说SQL Server中的加密

Transparent Data Encryption (TDE)

Database Encryption Key (DEK) management

SQL Server中的加密简介

　　在SQL Server2000和以前的版本，是不支持加密的。所有的加密操作都需要在程序中完成。这导致一个问题，数据库中加密的数据仅仅是对某一特定程序有意义，而另外的程序如果没有对应的解密算法，则数据变得毫无意义。举个例子来说，我以前写过一个web应用程序(参考博客：自定义SqlMembershipProvider方法)，想让用户信息中的密码字段用密文保存，我是在程序中将用户的明文密码加密以后保存到数据库中的。

　　到了SQL Server2005，引入了列级加密。使得加密可以对特定列执行，这个过程涉及4对加密和解密的内置函数。

　　SQL Server 2008时代，则引入的了透明数据加密（TDE），所谓的透明数据加密，就是加密在数据库中进行，但从程序的角度来看就好像没有加密一样，和列级加密不同的是，TDE加密的级别是整个数据库。使用TDE加密的数据库文件或备份在另一个没有证书的实例上是不能附加或恢复的。

对称加密与非对称加密

　　通常来说，加密可以分为两大类，对称(Symmetric)加密和非对称(Asymmetric)加密。

　　对称加密是那些加密和解密使用同一个**的加密算法，就是加***=解***。对称加密通常来说会比较羸弱，因为使用数据时不仅仅需要传输数据本身，还是要通过某种方式传输**，这很有可能使得**在传输的过程中被窃取。

　　非对称加密是那些加密和解密使用不同**的加密算法，就是加***!=解***。用于加密的**称之为公钥，用于解密的**称之为私钥。因此安全性相比对称加密来说会大大提高。当然有一长必有一短，非对称加密的方式通常算法会相比对称**来说复杂许多，因此会带来性能上的损失。

　　因此，一种折中的办法是使用对称**来加密数据，而使用非对称**来加密对称**。这样既可以利用对称**的高性能，还可以利用非对称**的可靠性。

证书与**的创建

每个数据库有且只有一个数据库主**(master key)，这是一个数据库级别的**。可以用于为创建数据库级别的证书或非对称**提供加密。通过T-SQL语句创建，如代码1所示。

View Code

如果要查看数据库是否有master key，可以通过下面的T-SQL语句查询

View Code

我的查询结果如下图所示：

从上图中我们可以看到master和TESTDB4这两个数据库含有master key，而且数据库没有。所以说master并不是只在master数据库下面的，如果我们要在TESTDB4中创建证书与**，那么就必须为TESTDB4这个数据库创建master key。

下面给出创建证书、对称**、非对称**的T-SQL代码

View Code

创建完以后我们可以在SSMS中找到我们创建的证书与**，如下图所示：

上面创建的证书、对称**、非对称**都是由固定的对称**[email protected]加密的。其实创建的证书、对称**、非对称**的时候，也可以使用证书、对称**、非对称**加密，T-SQL代码如下：

View Code

创建完以后我们在Symmetric Key中可以看到我们查询的结果：

 列级加密

　　在列级加密中我们将使用AdventureWorks2008R2这个样例数据库，在Sales.CreditCard这张表中我们可以看到CardNumber列是明文的，如下图所示：

在实际生产环境中，如果用户的信用卡号是明文的是非常不安全的， 我们在这项想将这个CardNumber使用列级加密。需要注意的是，进行加密或者解密的列必须是Varbinary类型。

首先我们创建不带数据的CreditCard的表结构，并且CardNumber这个列是Varbinary类型，T-SQL代码如下：

View Code

在创建表结构以后，我们将数据导入到这张表中去：

View Code

查询CreditCard_encrypt这个表，我们可以发现CardNumber列是密文了，如下图所示：

但是我们可以通过对称**来解密CardNumber这个列

View Code

使用这条语句查询出来的结果就是CardNuber的明文了。 注意这里需要OPEN SYMMETRIC KEY，如果不打开这个对称私钥的话查询结果是null。如果之前打开过这个私钥，那么此处可以不用再次打开。

透明数据加密

　　在SQL Server 2008中引入了透明数据加密(Transparent Data Encryption ，以下简称TDE)，之所以叫透明数据加密，是因为这种加密在使用数据库的程序或用户看来，就好像没有加密一样。TDE加密是数据库级别的。数据的加密和解密是以页为单位，由数据引擎执行的。在写入时进行加密，在读出时进行解密。客户端程序完全不用做任何操作。（Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.）

　　TDE的主要作用是防止数据库备份或数据文件被偷了以后，偷数据库备份或文件的人在没有数据加***的情况下是无法恢复或附加数据库的。我将一个encrypted database拷贝到另外的一台服务器上，视图attach这个database，但是报错如下：

上述错误表明这台服务器没有相应的证书。这从另一个角度也给我们做了提醒，如果是一个encrypted database的话， 我不单要备份数据库，还要对证书做备份，如果我们自身证书丢失的话，也会开不开数据库。

　　TDE使用数据加***（DEK）进行加密。DEK是存在Master数据库中由服务主**保护，由的保护层级如下图所示。

使用TDE的四个步骤为：

1. 创建一个master key(Create a master key)

   • 

     USE master;
     GO
     
     --在master数据库中创建一个master key
     CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[email protected]';
     go

     

2. 创建或者获取一个由master key保护的证书(Create or obtain a certificate protected by the master key)

   1. --使用masterkey创建证书MyServerCert
      CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
      go

3. 使用证书创建一个database**(Create a database encryption key and protect it by the certificate)

   1. 

      USE TESTDB2;
      GO
      
      --创建数据库加密key，使用MyServerCert这个证书加密
      CREATE DATABASE ENCRYPTION KEY
      WITH ALGORITHM = AES_128
      ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
      GO

      

      但是在创建完DATABASE ENCRYPTION KEY(DEK)以后，会报如下警告：

      Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate.If the certificate ever becomes unavailable or if you must restore or attach the database on another server,you must have backups of both the certificate and the private key or you will not be able to open the database.

      这是因为我们使用了证书来创建DEK，并且加密数据库，因此我们建议为证书做一个备份，否则如果证书被破坏以后，我们自身都无法打开数据库。证书备份的方法可以参考：backup certificate，我的T-SQL所示：

      BACKUP CERTIFICATE MyServerCert TO FILE = 'd:\storedcerts\MyServerCert'; --cert的保存地址 

      这里需要注意的是我们的MyServerCert没有由private key加密，而只是有master key加密。如果使用了private key的话还要为private key进行备份。我们如果对MyServerCert进行备份private key操作会报错如下：

      No decryption password should be provided because the private key of this certificate is encrypted by a master key.

4. 将数据库设置为TDE(Set the database to use encryption)

　　　　　　　最后我们需要做的就是讲数据库设置为加密，T-SQL语句如下所示：

ALTER DATABASE TESTDB2 SET ENCRYPTION ON;

　　　　　　　此时我们也可以右键TESTDB2->tasks->manage database encryption中查看加密设置，如下图所示：

 我们也可以通过T-SQL语句来查看有哪些数据库进行了TDE加密，T-SQL语句如下：

View Code

查询结果显示tempdb也被透明加密了，原因我们可以在msdn上找到，我把原文摘出来：

Transparent Data Encryption and the tempdb System Database

master key和certificate的备份与还原

View Code

 本文转自xwdreamer博客园博客，原文链接：http://www.cnblogs.com/xwdreamer/archive/2012/08/21/2648561.html，如需转载请自行联系原作者