第九篇 SQL Server安全透明数据加密

示例代码会执行下面四步来启用数据库上的TDE：
1、master库创建一个master key
2、master库创建一个用master key保护的证书
3、创建数据库加密密钥，用证书保护
4、启用数据库TDE
代码9.1首先用一个强密码创建database master key，然后创建AdventureWorks2012TDECert证书。作为预防，代码备份证书。你应该把它保存到一个安全的地方，以防迁移数据库时需要：

-- Create a certificate in master to use with TDE
USE master;
GO
-- TDE hooks into encryption key hierarchy in SQL Server
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘!drJP9QXC&Vi%cs‘;
GO
-- Create the certificate used to protect the database encryption key
CREATE CERTIFICATE AdventureWorks2012TDECert WITH SUBJECT = ‘Certificate to implement TDE on AdventureWorks2012Copy‘;
GO

-- Backup the certificate
-- Either create the D:\SQL2012 folder or change it in the code below
BACKUP CERTIFICATE AdventureWorks2012TDECert TO FILE = ‘D:\SQL2012\AdventureWorks2012TDECert‘
    WITH PRIVATE KEY ( FILE = ‘D:\SQL2012\AdventureWorks2012TDECertPrivateKey‘ , 
    ENCRYPTION BY PASSWORD = ‘RISiS9Ul%CByEk6‘ );
GO
-- Must backup private key as well

代码9.1 创建证书并备份证书
在深入配置TDE之前先运行代码9.2。语句会列出当前实例下加密的数据库，以及加密状态。在一个全新、原始的实例上，应该不会返回任何结果集。在应用TDE之后，你会看到结果有所改变：

SELECT DB_NAME(database_id) AS DatabaseName,
    key_algorithm AS [Algorithm],
    key_length AS KeyLength,
    CASE encryption_state
        WHEN 0 THEN ‘No database encryption key present, no encryption‘
        WHEN 1 THEN ‘Unencrypted‘
        WHEN 2 THEN ‘Encryption in progress‘
        WHEN 3 THEN ‘Encrypted‘
        WHEN 4 THEN ‘Key change in progress‘
        WHEN 5 THEN ‘Decryption in progress‘
    END AS EncryptionStateDesc,
    percent_complete AS PercentComplete
FROM sys.dm_database_encryption_keys;
GO

代码9.2 罗列SQL Server实例上加密的数据库
现在是时候启用AdventureWorks2012Copy的TDE。执行代码9.3，使用之前在master库创建的证书创建一个数据库加密密钥。如果你没有备份证书的话会收到一条警告。然后使用ALTER DATABASE带上SET ENCRYPTION ON子句启用TDE，根据数据库的大小和服务器的速度，它可能需要一段时间才能完全加密数据库，可能数小时或数天。

USE AdventureWorks2012Copy;
GO
-- Create the database encryption key for TDE. Analogous to database master key for data encryption.
CREATE DATABASE ENCRYPTION KEY 
    WITH ALGORITHM = TRIPLE_DES_3KEY
    ENCRYPTION BY SERVER CERTIFICATE AdventureWorks2012TDECert;
GO
-- Get a warning about backing up the key, if you haven‘t already
-- ...take the advice and back it up!

-- Now need to turn TDE on. 
ALTER DATABASE AdventureWorks2012Copy SET ENCRYPTION ON;
GO

代码9.3 创建数据库加密密钥并启用TDE
当加密完成后，重新执行代码9.2。你会看到类似图9.1的结果。注意图中显示两个数据库：AdventureWorks2012Copy数据库启用了TDE，tempdb库是自动应用加密。一旦初始化加密完成，PercentComplete列会显示为0(是的，列名有点误导人，它完成时并不是真实的完成百分比)。

图9.1 sys.dm_database_encryption_keys监视加密进度
注意图中AdventureWorks2012Copy是由TRIPLE_DES_3KEY算法加密的。这是因为在代码9.3中我们指定的加密算法。你可以选择SQL Server中支持的任何加密算法。同时注意tempdb库使用默认的AES加密算法。
使用代码9.4测试加密。只要在开启TDE之前有访问数据的必要权限，你就依然能够访问数据。应用程序访问数据也是一样。

SELECT TOP 500 * FROM Production.Product;

代码9.4 测试开启TDE后访问数据
如果你想关闭数据库的TDE，可以使用代码9.5

ALTER DATABASE AdventureWorks2012Copy
    SET ENCRYPTION OFF;

代码9.5 禁用数据库的TDE功能
测试TDE
测试安全特性以确保其按照你期望的方式工作通常是明智的。示例代码包含部分测试TDE步骤，包括假设你意外地删除TDE中用于保护数据库加密密钥的证书。代码9.6先备份AdventureWorks2012Copy数据库，接着删除AdventureWorks2012Copy数据库，然后删除AdventureWorks2012TDECert证书，从而模拟证书丢失。(这也模拟了当你试图附加加密数据库到不同的、且没有安装原始证书的SQL Server实例)

BACKUP DATABASE AdventureWorks2012Copy
    TO DISK = N‘D:\SQL2012\MSSQL11.SQL12\MSSQL\Backup\AdventureWorks2012Copy.bak‘
    WITH NOFORMAT, INIT, NAME = N‘AdventureWorks2012Copy Full Database Backup‘,
    SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;
GO
USE master
GO
DROP DATABASE AdventureWorks2012Copy;
GO
-- Oops! We lost the certificate and don‘t have a copy!
-- Or, going to restore the database to another server instance
DROP CERTIFICATE AdventureWorks2012TDECert; 
GO

代码9.6 备份数据库、删除数据库、删除证书
接下来，尝试用代码9.7还原数据库。你会看到如图9.2所示的错误信息。这是TDE的保护机制：无法还原或附加数据库到一个没有安装原始加密证书的SQL Server实例上。

RESTORE DATABASE AdventureWorks2012Copy
    FROM DISK = N‘D:\SQL2012\MSSQL11.SQL12\MSSQL\Backup\AdventureWorks2012Copy.bak‘
    WITH 
        FILE = 1, NOUNLOAD, REPLACE, STATS = 10;

代码9.7 尝试还原数据库

图9.2 在没有安装原始加密证书的实例还原TDE数据库的出错信息
但是如果你备份了证书，那么可以用代码9.8来从备份文件中还原证书，使用之前在备份证书时保护证书的密码，然后尝试还原数据库。这次数据库成功被还原了。

-- Restore the certificate
CREATE CERTIFICATE AdventureWorks2012TDECert
    FROM FILE = ‘D:\SQL2012\AdventureWorks2012TDECert‘
    WITH PRIVATE KEY ( FILE = ‘D:\SQL2012\AdventureWorks2012TDECertPrivateKey‘, 
    DECRYPTION BY PASSWORD = ‘RISiS9Ul%CByEk6‘);    
-- Now try to restore the database
RESTORE DATABASE AdventureWorks2012Copy
    FROM DISK = N‘D:\SQL2012\MSSQL11.SQL12\MSSQL\Backup\AdventureWorks2012Copy.bak‘
    WITH 
        FILE = 1, NOUNLOAD, REPLACE, STATS = 10;

代码9.8 从备份文件中还原证书，然后还原数据库
TDE与列级数据加密对比
在第八篇和第九篇，你应该对SQL Server提供的这两种加密形式有一个很好的理解。为总结主要不同点，在列级的数据加密：
->加密有更细的粒度，你可以在一个单独的表中加密单独的一列
->只有在使用的时候才解密数据，如果数据很少被使用，它就很少会被解密
->你需要更改表结构以适应加密的二进制数据，并将应用程序更改为包含加密和解密代码
->你不能很容易地搜索和排序加密数据，索引基本上是没有用的。有一些解决方法，但它们不是有效的，攻击者可以利用探索特性来获得一个立足点
在一般情况下，你会使用列级数据加密少量的数据以保护最敏感的数据，并节约服务器上处理周期。
这就引出了一个问题：SQL Server 2008及之后版本应该使用哪种形式的加密？关键是要了解你需要防护的威胁，这在执行任何安全功能时是至关重要的。
如果威胁是对数据和日志文件的盗用和滥用，使用透明数据加密。TDE会阻止别人附加数据库文件到另一个SQL Server实例获取数据。
但是如果威胁是在你的服务器上的数据，列级加密可能是更好的选择。正确地使用列级数据加密可以防止攻击者访问数据库服务器时去访问数据。
如果你不能在两者之间决定，那就有可能需要使用两种类型的加密来保护数据。幸运地是，它们一起工作很和谐，每一个保护自己独特的威胁。

-- *** Clean up ***
-- ****************
USE master;
GO
IF DB_ID(‘AdventureWorks2012Copy‘) IS NOT NULL DROP DATABASE AdventureWorks2012Copy;
GO
-- Can‘t turn off TDE in tempdb once it is on
DROP CERTIFICATE AdventureWorks2012TDECert;
GO
DROP MASTER KEY;
GO

总结
透明数据加密在文件级别实时加密和解密数据和日志文件。这种加密数据库中的所有记录数据，防止攻击者将数据库附加到另一个SQL Server实例获取数据。如果你需要抵御数据文件的盗窃和滥用，TDE提供强有力的数据保护，并且架构或编程不需要改变。Used properly, it can provide a strong layer of security for overall defense in depth.

第九篇 SQL Server安全透明数据加密

标签：