--- # System prepended metadata title: Sql Server always encrypted 資料庫加密 tags: [資安, 資料庫] --- # 資料庫欄位加密以及金鑰移轉的方法，使用 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 資料庫欄位加密功能 ![](https://i.imgur.com/XF5Td5h.png) 但 Express 版本沒有把備份檔加密的功能 ![](https://i.imgur.com/88dg1od.png) 其他各版本功能比較與差異詳見以下連結 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. 加密後的運作效能評估? (新增、修改、查詢都需要評估) ## 加密金鑰盤點以及存放位置 可以在資料庫輸入以下語法查詢 ```sql= 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 的完整的連線字串範例 ` ` * 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 工具 ![image](https://hackmd.io/_uploads/BJ_JTc2XA.png) * 開啟 新增/移除 嵌入式管理單元 ![image](https://hackmd.io/_uploads/S1JZ6q2XR.png) * 左邊捲到最下面，選擇憑證後，勾其中一個(看當初金鑰放哪，不知道就選三次都加進來) ![image](https://hackmd.io/_uploads/Hk5Z0c2m0.png) * 以我來說，是在(目前的使用者)個人 > 憑證 ，找到 Always Encrypted Auto Certificate 1 和 2 的金鑰，按右鍵匯出金鑰 ![image](https://hackmd.io/_uploads/SJEK392XA.png) * 匯出的 pfx 檔，帶到另外一台電腦，一樣開mmc 憑證，按右鍵選匯入 ![image](https://hackmd.io/_uploads/Sydy1s3m0.png) * 匯入找不到的話，下拉選單記得改成.pfx ![image](https://hackmd.io/_uploads/HJb4Jih70.png) * 使用Sql server management studio 的圖形介面工具查詢時，第一次查可能會出現的畫面 ![image](https://hackmd.io/_uploads/HkX-oKhX0.png) --- ## 移轉資料庫，但是沒有正確移轉金鑰，可能會出現的錯誤訊息 如果資料庫還原到其他電腦時，會出現這樣的訊息，如果出現的話，請參考[](https://) #匯出金鑰 的部分 標題: Microsoft SQL Server Management Studio ------------------------------ 無法解密資料行 '欄位名稱'。 無法使用金鑰存放區提供者 'MSSQL_CERTIFICATE_STORE' 解密資料行加密金鑰。加密資料行的加密金鑰前 10 個位元組為: '99-C1-70-B0-CE-72-B9-60-5B-55'。 在憑證位置 'CurrentUser' 的憑證存放區 'My' 中找不到指紋為 'D12H4W6C77F944W56B45 ' 的憑證。請確認資料庫內資料行主要金鑰定義中的憑證路徑正確，且憑證已經正確地匯入該憑證位置/存放區。 參數名稱: masterKeyPath ------------------------------ 錯誤畫面 ![image](https://hackmd.io/_uploads/BkRjaK370.png) --- * 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