:::success
**Always Encrypted (SSMS的加密精靈)** 是 Microsoft SQL Server 和 Azure SQL Database 提供的一種數據保護功能。允許數據在應用程序與數據庫之間加密，即使數據庫管理員和數據庫本身都無法訪問明文數據。
:::

## 用途
**Always Encrypted** 主要用於保護存儲在 SQL Server 或 Azure SQL Database 中的敏感數據，**確保在傳輸和存儲過程中數據始終保持加密狀態**。這有助於防止數據在數據庫或備份中被未經授權的人員訪問，即使他們擁有數據庫的完整權限，也無法讀取敏感信息。
(**Always Encrypted 的核心理念是讓資料庫伺服器無法解密數據**)

## 應用場景
##### 敏感數據。

##### 雲端數據庫：當數據存儲在 Azure SQL Database 等雲端服務中，使用 Always Encrypted 可以防止雲端提供商等第三方未經授權的訪問。
## 優點
##### 數據隱私保護：數據即使存儲在數據庫中也無法被數據庫管理員或其他內部人員查看。
##### 數據傳輸安全：加密數據從客戶端到服務器端的整個過程都是加密的。
##### 最小化信任：即使數據庫服務器本身被入侵，攻擊者也無法解密數據，因為解密操作只在應用端進行。
##### 合規性：有助於滿足各種行業規範，如 HIPAA、PCI-DSS 和 GDPR 等，這些規範對敏感數據的保護有嚴格要求。
##### 應用透明性：Always Encrypted 支持在不需要修改應用邏輯的情況下啟用，應用層幾乎無需感知加密過程。
## 缺點
##### 性能影響：加密和解密操作在應用端進行，這會增加一些性能負擔，尤其是在處理大量敏感數據時，可能導致響應時間變慢。
##### 有限的 SQL 支援：並非所有 SQL 操作都支援 Always Encrypted。例如，對加密列進行篩選、分組和排序時，功能會受到限制，某些函數和操作無法在加密列上運行。
##### 數據類型限制：並非所有數據類型都支援 Always Encrypted，主要支援字符類型和數字類型的列。
##### 應用程序修改：雖然 Always Encrypted 是透明的，但某些情況下，應用端需要特殊處理以適應加密/解密操作，如配置數據提供程序來支持 Always Encrypted。
## 適用框架、語言和資料庫
### 支援資料庫：

Microsoft SQL Server 2016 及更新版本
Azure SQL Database
SQL Server Always Encrypted 可以通過 T-SQL 和 SQL Server Management Studio (SSMS) 管理。
### 支援的框架和語言：
Always Encrypted 的主要支援在 Microsoft 的技術堆棧上實現，通常與以下框架和語言一起使用：

##### .NET Framework 和 .NET Core：使用 ADO.NET 驅動來訪問 SQL Server 數據庫時，可啟用 Always Encrypted。
##### Java：通過 Microsoft 提供的 JDBC 驅動程式支援 Always Encrypted。
##### ODBC 驅動程式：支援 Always Encrypted 的 ODBC 驅動程式可用於不同語言的應用，如 C++。
##### PowerShell：可以用來管理加密金鑰和加密配置。

## 密鑰管理：

Always Encrypted 使用兩種類型的密鑰來保護數據：列加密密鑰 (CEK) 和列主密鑰 (CMK)。
##### CEK（Column Encryption Key）：用於實際加密敏感數據列的密鑰。
##### CMK（Column Master Key）：用於保護 CEK 的密鑰，可以存儲在 Windows Certificate Store、Azure Key Vault 等受信的密鑰存儲中。
## 加密模式：

Always Encrypted 支持兩種加密模式：
##### 確定性加密：加密後相同的明文會生成相同的密文。這允許在加密列上進行相等比較操作，但會暴露數據模式。
##### 隨機加密：相同的明文每次加密都會生成不同的密文，提供更強的安全性，但無法進行相等比較或聯接操作。
## 適用場合的設計考量
##### 設計密鑰輪換策略：需要設計一個定期輪換加密密鑰的策略，以增強安全性並滿足合規要求。
##### 性能調優：由於加密/解密操作會影響性能，應對涉及大量加密數據的應用進行性能調整，並僅加密真正敏感的列。
##### 測試場景：在部署 Always Encrypted 時，需要在測試環境中模擬實際負載和查詢場景，確保其對應用的影響在可接受範圍內。
## 可能的問題
#### 部分資料型別或是定序規則不支援。
#### **確定性加密**需要使用二進制排序規則（如 Latin1_General_BIN2），這可能會導致應用程式或現有資料庫結構的調整。
#### 資料型別如一些大型物件（如 TEXT、NTEXT 或 IMAGE）無法使用 Always Encrypted 進行加密。

---
## .NET Framework + SSMS 實作加密資料 Always Encrypt
### 主要金鑰組態（Master Key Configuration）
主要金鑰組態的目的是設定用來保護資料加密金鑰（Column Encryption Key, CEK）的列加密金鑰（Column Master Key, CMK）。在 Always Encrypted 中，CMK 是用來加密和保護 CEK 的金鑰，而 CEK 則是實際上用來加密資料庫中的資料列。
### 就地加密設定:
就地加密設定涉及如何將現有資料加密，即加密實際數據的過程是否會直接修改現有的資料列（就地加密），或是使用其他方式進行加密。
主要用於確定如何在不影響正常操作的情況下將敏感資料加密，適合針對現有的資料庫實施加密。
對於性能考量比較多，數據大量時有啟用記憶體保護區，可以將資料搬移至外面再做加密，不影響SQL其他操作讀寫的效能。

## .NET Framework 實作 (.NET Framework4.6 ⭡)
1. ADO.NET & Dapper : 需再DB連線字串加上 "Column Encryption Setting=Enabled"，需使用參數化方式。
－[ADO.NET with Always Encrypted](https://ithelp.ithome.com.tw/m/articles/10187092)
－[懶人包-史丹利好熱](https://dotblogs.com.tw/stanley14/2016/03/19/165914)
2. Entity Framework : 需再DB連線字串加上 "Column Encryption Setting=Enabled"，查詢條件要用參數化

var ssn = "123-45-6789"; 
context.Patients.Where(p => p.SSN == ssn);

－[EF with Always Encrpted](https://techcommunity.microsoft.com/t5/sql-server-blog/using-always-encrypted-with-entity-framework-6/ba-p/384433)

---
## 解密
- 開啟匯出的憑證(.pfx)
- 選擇 **"本機電腦"**
- 憑證存放區 選擇 **"個人"**

---
## 實作應用
- **加密欄位為比較值** 需要宣告變數。
✅**正確方式**

        DECLARE @IDCard NVARCHAR(50) = 'AXXXXXXXXX'; --假設IDCard是加密欄位
         SELECT ID
           FROM UserProfile
          WHERE IDCard = @IDCard

❌**錯誤方式**

         SELECT ID
           FROM UserProfile
          WHERE IDCard = 'AXXXXXXXXX' --加密欄位必須宣告才能做比較

- **加密欄位宣告變數** 注意順序。(宣告變數如果有加密欄位建議放在最後一個，或者將所有變數都加上預設值)
✅**正確方式**

        DECLARE @ID INT; 
        DECLARE @IDCard NVARCHAR(50) = 'AXXXXXXXXX';
        --或 DECLARE @ID INT = 0;
         SELECT @ID = P.ID
           FROM UserProfile
          WHERE IDCard = @IDCard

❌**錯誤方式**

         DECLARE @IDCard NVARCHAR(50) = 'AXXXXXXXXX';
         DECLARE @ID INT;
          SELECT @ID = P.ID
           FROM UserProfile
          WHERE IDCard = @IDCard

---
## 特殊狀況:
:::warning
An unhandled exception occurred:
Message: Failed to decrypt column 'FirstName'.
Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: 'DD-73-EE-95-6F-75-A3-0A-63-84'.
Certificate with thumbprint '0F76519B0D08D52D6EBAF5BD2B86FA46E6DBAA7F' not found in **certificate store 'My' in certificate location 'CurrentUser'**. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.
Parameter name: masterKeyPath
:::
- 上述粗體字意思為 在 **"個人憑證區的目前使用者"** 找不到對應的憑證，可以做解密。
- 這時要去確認當你在匯入使用者時，你匯入的地點是 **"目前使用者(CurrentUser)"** 或是 **"本機電腦(LocalMachine)"** 。
- 如遇到無法選擇本機電腦的狀況時，可以使用 Microsoft 管理控制台（MMC）匯入憑證：

1. 按下 Win + R，輸入 mmc 並按下 Enter，開啟 Microsoft 管理控制台（MMC）。
2. 在 MMC 中，依序點擊 檔案 > 新增/移除管理單元。
3. 在「可用的管理單元」列表中，選擇「憑證」，然後點擊「新增」。
4. 系統會提示您選擇管理憑證的對象，請選擇「本機電腦」，然後點擊「完成」。
5. 接著，點擊「確定」返回主畫面。
6. 現在您可以看到「本機電腦」的憑證存放區。
7. 展開「憑證（本機電腦）」節點，右鍵點擊您要匯入憑證的存放區（通常是「個人」或「受信任的根憑證授權單位」）。
8. 選擇「所有工作」 > 「匯入」，並按照嚮導步驟將憑證匯入到「本機電腦」的適當存放區。
- 再回到資料庫，加上 "Column Encryption Setting=Enabled"，即可正確解密資料庫。