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

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

@摘要說明

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

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

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

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

@測試案例

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

/*建立 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 =====*/

@效能測試

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

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

測試結果分析：

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