# 資料庫欄位加密以及金鑰移轉的方法，使用 Sql Server always encrypted 資料庫加密

## 資料庫加密的用途

資料庫加密可以達到幾個目的
1. 資料原始檔被竊取，但沒有拿到金鑰時，無法正確解密，看不到機密資料
2. 資料庫加密可以避免未經授權的系統管理者取得資料

舉例來說，如果資料庫中的資料加密了，系統管理員在資料庫中檢視資料，看到的都是加密的內容，需要擁有解密憑證(金鑰)才能看到真正的資料。
* 如果目標是確保組織中的 DBA 無法存取敏感性資料，建議使用角色隔離管理金鑰。
:::info
只在應用程式伺服器存放金鑰，資料庫系統的伺服器作業系統中；或是資料庫管理者的帳號無法取得拿不到金鑰即可
:::
* 如果目標是限制雲端管理員 (而不是內部部署 DBA) 存取敏感性資料，可建議不使用角色隔離來管理金鑰。
參考資料：[金鑰管理角色](https://docs.microsoft.com/zh-tw/sql/relational-databases/security/encryption/overview-of-key-management-for-always-encrypted?view=sql-server-ver16#key-management-roles)

## Sql Server 版本比較，共五個版本
1. SQL Server Enterprise
2. SQL Server Standard
3. SQL Server Web Edition
4. SQL Server Developer Edition
5. Express Edition
Express 版本可以使用 Always Encrypted 資料庫欄位加密功能

但 Express 版本沒有把備份檔加密的功能

其他各版本功能比較與差異詳見以下連結
https://docs.microsoft.com/zh-tw/sql/sql-server/editions-and-components-of-sql-server-version-15?view=sql-server-ver16

## 要解決的問題

1. 能不能做全資料庫加密? (自動增加的 id 好像不行)
1. 如何確認資料已被加密? (不要帶金鑰連線，或是按加密的資料行檢查加密狀態)
1. 如何觀看解密資料?
2. 如何改回未加密的狀態? (有金鑰的狀態，按加密資料行，選成純文字)
3. 加密金鑰被放在哪裡? (預設位置在 Get-ChildItem -Path Cert:\CurrentUser\My )
4. 加密金鑰的備份還原的方法?
5. 被加密後的資料，能不能用 where and or like 等等條件式搜尋? (好像都不行)
6. 加密後的運作效能評估? (新增、修改、查詢都需要評估)

## 加密金鑰盤點以及存放位置
可以在資料庫輸入以下語法查詢

SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values

查詢結果範例

CMK_Auto1	1	2024-02-22 22:31:17.280	2024-02-22 22:31:17.280	MSSQL_CERTIFICATE_STORE	CurrentUser/my/D67B7EB7209277012DFB72A90C9D4043E227B136	0	NULL

## 解密資料方法1 圖形介面管理工具
GUI 登入介面，要選 其他連線參數(Additional Connection Parameters) 頁籤，加入「Column Encryption Setting = Enabled」
這樣連線進去才看得到資料，沒加的話會看到加密的資料

## Always Encrypted 的效能影響
Always Encrypted 的加解密不是在資料庫進行，而是在用戶端(client)的應用程式 or GUI 圖形介面工具，所以大部分的效能額外負荷是在資料庫上看不出來，除了加密和解密作業成本之外，用戶端其他的效能額外負荷來源如下：
* 額外往返資料庫以擷取查詢參數的中繼資料。
* 呼叫資料行主要金鑰存放區以存取資料行主要金鑰。

參考來源：[控制 Always Encrypted 的效能影響](https://docs.microsoft.com/zh-tw/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver?view=sql-server-ver16#controlling-the-performance-impact-of-always-encrypted)

---

## 實際執行加密

跟著微軟教學 [教學課程：開始使用 Always Encrypted](https://learn.microsoft.com/zh-tw/sql/relational-databases/security/encryption/always-encrypted-tutorial-getting-started?view=sql-server-ver16&tabs=ssms)
可以正常做到資料庫加密，但加密完後 Visual Studio 的連線字串要加上 ```Column Encryption Setting=Enabled;``` 才能在程式內解密，並且和 EntityFramework 搭配，如果加上去後發現沒有解密，可以重開整個 visual studio 讓程式重新讀取一次

以下是包含 Column Encryption 的完整的連線字串範例

`
<add name="ContosoHREntities" connectionString="metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost\SQLEXPRESS;initial catalog=ContosoHR;Column Encryption Setting=Enabled;integrated security=True;encrypt=False;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
`

* EF6資料庫加密 優先閱讀這篇 sql server 加密和ef程式連動的方法
https://dotblogs.com.tw/yc421206/2019/05/18/ef6_connect_sql_server_2016_always_encrypted

* 再閱讀同作者這篇 加密後的使用限制
https://dotblogs.com.tw/yc421206/2019/05/31/ef6_connect_sql_server_2016_always_encrypted_limit_solution

---

## 匯出金鑰

如果要更換電腦，金鑰檔消失的話會造成無法讀取也無法解密，這非常重要

* 匯出的方法：在開始功能表輸入 certmgr.msc 或 mmc ，開啟 mmc 工具

* 開啟 新增/移除 嵌入式管理單元

* 左邊捲到最下面，選擇憑證後，勾其中一個(看當初金鑰放哪，不知道就選三次都加進來)

* 以我來說，是在(目前的使用者)個人 > 憑證 ，找到 Always Encrypted Auto Certificate 1 和 2 的金鑰，按右鍵匯出金鑰

* 匯出的 pfx 檔，帶到另外一台電腦，一樣開mmc 憑證，按右鍵選匯入

* 匯入找不到的話，下拉選單記得改成.pfx

* 使用Sql server management studio 的圖形介面工具查詢時，第一次查可能會出現的畫面

---

## 移轉資料庫，但是沒有正確移轉金鑰，可能會出現的錯誤訊息

如果資料庫還原到其他電腦時，會出現這樣的訊息，如果出現的話，請參考[](https://) #匯出金鑰 的部分

標題: Microsoft SQL Server Management Studio

------------------------------

無法解密資料行 '欄位名稱'。
無法使用金鑰存放區提供者 'MSSQL_CERTIFICATE_STORE' 解密資料行加密金鑰。加密資料行的加密金鑰前 10 個位元組為: '99-C1-70-B0-CE-72-B9-60-5B-55'。
在憑證位置 'CurrentUser' 的憑證存放區 'My' 中找不到指紋為 'D12H4W6C77F944W56B45 ' 的憑證。請確認資料庫內資料行主要金鑰定義中的憑證路徑正確，且憑證已經正確地匯入該憑證位置/存放區。
參數名稱: masterKeyPath

------------------------------

錯誤畫面

---

* Always Encrypted 介紹
https://docs.microsoft.com/zh-tw/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver16

* 使用 Always Encrypted 與 SQL Server Management Studio 查詢資料行
https://docs.microsoft.com/zh-tw/sql/relational-databases/security/encryption/always-encrypted-query-columns-ssms?view=sql-server-ver16

* 建立完整資料庫備份 (含加密備份的內容)
https://docs.microsoft.com/zh-tw/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver16

* Always Encrypted 的金鑰管理概觀
https://docs.microsoft.com/zh-tw/sql/relational-databases/security/encryption/overview-of-key-management-for-always-encrypted?view=sql-server-ver16

* 使用 SQL Server Management Studio 輪替 Always Encrypted 金鑰
https://docs.microsoft.com/zh-tw/sql/relational-databases/security/encryption/rotate-always-encrypted-keys-using-ssms?view=sql-server-ver16

* 搭配 .NET Framework Data Provider for SQL Server 使用 [永遠加密]
https://docs.microsoft.com/zh-tw/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider?view=sql-server-ver16

* [Day07] 資料儲存安全(SQL Always Encrypted)上
https://ithelp.ithome.com.tw/articles/10187092

* [Day08] 資料儲存安全(SQL Always Encrypted)下
包含 .NET程式端寫入資料 .NET專案目標framework必須為.NET 4.6
https://ithelp.ithome.com.tw/articles/10187234

* [SQL Server]Always Encrypted筆記(SQL Server 2016新功能)
https://dotblogs.com.tw/stanley14/2016/03/19/165914

* [實作] SQL 資料加密(Always Encrypted)
https://dotblogs.azurewebsites.net/BigCow/2019/12/26/155226

* 使用SQL Server 2016的Always Encrypt功能防止系統管理員讀取私密性資料
https://www.uuu.com.tw/Public/content/article/19/20190805.htm