TDE 資料庫 加解密 安全

Transparent Data Encryption (TDE) 透明資料加密

蔡宗勳 Tsunghsun Tsai 2019/12/20 11:06:39
314

    資料庫安全防護機制常見之方式有:防火牆、權限控管、資料加密等等,除上述之外尚可利用TDE之保護機制,來對資料進行防護,以避免資料庫之「實體檔案」因任何原因被攜出時意外導致資料外流。

 

 

@摘要說明

1.使用者可自定義之部分為資料庫層級(DataBase Level),如第4點架構圖。

2.2008R2,Enterprise以上版本(AWS:2012以上;Azure:支援,細節請參文件)

3.加解密使用Certificate(Symmetric key)/EKM(Asymmetric key)

4.加密架構示意圖如下:

TDE

 

@測試案例

環境 OS

Windows Server 2008 Enterprise

Windows Server 2012 Enterprise

Windows Server 2016 Enterprise

Windows Server 2019 DataCenter

DB

SQL Server 2008 R2 Enterprise

SQL Server 2012 Enterprise

SQL Server 2014 Enterprise

SQL Server 2016 Enterprise

情境

1. 不同的 OS 與 DB 之間相互備份&還原 (基本上版本由低至高)

2. 以不同方式(關聯與加密)產出憑證與金鑰

 

(避免篇幅過長,僅列出一種案例組合供參)

/*建立 Database Master key*/
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey'
GO

/*建立測試用憑證*/
--ENCRYPTION : 若無指定,預設將以 Database Master key 進行加密
--EXPIRY_DATE : 若無給值,預設為一年
CREATE CERTIFICATE TestDbCert WITH 
    SUBJECT = 'TestDbCert',
    EXPIRY_DATE = '2019/12/31'
GO

/*建立測試DB*/
CREATE DATABASE TestDB;
GO

/*使用測試憑證建立資料庫加密金鑰*/
USE TestDB;
GO
CREATE DATABASE ENCRYPTION KEY WITH
    ALGORITHM = AES_256  
    ENCRYPTION BY SERVER CERTIFICATE TestDbCert;  
GO

/*啟用資料庫加密*/
ALTER DATABASE TestDB
SET ENCRYPTION ON
GO

/*檢視金鑰*/
SELECT * FROM [master].sys.symmetric_keys WHERE NAME='##MS_DatabaseMasterKey##'
/*檢視憑證*/
SELECT * FROM [master].sys.certificates WHERE NAME='TestDbCert'
/*檢視資料庫加密狀態*/
SELECT 
    DB_NAME(database_id) [dbname]
	,(CASE encryption_state
		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'
		WHEN 6 THEN 'Protection change in progress'
		WHEN 0 THEN 'No database encryption key present, no encryption'
		ELSE 'Undefine' END) encryption_state
  ,create_date
  ,encryptor_type
FROM sys.dm_database_encryption_keys
GO

/*備份資料庫*/
BACKUP DATABASE TestDB 
    TO DISK = N'D:\TDE\BK\DB\TestDB.bak' 
    WITH 
		NOFORMAT
		,NOINIT  
		,NAME = N'TestDB-Full Backup'
		,SKIP
		,NOREWIND
		,NOUNLOAD
		,COMPRESSION
		,STATS = 10
GO

至此已完成測試準備,

接著以不同Instance之資料庫進行資料庫還原測試。

1. 直接附加資料庫檔案(.mdf):使用SQL staetment與SSMS

CREATE DATABASE [AttachTestDB] ON   
    (FILENAME = N'D:\TDE\BK\DB\TestDB.mdf'),  
    (FILENAME = N'D:\TDE\BK\DB\TestDB.LDF')
FOR ATTACH;  
GO 

 

2. 使用備份檔案(.bak)進行還原:使用SQL staetment與SSMS

RESTORE DATABASE [RestoreTestDB] 
    FROM DISK = N'D:\TDE\BK\DB\TestDB.Bak'
	WITH 
	    FILE = 1
	    ,NOUNLOAD
		,NORECOVERY
		,REPLACE
	    ,STATS = 10
GO

由以上結果確認,在沒有對應的憑證之前,資料庫檔案(.mdf、.bak)已確實受到保護。

 

 

@使用憑證正確還原資料庫

/*從來源備份 Master Key*/
USE [master]
GO
BACKUP MASTER KEY 
    TO FILE = 'D:\TDE\BK\KEY\MASTER_KEY'
    ENCRYPTION BY PASSWORD = 'MASTER_KEY'
GO

/*從來源備份憑證,並以私鑰防護*/
BACKUP CERTIFICATE TestDbCert 
    TO FILE = 'D:\TDE\BK\CERTIFICATE\TestDbCert'
    WITH PRIVATE KEY (
	    FILE='D:\TDE\BK\KEY\TestDbCert_Private_Key',
		ENCRYPTION BY PASSWORD='Tp@12341234'
	)
GO

/*=====於另一Instance還原DB=====*/
USE [master]
GO

/*匯入Database Master key*/
--Force :無論是否解析成功都強制覆蓋,若無指定此參數,將會先進行解析,若已存在相同KEY則會告知已存在相同KEY不進行匯入
RESTORE MASTER KEY FROM FILE = 'D:\TDE\BK\KEY\MASTER_KEY' 
    DECRYPTION BY PASSWORD = 'MASTER_KEY'    --以原本的password解開
    ENCRYPTION BY PASSWORD = 'MASTER_KEY_NEW'  --加上新的password
	Force
GO

/*將  Database Master key 與 Service Master Key 重新關聯,需先進行此動作才能順利匯入憑證*/
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MASTER_KEY_NEW'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
GO

/*使用私鑰匯入憑證*/
CREATE CERTIFICATE FromTestDbCert
    FROM FILE = 'D:\TDE\BK\CERTIFICATE\TestDbCert'     
    WITH PRIVATE KEY (
	    FILE = 'D:\TDE\BK\KEY\TestDbCert_Private_Key', 
        DECRYPTION BY PASSWORD = 'Tp@12341234'
    )
GO

/*還原1:附加資料庫檔案*/
CREATE DATABASE [AttachTestDB] ON   
    (FILENAME = N'D:\TDE\BK\DB\TestDB.mdf'),  
    (FILENAME = N'D:\TDE\BK\DB\TestDB_log.LDF')
FOR ATTACH;  
GO

/*還原2:使用備份檔案還原*/
RESTORE DATABASE [RestoreTestDB] 
    FROM DISK = N'D:\TDE\BK\DB\TestDB.bak' 
	    WITH 
		    FILE = 1  
			,MOVE N'TestDB' TO N'D:\MSSQL\DATA\TestDB.mdf'  
			,MOVE N'TestDB_log' TO N'D:\MSSQL\DATA\TestDB_log.ldf'  
			,NOUNLOAD  
			,STATS = 5

GO

--成功還原TestDB至其他Instance

/*===== The End =====*/

 

 

@效能測試

為增大結果數據差距幅度之可能性,以較低硬體配備進行,

不以單一運行模式進行,以較貼近實際整體運行狀況為基礎,提高頻率來做模擬測試

測試環境

(AP Server)

OS Windows Server 2019 Stand
CPU Intel Core i5 3300
RAM 8G
STORAGE 256G SSD

測試環境

(DB Server)

規格與 AP Server 相同

DB : SQL Server 2106 Enterprise

測試情境
使用人數

使用者3000人

(起始100,10分鐘內逐步增加至3000)

持續時間

30分鐘

使用者行為/週期

新增30筆;讀取200筆;更新10筆 / sec

Max Loading : 

新增90,000筆;讀取600,000;更新30,000筆 / 每秒

 

測試結果

環境

項目

未啟用TDE 啟用TDE

效能差異%

((啟用-未啟用)/未啟用)*100%

AP Server

平均回應時間(秒)

Avg.Response Time(sec)

1.96s 2.05s 4.59%
DB Server 

CPU 平均使用率

37% 41% 10.81%

MEMORY 平均使用率

63% 72% 14.28%

DISK IO 平均使用率

平均回應時間

5%

1433/ms

6%

1467/ms

20%

2.37%

測試結果分析:

1.平均回應時間無明顯增加,Client端使用者幾乎沒有影響

2.DB Server資源耗用增加比例主要在CPU與MEMORY上,因以較低規格硬體設備加高頻率使用者行為進行測試,故真正增加的資源耗用幅度比例應較測試結果來的要低,須再依系統實際運行模式進行估算。

結論:以一般系統來評估,增加的資源消耗應屬可接受範圍。

 

 

@心得摘要

1.以Instance為基礎,Database為主要個體對應。

2.憑證與金鑰以何方式建置、存放於哪並無固定,皆可獨立拆分,完全取決於規劃設計

3.啟用加密之影響:

    01.不影響原程式存取資料

    02.資料庫檔案(.mdf、.ldf、.bak)未見明顯增加,故無需大幅擴增儲存空間

    03.整體效能影響約5-10%

    04.只要有一個資料庫啟用加密,該Instance之tempdb將會自動啟動加密,多少會對其他資料庫效能造成影響

    05.此機制非於資料層進行加密,故不受資料量大小影響

4.須妥善保管憑證與金鑰,若原Instance毀損加上遺失憑證/金鑰,將無法再存取該資料庫相關資料。

(每次安裝新 Instance 之 Service Master Key 皆是唯一)

5.無法僅針對部分資料加密(同資料庫無法拆分)

6.FILESTREAM(FILETABLE)不在保護範圍內

7.ALGORITHM 依各 OS 版本之支援度而定

 

 

@延伸應用

1.使用憑證對機敏性資料進行加解密

2.利用憑證授權來控管使用者帳號

3.異地備援之安全性防護

4.雲端資料同步之安全防護

 

 

@參考資料

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?redirectedfrom=MSDN&view=sql-server-ver15

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/cc278098(v=sql.100)?redirectedfrom=MSDN#_Toc189384694

蔡宗勳 Tsunghsun Tsai