SQL Server Encryption Solutions (資料庫加密解決方案)

2021-11-05

筆記 SQL Server 在安全性上各相關的加密保護資料方式，包含欄位加密、備份檔加密、透明資料加密以及 Always Encrypted 等加密方式。

說明

Backup Encryption

在 Master Database 建立 Master Key，接著同樣於 Master Database 建立 Certificate，並以先前的 Master Key 加密該憑證。而在備份資料庫時，選擇以憑證對資料庫檔進行加密。

需要注意的是憑證必須妥善管理，否則若遺失憑證會無法還原資料庫備份檔；此外憑證逾期並不影響加密後資料庫備份檔的還原。

SQL Server Backup Encryption 資料庫備份的加密方式

實驗 🧪

❗ Master Key 的密碼是否影響 Certificate 的還原使用
❗ 還原到第二台伺服器後，再次備份，使用不同的 Master Key 是否能夠還原到第一台伺服器

Transparent Data Encryption

在 Master Database 建立 Master Key，接著同樣於 Master Database 建立 Certificate，並以先前的 Master Key 加密該憑證。TDE 屬於 Database Level 的加密方式，因此選定資料庫後設定進行 TDE 加密：

USE Master;
ALTER DATABASE DatabaseName SET ENCRYPTION ON;
GO

取消 TDE 的方式：

USE Master;
ALTER DATABASE DatabaseName SET ENCRYPTION OFF;
GO

透明資料加密 (TDE) | learn.microsoft

實驗 🧪

驗證 TDE 加密資料庫檔案後，附加至其他資料庫伺服器 (預期失敗)
驗證 TDE 加密資料庫檔案後，備份檔還原 (預期失敗)
驗證還原憑證後，將加密資料庫檔案附加至其他資料庫伺服器 (預期成功)
驗證還原憑證後，將加密資料庫檔案備份並還原至其他資料庫伺服器 (預期成功)

❗ Master Key 的密碼不影響 Certificate 的還原使用

Dynamic Data Masking

Dynamic Data Masking

Column Encryption

不同於 TDE 以及 Backup Encryption，Column Encryption 的 Master Key 以及 Certificate 是儲存於 User Database 而非 Master Database。

此外 Columne Encryption 不一定需要 Master Key 以及 Certificate 來加密 Symmetric Key，可以只接用 Password 的方式加密 Symmetric Key：

CREATE SYMMETRIC KEY SSN_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY password = 'P@ssw0rd';
GO

ALTER TABLE [dbo].[Cases]
    ADD EncryptedText varbinary(512);
GO

OPEN SYMMETRIC KEY SSN_Key
   DECRYPTION BY PASSWORD = 'P@ssw0rd';

UPDATE [dbo].[Cases]
	SET EncryptedText = EncryptByKey(Key_GUID('SSN_Key'), fullName);
GO

SELECT EncryptedText, DECRYPTBYKEY(EncryptedText) FROM [dbo].[Cases]

如果要使用 Master Key 來加密 Certificate，在使用 Certificate 來加密 Symmetric Key 則以下列語法進行：

Use DatabaseName;

CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'P@ssw0rd';

CREATE CERTIFICATE CertificateEncrypt
   WITH SUBJECT = 'Certificate Subject';
GO

CREATE SYMMETRIC KEY SSN_Key
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CertificateEncrypt;
GO

對 Client 端而言，每個連線的 Session 必須先 Open Symmetric Key 才取對資料進行解密讀取

OPEN SYMMETRIC KEY SSN_Key
   DECRYPTION BY PASSWORD = 'P@ssw0rd';

CLOSE SYMMETRIC KEY SSN_KEY;

而 Open Symmetric Key 需要對 Symmetric Key 有 View Definition 的權限。而如果 Symmetric Key 是以憑證加密的，則還需要對憑證擁有 Control 的權限。

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SSN_Key TO [UserName];  

Encrypt a Column of Data | learn.microsoft

Always Encrypted

參考資料

利用透明資料加密(TDE)對整個資料庫加密