SQL Server 2008的透明資料加密

對一個資料庫管理者來說，當要保護你所支援的資料庫時，安全是要考慮的最重要方面之一。我們使用多種機制和技術來保護我們的資料和資料庫，例如防火牆、認證和資料加密。不過盡管我們為我們的環境設定了安全，但是關于資料庫安全還總是有問題出現。盡管我們在保護我們的資料庫，但是如果有人竊取mdf 檔案或備份檔案那麼會怎麼樣呢?但是在SQL Server 2008之前沒有什麼方法來使用第三方解決方案控制這種場景也沒有什麼本地方法來處理這個問題。SQL Server 2008推出了一個新的特性來保護資料庫，它叫做透明資料加密(Transparent Data Encryption)——TDE，它對整個資料庫提供了保護。這篇文章的内容包括：

　　什麼是透明資料加密?

　　TDE的執行。

　　我的資料庫現在是安全的嗎?

　　在激活TDE之前需要考慮什麼?

　　當激活TDE之後會影響什麼?

　　Microsoft SQL Server 2008推出了另一個級别的加密——透明資料加密。TDE是全資料庫級别的加密，它不局限于字段和記錄，而是保護資料檔案和日志檔案的。在一個資料庫上的TDE執行對于連接配接到所選資料庫的應用程式來說是非常簡單而透明的。它不需要對現有應用程式做任何改變。這個保護是應用于資料檔案和日志檔案以及備份檔案的。一旦在一個資料庫上激活了TDE，備份恢複到另一個SQL Server執行個體或附加資料檔案到另一個SQL Server執行個體上去将是不允許的，除非用來保護資料庫加密密鑰(DEK)的證書是可用的。

　　TDE的加密特性是應用于頁面級别的。一旦激活了，頁面就會在它們寫到磁盤之前加密，在讀取到記憶體之前解密。有一點一定要記住，那就是SQL Server和用戶端應用程式之間的通信管道沒有通過TDE來保護和加密。

　　下圖顯示了SQL Server怎樣使用TDE加密一個資料庫：

<a href="http://www.cnblogs.com/TLimages/picview/?/imagelist/2008/218/204dh01m9t76.JPG" target="_blank"></a>

　　透明資料加密使用一個資料加密密鑰(DEK)用于加密資料庫，它存儲在資料庫啟動記錄中。DEK由一個存儲在主資料庫中的證書來保護。可選的，DEK可以由一個放置在硬體安全子產品(HSM)中的非對稱密鑰以及外部密鑰管理(EKM)的支援來保護。證書的私鑰由對稱密鑰的資料庫主密鑰來加密，它通常由一個強密碼來保護。注意，盡管這個證書可以由一個密碼來保護，但是TDE要求這個證書由資料庫主密鑰來保護。資料庫主密鑰由服務主密鑰來保護，而服務主密鑰由資料保護API來保護。

TDE的執行

　　如同上面所提到的，TDE的執行相對簡單。下面是一個示例腳本，它使得在一個叫做TestDatabase的資料庫上激活了TDE。　

-- If the master key is not available, create it.

USE master;

GO

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%')

BEGIN

        CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd4545';

END

-- Create the certificate in the master database.

-- Since ENCRYPTION BY PASSWORD is not mentioned, the private key of the certificate

-- will be encrypted by database master key created above.

IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name LIKE '%DEKCertificate%')

        CREATE CERTIFICATE DEKCertificate WITH SUBJECT = 'DEK Certificate'

-- Create Database Encryption Key (DEK) in the user database

USE TestDatabase

IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys WHERE database_id = DB_ID('TestDatabase'))

BEGIN       

        CREATE DATABASE ENCRYPTION KEY

        WITH ALGORITHM = AES_128

        ENCRYPTION BY SERVER CERTIFICATE DEKCertificate

-- Check whether the key is created

SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys

-- This should return one row (or more if DEKs have been generated in other databases)

-- with the encryption_state of 1 (1 = unencrypted).

-- Set the DEK on in the TestDatabase.

ALTER DATABASE TestDatabase

SET ENCRYPTION ON

-- Check whether the encryption_state is changed to 3. It should be.

　　前兩個步驟顯示了怎樣建立主資料庫中的資料庫主密鑰和證書。注意，ENCRYPTION BY PASSWORD 不是由CREATE CERTIFICATE 來指定，是以自簽名的證書的私鑰将由資料庫主密鑰來保護。下一步顯示了在TestDatabase中建立DEK的方法。執行這個代碼。它添加了DEK到TestDatabase。如果這個證書的私鑰由一個密碼保護，那麼你将獲得如下所示的錯誤資訊：

　　Msg 33101, Level 16, State 1, Line 4

　　不能使用證書“DEKCertificateTest”，因為它的私鑰沒有顯示出來或者它不是由資料庫主密鑰來保護的。SQL Server 需要自動通路這個操作所使用證書的私鑰的能力。

　　sys.dm_database_encryption_keys 使你可以看到DEK被添加到伺服器上。字段encryption_state 表示DEK是處于下面的哪個狀态：沒有加密、加密中、已加密、密鑰改變中、和解密中，這些各自對應1、2、3、4、和5這幾個數值。當你在設定ENCRYPTION之前運作DMV時，這個狀态将顯示為1，如果設定了，這個狀态将顯示為3。完成了。現在TestDatabase 已經是完全安全的了。

　　盡管我們成功地使得在我們的資料庫上激活了TDE，但是我們還需要確定它在所有級别都是安全的。我們将在這方面做兩個測試。首先，我們将備份這個資料庫并嘗試恢複這個備份到另一個SQL Server 2008執行個體上去。這個恢複操作一定會失敗的，除非這個證書用于保護DEK的私鑰是可用于主資料庫的。第二，我們将嘗試在另一個執行個體中附加TestDatabase的mdf和ldf檔案。它應該也不能起作用。這是用于測試的代碼：

-- First step is backing up the TestDatabase.

BACKUP DATABASE [TestDatabase] 

TO DISK = N'E:\TestDatabaseFull.bak' 

WITH NOFORMAT, NOINIT, NAME = N'TestDatabase-Full Database Backup'

-- Now connect with another SQL Server 2008 instance.

-- Try to restore the backup we have taken, in the new instance.

-- This statement should be failed.

RESTORE DATABASE [TestDatabase] 

FROM DISK = N'E:\TestDatabaseFull.bak' 

WITH FILE = 1, 

MOVE N'TestDatabase' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDatabase.mdf', 

MOVE N'TestDatabase_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDatabase_log.ldf', 

NOUNLOAD, STATS = 10

　　第一個步驟備份了這個資料庫。第二部分需要運作在一個不同的SQL Server 2008執行個體上。當你嘗試在一個不同的SQL Server 2008執行個體中恢複這個備份時，你将得到一個類似于下面所示的錯誤資訊：　　

10 percent processed. 

20 percent processed. 

31 percent processed. 

41 percent processed. 

52 percent processed. 

62 percent processed. 

73 percent processed. 

83 percent processed. 

94 percent processed. 

100 percent processed. 

Msg 33111, Level 16, State 3, Line 1 

Cannot find server certificate with thumbprint '0x8AD8C0A89476752FCC3D7A7005A2DCF546C38C58'. 

Msg 3013, Level 16, State 1, Line 1 

RESTORE DATABASE is terminating abnormally.

　　當你嘗試将這個資料庫附加到另一個執行個體中去時你将面對相同的問題。

USE [master]

CREATE DATABASE [TestDatabase] ON 

( FILENAME = N'D:\Test\TestDatabase.mdf'),

( FILENAME = N'D:\Test\TestDatabase_log.ldf')

FOR ATTACH

　　上面代碼的結果是：

　　Msg 33111, Level 16, State 3, Line 1

　　Cannot find server certificate with thumbprint '0x8AD8C0A89476752FCC3D7A7005A2DCF546C38C58'.

　　它起作用了。我們的資料庫是安全的了。恢複或附加TestDatabase 到另一個執行個體中去的唯一方法是在第二個執行個體中添加相同的證書。學習下面的代碼：

-- backup the certificate

-- The private key will be encrypted by the password specified

BACKUP CERTIFICATE DEKCertificate TO FILE = 'E:\DEKCertificate.cert'

         WITH PRIVATE KEY

         (

                  FILE = 'E:\Instance1PrivateKey.key',

                  ENCRYPTION BY PASSWORD = 'Pa$$w0rd5454'

         )

-- create the certificate in the second instance by using the backup

-- Private key will be loaded from the saved file.

CREATE CERTIFICATE DEKCertificate

         FROM FILE = 'E:\DEKCertificate.cert'

         WITH PRIVATE KEY (

                  FILE = 'E:\Instance1PrivateKey.key'

                  , DECRYPTION BY PASSWORD = 'Pa$$w0rd5454'

                  )

　　這個代碼的第一部分将這個證書備份到了一個檔案中。它還備份了這個證書的私鑰。代碼中指定的密碼是用于加密私鑰的。代碼的第二部分需要運作在第二個SQL Server 2008執行個體上。它使用備份證書建立了一個證書。當這個代碼運作後，你将可以恢複或附加TestDatabase資料庫到新的執行個體中去。

　　在你在資料庫上激活TDE之前隻有很少的事情需要注意，那就是：

　　TDE是否影響所執行的災難複原計劃?

　　設想一個簡單的災難複原計劃，備份和恢複。你可能開發了這個計劃而且它執行沒有任何問題。你激活了TDE，仍然沒有問題，時間表作業備份了你的資料庫。假設這個伺服器開始産生嚴重錯誤導緻你需要重新安裝作業系統和SQL Server。你可能不做它想就輕松地重新安裝，因為你有資料庫備份。當資料庫恢複時問題出現了。你可能具有不是加密格式的資料庫完全備份，你可能有一些在激活TDE之後進行的事務型備份，是以它們是加密的。你沒有用于TDE的證書備份。這導緻你處于一個不可預料的境地。因為你沒有所用證書的備份，是以你将不能恢複事務型備份。

　　想想在激活TDE之前災難複原計劃的開發。如果你有計劃，那麼確定這個計劃在激活TDE之後仍然可用。這不隻用于備份和恢複政策，它還用于其它計劃，例如日志傳送和資料庫鏡像。

　　在你的資料庫中有隻讀檔案組嗎?

　　如果資料庫有隻讀檔案組，那麼TDE将會失敗。一旦TDE激活了，那麼encryption_state的數值将永遠不可能是3(加密的)而是2(加密中)。SQL Server在運作TDE代碼時不會抛出任何異常。激活TDE之後，如果你打開資料庫的屬性視窗，你将會發現屬性Encryption Enabled的值被設為了true。使用下面的代碼進行測試：

-- create a new database for testing TDE on readonly file groups

USE master

CREATE DATABASE [TestDatabase2] ON PRIMARY 

( NAME = N'TestDatabase2_Primary', FILENAME = N'E:\TestDatabase2_Primary.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [FG1_Default] 

( NAME = N'TestDatabase2_FG1', FILENAME = N'E:\TestDatabase2_FG1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), 

FILEGROUP [FG2_ReadOnly] 

( NAME = N'TestDatabase2_FG2', FILENAME = N'E:\TestDatabase2_FG2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON 

( NAME = N'TestDatabase2_log', FILENAME = N'E:\TestDatabase2_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

-- Set the FG1_Default file group as the default one.

USE [TestDatabase2]

IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'FG1_Default') 

ALTER DATABASE [TestDatabase2] 

MODIFY FILEGROUP [FG1_Default] DEFAULT

-- Add a table to the default file group

CREATE TABLE TestTable1 (Id int PRIMARY KEY, [Text] varchar(100))

INSERT INTO TestTable1 VALUES (1, 'hello')

-- Add a table to the FG2_ReadOnly file group

CREATE TABLE TestTable2 (Id int PRIMARY KEY, [Text] varchar(100))

ON [FG2_ReadOnly]

INSERT INTO TestTable2 VALUES (1, 'hello')

-- Set the file group FG2_ReadOnly file group as READONLY

IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'FG2_ReadOnly') 

MODIFY FILEGROUP [FG2_ReadOnly] READONLY

USE TestDatabase2

IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys WHERE database_id = DB_ID('TestDatabase2'))

-- Enable TDE on the database

ALTER DATABASE TestDatabase2

　　首先這個代碼建立了一個具有三個資料檔案的資料庫，這三個檔案叫做TestDatabase2_Primary、TestDatabase2_FG1和TestDatabase2_FG2。檔案組FG1_Default 設定為預設檔案組，在其中建立了TestTable1。在FG2_ReadOnly檔案組中建立了TestTable2。然後FG1_ReadOnly檔案組被辨別為READONLY。

　　最後，在TestDatabase2 中建立了DEK，Encryption屬性設定為true。所有的語句都成功執行。如果你查詢sys.dm_database_encryption_keys，你将看到TestDatabase2的encryption_state是2，這表示加密結束了但沒有完成。

是否使用了FileStream資料類型?

　　使用了filestream類型的資料庫可以使用TDE來進行加密，但是檔案流資料不會被加密。

　　在一個資料庫上激活TDE會影響以下事情：

　　事務日志

　　一旦TDE激活了，SQL Server 通過将文本資料清理出去進而確定日志檔案不包含文本資料。SQL Server 從具有加密格式的新VLF開始。

　　TEMPDB系統資料庫

　　當你在任何資料庫上激活了TDE之後這将會自動加密。這會導緻使用tempdb資料庫的非加密資料庫性能下降。

　　日志傳送和資料庫鏡像

　　如果你在一個傳送日志到另一個資料庫的資料庫(意味着激活了日志傳送的資料庫)上激活了TDE，那麼日志傳送操作将會在輔助資料庫上失敗，除非在輔助伺服器上證書可用。

　　壓縮備份

　　下面是在一個激活了TDE的資料庫上進行壓縮備份的測試，看起來在激活TDE的資料庫上壓縮并不怎麼高效：　

-- create a new database for testing compressed backup on TDE enabled database

CREATE DATABASE [TestDatabase3] ON PRIMARY 

( NAME = N'TestDatabase3', FILENAME = N'E:\TestDatabase.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),

( NAME = N'TestDatabase3_log', FILENAME = N'E:\TestDatabase3_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

-- Create a table and insert some records

USE TestDatabase3

CREATE TABLE TestTable (Id int primary key, [Value] char(8000))

INSERT INTO TestTable VALUES (1, 'value1')

INSERT INTO TestTable VALUES (2, 'value1')

INSERT INTO TestTable VALUES (3, 'value1')

INSERT INTO TestTable VALUES (4, 'value1')

INSERT INTO TestTable VALUES (5, 'value1')

INSERT INTO TestTable VALUES (6, 'value1')

-- Backup the TestDatabase3 without compressing it

BACKUP DATABASE [TestDatabase3] 

TO DISK = N'E:\TestDatabase3Full.bak' 

WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup'

-- Backup the TestDatabase3 with  compression

TO DISK = N'E:\TestDatabase3Full_Compressed.bak' 

WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup (Compressed)', COMPRESSION

-- Now enable TDE on this

-- Use the code we used with first example

-- Backup the TestDatabase3 again (after TDE enabled) without compressing it

TO DISK = N'E:\TestDatabase3Full_Encrypted.bak' 

WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup (encrypted)'

-- Backup the TestDatabase3 with compression

TO DISK = N'E:\TestDatabase3Full_Encrypted_Compressed.bak' 

WITH NOFORMAT, NOINIT, NAME = N'TestDatabase3-Full Database Backup (Encrypted and compressed', COMPRESSION

　　這個代碼建立一個資料庫并插入一些記錄到資料表中。然後這個資料庫被備份兩次，一次沒有壓縮另一次有壓縮。然後你需要在這個資料庫上激活TDE并執行其它的與激活TDE之前備份所使用的相同代碼。備份檔案規模是：

　　在激活TDE之前完全備份1,365 KB

　　在激活TDE之前有壓縮的完全備份124KB

　　激活TDE之後的完全備份1,365 KB

　　激活TDE之後有壓縮的完全備份 1,278 KB

　　你可以看到它們的不同。結果證明激活了TDE的資料庫的壓縮備份檔案不那麼高效。

本文轉自 你聽海是不是在笑 部落格園部落格，原文連結：http://www.cnblogs.com/nuaalfm/archive/2010/04/15/1712560.html  ，如需轉載請自行聯系原作者