SQL Server 透明資料加密（TDE）

透明資料加密（TDE）是 SQL Server 中的一項功能，用於對資料庫中的數據進行加密。TDE 通過對數據庫的物理文件進行加密，保護數據在儲存時的安全性。當 TDE 啟用後，數據在寫入磁盤之前會被自動加密，在讀取時自動解密。TDE 使用對稱金鑰進行加解密，該金鑰由主要密鑰保護。TDE 提供強大的數據保護，確保數據在儲存和傳輸過程中的機密性。

在早年這功能要用 SQL Enterprise 才能使用此功能，SQL 2019 後這功能下放到 Standard 版了。

現有資料庫啟用 TDE 步驟

啟用金鑰與建立憑證

USE master;
GO
--創建主要金鑰(那個密碼不要忘掉)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDE@0123456789';
GO
--創建加密憑證
CREATE CERTIFICATE TdeServerCert WITH SUBJECT = N'透明加密憑證';
GO

啟用資料庫TDE功能

USE 目標資料庫;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TdeServerCert;
GO
ALTER DATABASE 目標資料庫
SET ENCRYPTION ON;
GO

啟用 TDE 後 資料庫的備份與移轉

啟用TDE後，資料庫的 .mdf .ldf .bak 等檔案都是受到保護的，直接拿到別臺主機是不能還原與使用的，需要先把原主機上的憑證備份遷移過去。才能還原 bak 或掛載 .mdf .ldf。

備份憑證(在原主機執行)

BACKUP CERTIFICATE TdeServerCert
TO FILE = '/var/MsSqlBackUp/TdeServerCert.bak'
WITH PRIVATE KEY (
    FILE = '/var/MsSqlBackUp/TdeServerCertKey.pvk',
    ENCRYPTION BY PASSWORD = 'TDE@9876543210'
);

還原憑證(在新主機執行)

USE master;
GO
--創建主要金鑰(那個密碼不要忘掉，這邊的主要金鑰可以直接建立新的不需要從原主機備份過來)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NEWTDE@0123456789';
GO
--還原 TDE 使用憑證.(TdeServerCert.bak TdeServerCertKey.pvk 從原主機備份下來的)
CREATE CERTIFICATE TdeServerCert
FROM FILE = '/var/MsSqlBackUp/TdeServerCert.bak'
WITH PRIVATE KEY (
    FILE = '/var/MsSqlBackUp/TdeServerCertKey.pvk',
    DECRYPTION BY PASSWORD = 'TDE@9876543210'
);
--還原資料庫
USE [master]
RESTORE DATABASE [目標資料庫] 
FROM  DISK = N'/var/MsSqlBackUp/目標資料庫.bak' WITH  FILE = 1,  
MOVE N'EPB_CEMS' TO N'/var/opt/mssql/user/data/目標資料庫.mdf',  
MOVE N'EPB_CEMS_log' TO N'/var/opt/mssql/user/data/目標資料庫.ldf',  
NOUNLOAD,  STATS = 5
GO

其他作業參考

主要金鑰操作

--備份主要金鑰
BACKUP MASTER KEY
TO FILE = '/var/MsSqlBackUp/MasterKey.bak'
ENCRYPTION BY PASSWORD = 'TDE@9876543210'; /*這邊的密碼是 .bak 檔的密碼，跟原本 CREATE 無關*/

--還原主要金鑰
RESTORE MASTER KEY
FROM FILE = '/var/MsSqlBackUp/MasterKey.bak'
DECRYPTION BY PASSWORD = 'TDE@9876543210'  /*這邊的密碼是 .bak 檔的密碼*/
ENCRYPTION BY PASSWORD = 'TDE@0123456789'; /*這個是日後如果要 OPEN MASTER KEY 時需要用到的密碼*/

--開啟主要金鑰(還原後預設狀態是 CLOSE 的，這時候需要先 OPEN 才能使用，OPEN 的時候需要當初設定的密碼)
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'TDE@0123456789';
--關閉主要金鑰，關閉後 TDE 資料庫還是可以操作，但無法在創建的憑證
CLOSE MASTER KEY;
--刪除主要金鑰，當金鑰還被某些憑證使用時基本也是刪不掉的
DROP MASTER KEY;
--查詢目前主要金鑰的設定狀況
SELECT name, is_master_key_encrypted_by_server FROM sys.databases
---

憑證操作參考

-- 查詢目前資料庫中已經有哪些憑證
SELECT * FROM sys.certificates
-- 刪除憑證(當憑證被作用於某個TDE資料庫時，是刪不掉的)
DROP CERTIFICATE TdeServerCert;

查詢目前TDE處理的進度

select 
    DB_NAME(database_id) ,  
    encryption_state_desc,
    encryption_scan_state_desc, 
    encryption_scan_modify_date,
    percent_complete
from sys.dm_database_encryption_keys

TDE憑證更新

--建立新的憑證
USE [master]
GO

CREATE CERTIFICATE NewTDECert
WITH SUBJECT = 'New TDE DEK Certificate',
EXPIRY_DATE = '20181231';
GO 

--抽換目前使用的憑證
USE [目標資料庫]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE NewTDECert;
GO

補充說明

啟用 TDE 後幾個重要的東西要備份好，搞丟了光拿到資料庫備份檔也無法使用還原使用。

• MASTER KEY 的密碼
• 備份下來的憑證(有兩個檔案一個憑證一個私鑰)
• 備份憑證用的密碼

網路參考資料

• 透明資料加密 (TDE)
• SQL Server 利用透明資料加密(TDE)對整個資料庫加密
• [SQL SERVER]瞭解透明資料加密 (TDE)#實做
• Updating an expired SQL Server TDE certificate