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所示。

--创建证书，对称与非对称密钥需要master key
--创建master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='P@ssw0rd';

--删除master key
drop MASTER KEY ; 

 

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

--在SSMS中没有查看master key的地方，但是可以在sys.databases中查看到数据库是否有master key
select name,is_master_key_encrypted_by_server from sys.databases

 

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

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

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

use TESTDB3;

--创建证书
CREATE CERTIFICATE CertTest 
with SUBJECT = 'Test Certificate'
GO

--创建非对称密钥
CREATE ASYMMETRIC KEY TestAsymmetric
    WITH ALGORITHM = RSA_2048 
    ENCRYPTION BY PASSWORD = 'P@ssw0rd'; 
GO

--创建对称密钥
CREATE SYMMETRIC KEY TestSymmetric
    WITH ALGORITHM = AES_256
    ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO

 

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

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

--由证书加密对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CertTest;
GO

--由对称密钥加密对称密钥
OPEN SYMMETRIC KEY TestSymmetric
    DECRYPTION BY PASSWORD='P@ssw0rd'
--必须先open 了TestSymmetric以后才可以用TestSymmetric来加密
CREATE SYMMETRIC KEY SymmetricBySy
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SYMMETRIC KEY TestSymmetric;
GO

--由非对称密钥加密对称密钥
CREATE SYMMETRIC KEY SymmetricByAsy
    WITH ALGORITHM = AES_256
    ENCRYPTION BY ASYMMETRIC KEY TestASymmetric;
GO

 

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

 列级加密

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

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

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

--创建不带数据的表结构，有where 1<>1来控制
SELECT CreditCardID, 
CardType,
CardNumber_encrypt = CONVERT(varbinary(500), CardNumber), 
ExpMonth, 
ExpYear, 
ModifiedDate
INTO CreditCard_Encrypt 
FROM AdventureWorks2008R2.Sales.CreditCard
WHERE 1<>1

 

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

--打开之前创建的由证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert DECRYPTION BY CERTIFICATE CertTest;

--利用这个密钥加密CardNumber这个数据列,插入新建的表中,使用了EncryptByKey这个函数
insert CreditCard_encrypt (
CardType,
CardNumber_encrypt, 
ExpMonth, 
ExpYear, 
ModifiedDate
) 
select top 10
CardType,
CardNumber_encrypt = EncryptByKey(KEY_GUID('SymmetricByCert'), CardNumber),
ExpMonth,
ExpYear, 
ModifiedDate
from AdventureWorks2008R2.Sales.CreditCard

 

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

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

--打开之前创建的由证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert DECRYPTION BY CERTIFICATE CertTest;
--查看CardNumber_encrypt
select convert(nvarchar(25), DecryptByKey(CardNumber_encrypt)) from CreditCard_Encrypt;

 

使用这条语句查询出来的结果就是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 = 'P@ssw0rd';
   go

    

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

   --使用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. 但是在创建完DATABASE ENCRYPTION KEY(DEK)以后，会报如下警告：

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

       

      
      

      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语句如下：

/* The value 3 represents an encrypted state 
   on the database and transaction logs. */
SELECT DBName=DB_NAME(database_id),encryption_state 
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
GO

 

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

Transparent Data Encryption and the tempdb System Database

The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server. For more information about the tempdb system database, see tempdb Database.

master key和certificate的备份与还原

--备份master key
BACKUP MASTER KEY TO FILE = 'd:\storedkeys\masterkey' ENCRYPTION BY PASSWORD = 'P@ssw0rd'
go

--备份证书
BACKUP CERTIFICATE MyServerCert TO FILE = 'd:\storedcerts\MyServerCert'; --cert的保存地址
go

--还原master key
use master
RESTORE MASTER KEY 
    FROM FILE = 'd:\storedkeys\masterkey' 
    DECRYPTION BY PASSWORD = 'P@ssw0rd' 
    ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO
--因为我这里还留有原来的证书，所以会提示如下信息：The old and new master keys are identical. No data re-encryption is required.

--还原证书
CREATE CERTIFICATE MyServerCert2
    FROM FILE = 'd:\storedcerts\MyServerCert' 
GO 
--因为证书已经存在，所以提示如下信息：--A certificate with name 'MyServerCert2' already exists or this certificate already has been added to the database.
--需要注意的是证书并不是按照证书名来区分的。我原来的证书名叫做MyServerCert，此处创建的证书名为MyServerCert2，但是是来自MyServerCert的一个备份，还是报错。