Share via

SQL Server 2016 新功能搶先看 - Always Encrypted

  • Article

SQL Server 2016 搶先看，這次要帶大家認識 Always Encrypted 新功能！

回顧系列專文：　

1. SQL Server 2016 新功能搶先看 - Stretch Database / Managed Backup (內含 Demo 畫面)

2. SQL Server 2016 新功能搶先看 - Live Query Statistics / JSON

3. SQL Server 2016 新功能搶先看 - 動態資料遮罩 / 多重 tempdb 資料檔

4. SQL Server 2016 新功能搶先看 - Stretch Database 詳解

　

本文將帶大家了解 SQL Server 2016 Always Encrypted 的功能

  1. 說明
  2. 如何運作
  3. 選擇加密類型
  4. Always Encrypted 金鑰
  5. 使用 Always Encryption 功能

說明

SQL Server 2016 提供保護機敏資料的動態資料遮罩（Dynamic Data Masking），用來防止開發人員或未經授權的使用者，有意或無意去窺探不該看的資料，這是基於資料管理者（如 DBA 或特權使用者）對於不受信任的資料存取，可以在查詢結果輸出到用戶端時自動的依照資料遮罩所設定的規則，來適當對資料產生模糊化，可以使用的一項相當便利的功能。

另外，在多租戶情境中，藉由資料列層級安全性（Row-level Security）可以集中控管資料存取的安全性，讓租戶只能存取到自己應該存取的資料，即使開發人員不小心未加任何 WHERE 篩選條件，仍然不會因此存取到其他租戶的資料。

換個角度來看，若資料擁有者（如應用程式的使用者）對於資料存放在資料庫甚至是公有雲平台上不放心，擔心資料被系統管理員動手腳，那麼 SQL Server 2016 的新推出的加密功能 — Always Encrypted 可以幫您解決這個問題，它能夠在資料傳輸到資料庫時就進行加密，查詢時也能夠自動解密，並且防止系統管理員修改被加密的資料行。

 

如何運作

Always Encrypted 功能之所以能夠實現，其中增強的 ADO.NET 程式庫扮演著重要的角色，下圖左邊為用戶端的應用程式透過增強的 ADO.NET 程式庫傳送包含機敏資料的查詢，查詢參數會被加密並傳送到右邊的 SQL Server 2016 或 Azure SQL Database，資料庫接收到的查詢參數內容為加密文字（cipher text），產生內含加密文字的查詢結果集回傳到用戶端，再由 ADO.NET 解密並還原為純文字（plain text），整個過程完全透明，也就是說，完全不須開發人員介入或是額外的程式修改，就可以輕鬆保護存在資料庫中的資料。

 

 

選擇加密類型

Always Encrypted 支援兩種加密的方式，分別為 Deterministic 和 Randomized 加密，說明如下：

  • Deterministic：相同純文字（plain text）的內容，加密後會產生相同的加密文字（cipher text），若資料行存放的值較單純（如男/女、北/中/南/東），可能容易造成被猜出裡面存放的資訊。針對加密後的內容支援分群、篩選及連結等運算，另外使用 deterministic 加密必須搭配 BIN2 的資料行定序。
  • Randomized：相對較為安全的加密方式，但不支援針對加密後的內容進行分群、篩選及連結等運算

 

Always Encrypted 金鑰

Always Encrypted 使用兩種金鑰來為資料進行加密，分別為資料行加密金鑰和資料行主要金鑰，說明如下：

  • 資料行主要金鑰（Column Master Key，CMK）：用來保護資料行加密金鑰。資料行主要金鑰應該保存在受信任的金鑰存放區（trusted key store）。
  • 資料行加密金鑰（Column Encryption Key，CEK）：用來對存放在資料行中的機敏資料進行加密，您可以使用單一資料行加密金鑰來對所有需要加密的資料行進行加密。資料行加密金鑰應備份並保存在妥善且安全的位置。

 

使用 Always Encryption 功能

在開始之前，請以下列 T-SQL 敘述來建立測試資料庫。

USE master

GO

 

--步驟一、建立測試資料庫

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'AlwaysEncryptedDB')

        DROP DATABASE AlwaysEncryptedDB

GO

 

CREATE DATABASE AlwaysEncryptedDB

GO

然後依照下列步驟建立 Always Encrypted 金鑰及含有加密資料行的資料表。

 

步驟一、建立資料行主要金鑰（CMK）定義

在物件總管展開要啟用 Always Encrypted 功能的資料庫節點，點選【 一律加密的金鑰 > 資料行主要金鑰定義 > 新增資料行主要金鑰定義 】。

 

於【 新增資料行主要金鑰定義 】視窗輸入資料行主要金鑰的名稱，若您已經在目前使用者或本機電腦的憑證存放區建立可用來加密金鑰的憑證，可以在金鑰定義來源的下拉式選單選擇現有的憑證，否則請點選【 產生自我簽署憑證 】由 SSMS 幫您建立，建立的憑證只能選擇存放在目前使用者的憑證存放區。

 

當您點選【 產生自我簽署憑證 】即可看到 SSMS 幫您建立的憑證，選擇該憑證並按確定。

 

若一切順利，您就可以看到資料行主要金鑰已被成功建立（如下圖）。

 

上述步驟也可以利用【 CREATE COLUMN MASTER KEY DEFINITION 】敘述來完成，例如下列的 T-SQL 指令碼。

USE [AlwaysEncryptedDB]

GO

 

CREATE COLUMN MASTER KEY DEFINITION [CMK01]

WITH

(

        KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',

        KEY_PATH = N'CurrentUser/My/5B05854FEC33D54C74626E1379558CC45EF893FC'

)

GO

其中 KEY_PATH 參數必須填入憑證指紋，可以利用憑證嵌入式管理單元來查看憑證指紋的內容（如下圖）。

 

步驟二、建立資料行加密金鑰（CEK）

建立資料行主要金鑰之後接著就可以建立資料行加密金鑰，請在要啟用 Always Encrypted 功能的資料庫節點，點選【 一律加密的金鑰 > 資料行加密金鑰 > 新增資料行加密金鑰 】。

 

在【 新增資料行加密金鑰 】視窗輸入資料行加密金鑰名稱，接著選擇前面所建立的資料行主要金鑰定義，若您尚未建立資料行主要金鑰定義，也可以在此選擇新增。

輸入資料行主要金鑰名稱並選擇憑證或建立新的自我簽署憑證後按建立，即可建立資料行主要金鑰定義。

 

選擇適當的資料行主要金鑰定義後按確定來新增資料行加密金鑰。

 

新增成功後可以在【資料行加密金鑰】節點中看到前面所建立的項目（如下圖）。

 

同理可證，您也可以使用 CREATE COLUMN ENCRYPTION KEY 敘述來建資料行加密金鑰。

 

USE [AlwaysEncryptedDB]

G

 

CREATE COLUMN ENCRYPTION KEY [CEK01]

WITH VALUES

(

        COLUMN MASTER KEY DEFINITION = [CMK01],

        ALGORITHM = 'RSA_OAEP',

        ENCRYPTED_VALUE =

0x016E000001630075007200720065006E00740075007300650072002F006D0079002F003

50062003000350038003500340066006500630033003300640035003400630037003400360

03200360065003100330037003900350035003800630063003400350065006600380039003

3006600630079FE57F51E690CE0FE17D96F535A1AEB230F0380F439A9DF42962C0914

4B917F6422EFEB7F0890A10DAB41D5DB72D90BC11C74A853C906E5636EA753845265

9EC6196DB4DB8FBC75EC3B51BCB9F529E2DDB2A55E72A57BF762EE3768025E32AB

947A817075CCC5F0B8CB01514E0A4C705AAF899E1C69731B1AF26D52928EB8096B64

B8A2E18F6759E5AAB5FF4004DD34805DFFAF651C61119C13A476872641C93FC2F0E44

A922FEDFA73F89BB4BB428C7C2D68E2FBD1A9B375188840FF5039A9C69E61F71EA60

E4A2648BD3C278B9D08C2D7F1B93EF0FFA69450F438733298084B78BA550F43A8515BF

E669BD01A73158A89EAF05AAEAECF3DA99D16342D347C7133CB41D381BE675C83F3

2FD8AFD10C355DF14926204CA868FAFCFBC47A2324D8C503A0F6938620C690D4E73E5

5B0D0F808557B2282F08AE815F0A317BBFAA22972E05B4DA4E53C3B43227613971C34B

F8A386706CFD4D6F42A414821B90DFCE78FB85C65B455CE15540CF381D02B6525BD255

CA097763CDAFFFEF582EB917ECD7A2DB004DECAA0683D5DE5C7A65028D0DF21A6A58

43094ABF548FD963691EF56187CB3BF8101DD66E1655B17F5DB044AC45E85332E8F5866

FDA0CB86A25388CBB00D5367E6644DC5984C53C1C971F7588303D5A0BD2884EE6F5D617

A745B1151078F060DB606F82B8850131354B3F2312C78E86750FFEB367D036A4AD74F15

)

GO

其中 WITH VALUES 引述的內容必須填入資料行主要金鑰的值。

 

 

步驟三、建立含有加密資料行的資料表

以下列 T-SQL 指令碼建立測試資料表 Customers，並將 ID、Salary、CreditCard 及 Gender 資料行啟用 Always Encrypted 功能。

啟用加密功能時有三個重要參數必須設定，首先是 COLUMN_ENCRYPTION_KEY 參數，用來指定所要使用的資料行加密金鑰名稱；其次是 ENCRYPTION_TYPE 參數，用來指定加密類型；最後是 ALGORITHM ，填入的是加密所使用的演算法名稱，如 AEAD_AES_256_CBC_HMAC_SHA_256。

此外，若加密類型為 DETERMINISTIC ，啟用加密的資料行定序必須宣告為 *_BIN2 。

CREATE TABLE [dbo].[Customers](

        [Name] [nvarchar](5) NOT NULL,                                                                              

        [ID] [varchar](10) COLLATE Chinese_Taiwan_Stroke_BIN2                       

                ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK01,

        ENCRYPTION_TYPE = DETERMINISTIC ,

        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')

                NOT NULL ,                                   

        [Email] [varchar](50) NOT NULL,                                                                               

        [Tel] [varchar](20) NOT NULL,                                                                           

        [Address] [nvarchar](50) NOT NULL,                                                                        

        [Salary] [int] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK01,

        ENCRYPTION_TYPE = RANDOMIZED,      

        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,

        [CreditCard] [varchar](19) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK01,

        ENCRYPTION_TYPE = RANDOMIZED,                                                              

        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,  

        [Gender] [char](1) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK01,

        ENCRYPTION_TYPE = RANDOMIZED,                                                              

        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL

 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([ID] ASC)

)

GO

步驟四、建立存取 Always Encrypted 的應用程式

存取啟用 Always Encrypted 功能的資料表時，有些先決條件必須具備，其中包含：

  • 必須使用 .NET Framework 4.6 。
  • 連線字串必須加入【 Column Encryption Setting=Enabled 】。
  • 必須使用參數化查詢。

 

下列示範利用 Visual Studio 2015 建立使用 .NET Framework 4.6 的 ASP.NET MVC 專案。

 

對於既有的應用系統需要使用 Always Encrypted 功能，可於專案屬性視窗的應用程式頁籤，將目標 Framework 設定為 .NET Framework 4.6。

 

若您使用 Entity Framework 來存取資料庫，在建立連線時可於連接屬性視窗點選【進階】，接著在進階屬性視窗將【Column Encryption Setting】屬性設為 Enabled。

 

除此之外，使用 ADO.NET 存取資料庫，也可自行在 Web.Config 中將連線字串加入 Column Encryption Setting=Enabled。

 

或是利用 SqlConnectionStringBuilder 建立連線字串時，指定 ColumnEncryptionSetting 屬性為 SqlConnectionColumnEncryptionSetting.Enabled。

System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();

scsb.ColumnEncryptionSetting = System.Data.SqlClient.SqlConnectionColumnEncryptionSetting.Enabled;

 

由於 Entity Framework 預設就是以參數化的方式進行在存取 SQL Server ，若您是自行以 ADO.NET 的方式連接 SQL Server ，記得要以 SqlParameter 類別來傳遞參數，而避免自行串字串。

符合 Always Encrypted 功能所需的（一）使用.NET Framework 4.6 ；（二）在連線字串指定 Column Encryption Setting=Enabled ；（三）使用參數化查詢等要件，就可以開始測試 Always Encrypted 功能。

下圖示範新增客戶資料到步驟三所建立的 Customers 資料表，在按 Create 前先開啟 SQL Server Profiler 來查看增強的 ADO.NET 程式庫會如何處理傳送到 SQL Server 的查詢字串。

 

在 SQL Server Profiler 可以看到，Entity Framework 幫我們產生使用 sp_executesql 的參數化查詢來新增資料，其中寫入到設定為 Encrypted With 資料行的資料，在傳送到 SQL Server 時會以加密文字傳遞。

 

以加密資料行 ID 來查詢資料時，加密的資料會如何回傳到應用程式？我們一樣可透過 SQL Server Profiler 來查看。

 

由下圖可見，當查詢字串由應用程式傳遞到 SQL Server 的過程，包含使用到加密資料行的查詢參數，也會以加密文字來傳遞參數內容。

 

利用 SSMS 查詢 Customers 資料表，可以看到加密資料行的內容確實是經過加密的文字，您不用像過去那樣，為了加密資料行而宣告 varbinary 資料型態來存放加密後的資料，查詢時還得自己解密才能給應用程式來呈現。

 

若有需要在 SSMS 查看解密後的內容，可在【 連接到 Database Engine 】視窗，切換至 Additional Connection Parameters 頁籤，輸入【 Column Encryption Setting = Enabled 】參數後按 Connect，接著查詢啟用 Always Encrypted 的資料表就可以直接看到解密後的純文字內容。

 

 

對於加密資料行的操作，會根據所使用的加密類型而有不同的支援度，如同【 選擇加密類型 】一節所述，ENCRYPTION TYPE 為 Deterministic 的資料行，支援支援分群、篩選及連結等運算， ENCRYPTION TYPE 為 Randomized 的資料行則不支援，但相對安全性較高（如下圖所示）。

Comments

  • Anonymous
    July 14, 2015
    SQL Server 2016 搶先看，這次要帶大家認識 Temporal Tables 新功能！
    回顧系列專文：　

    SQL Server 2016 新功能搶先看 - Stretch
  • Anonymous
    July 27, 2015
    The comment has been removed
  • Anonymous
    August 07, 2015
    The comment has been removed
  • Anonymous
    September 24, 2015
    The comment has been removed
  • Anonymous
    September 29, 2015
    The comment has been removed
  • Anonymous
    October 04, 2015
    SQL Server 2016 搶先看系列第十彈 ！

    本系列文已經介紹 SQL Server 2016 在安全性、混合雲應用、效能調校及開發等方面所推出的嶄新功能或改良， SQL Server