[實作] SQL 資料加密(Always Encrypted)

  • 3724
  • 0
  • SQL
  • 2020-09-17

Azure SQL Always Encrypted 實作

最近在備份Azure SQL時發現無法正確匯出

原因是當初資料欄位加密是採用對稱金鑰

只好重新改寫加解密方式

 

  1. 建立憑證及欄位加密主要金鑰(CMK:Column Master Key)

    [資料庫] > [安全性] > [Always Encrypted 金鑰] > [新增資料行主要金鑰]


    建立憑證及CMK




     
  2. 建立欄位加密金鑰(CEK:Column Encryption Key)並且CMK加密

    [資料庫] > [安全性] > [Always Encrypted 金鑰] > [資料行加密金鑰] > [新增資料行加密金鑰]


    建立CEK並且CMK加密


     
  3. 指定欄位使用 Always Encrypted一律加密













     


    也可採用指令碼方式產生(針對SSN欄位做加密)
    USE SecurityDB
    CREATE TABLE [dbo].[Patients](
    [Name][varchar] (20), 
    [SSN] [char](11) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, 
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
    COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL,
    [Country] [varchar](50) NULL)

     

    定序問題

    ALTER DATABASE SecurityDB  
    COLLATE Latin1_General_BIN2 ;  
    GO 

     

  4. DB Server 匯出憑證

    左下角放大鏡輸入[執行] => 輸入[mmc] => 啟動主控台根目錄  點選新增/移除嵌入式管理單元











     
  5. 匯出的憑證上傳到Server然後安裝,同步驟4先啟動主控台根目錄






    如果程式在IIS上執行會發生錯誤可能是IIS權限不足的關係
    可參考最下方連結參考8.



  6. 測試加解密
    .Net framework 版本必須為4.6以上,SSMS版本必須為17.0以上。
    Web.config 或 SSMS參數需要加上以下連線字串 Column Encryption Setting=Enabled




    新增/編輯/查詢時啟用資料加密的欄位必須以參數方式帶入

 

參考


  1. [Day07] 資料儲存安全(SQL Always Encrypted)上
  2. [Day08] 資料儲存安全(SQL Always Encrypted)下
  3. [SQL Server]Always Encrypted筆記(SQL Server 2016新功能)
  4. MSSQL 2016 使用 Always Encrypted(始終加密)
  5. VIEWING ALWAYS ENCRYPTED DATA IN SSMS
  6. 於Azure SQL Database使用Always Encrypted
  7. [SQL Server]Always Encrypted筆記(SQL Server 2016新功能)
  8. How to give ASP.NET access to a private key in a certificate in the certificate store?