Try   HackMD

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

資料庫加密的用途

資料庫加密可以達到幾個目的

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

舉例來說，如果資料庫中的資料加密了，系統管理員在資料庫中檢視資料，看到的都是加密的內容，需要擁有解密憑證(金鑰)才能看到真正的資料。

  • 如果目標是確保組織中的 DBA 無法存取敏感性資料，建議使用角色隔離管理金鑰。

只在應用程式伺服器存放金鑰，資料庫系統的伺服器作業系統中；或是資料庫管理者的帳號無法取得拿不到金鑰即可

  • 如果目標是限制雲端管理員 (而不是內部部署 DBA) 存取敏感性資料，可建議不使用角色隔離來管理金鑰。
    參考資料：金鑰管理角色

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 資料庫欄位加密功能
    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

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

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

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

要解決的問題

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

加密金鑰盤點以及存放位置

可以在資料庫輸入以下語法查詢




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 的效能影響

實際執行加密

跟著微軟教學 教學課程：開始使用 Always Encrypted
可以正常做到資料庫加密，但加密完後 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" />

匯出金鑰

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

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

    Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →

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

    Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →

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

    Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →

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

    Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →

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

    Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →

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

    Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →

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

    Image Not Showing Possible Reasons
    • The image was uploaded to a note which you don't have access to
    • The note which the image was originally uploaded to has been deleted
    Learn More →

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

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

標題: Microsoft SQL Server Management Studio

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

錯誤畫面

Image Not Showing Possible Reasons
  • The image was uploaded to a note which you don't have access to
  • The note which the image was originally uploaded to has been deleted
Learn More →

Last changed by 
 
Not
你好，這邊有一些不限我個人的筆記，如果你發現哪邊有錯或是可以更好，歡迎在筆記上留言或是直接修改 前端隨手寫 https://codepen.io/not0000 後端隨手寫 https://dotnetfiddle.net/Authors/72023/not0000
0
3220

Read more

Google Cloud Skills AI課程

https://www.cloudskillsboost.google/paths/118?locale=zh_TW

May 7, 2025
網站檢測的資安風險修正筆記

IIS Crypto 是一個免費軟體，可以利用圖形介面關閉舊的加密協定或是不安全的加密演算法

Feb 20, 2025
免費 可商業使用 圖片 文字 音樂 影片

如果擔心版權問題，請見 素材使用注意事項

Feb 12, 2025
ASP.NET MVC 打包(Bundling)及壓縮(Minification)功能

MVC提供方便的Bundling功能，可以把css、js打包壓縮，只要兩個步驟就完成了

Jan 4, 2025
Read more from Not
Published on HackMD