魯斯の隨手記

makecert -r -pe -n "CN=AlwaysEncryptedMasterKey" -b 01/01/2016 -e 01/01/2025 -sky exchange -ss my

補充一下always Encrypted的基本觀念和後續：

1. 資料庫中的資料始終為加密狀態，資料的加解密會於AP端的ADO.NET 應用程式端執行，資料庫只接收或儲存AP端加密後的資料

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

3. 基於安全性原則記得刪除DB Server的憑證 (以防止可透過「Column Encryption Setting = Enabled」即可看到解密後資料。)

開始前我先將TechNet上需要的一些條件列出：

環境面：

1. 必須使用 .NET Framework 4.6 (但我用4.5也可以)

2. 一張可用憑證(廢話)

3. SQL 2016(含)以上版本

4. SSMS 18版 (我是直接使用18)，說明一下，13 版的無法於查詢時進行參數查詢

資料面：

1. COLUMN_ENCRYPTION_KEY 指定使用金錀

2. ENCRYPTION_TYPE 加密類型：① DETERMINISTIC  或  ②RANDOMIZED 如果使用①的話啟用加密的資料行定序必須宣告為 *_BIN2

3. ALGORITHM 加密所使用的演算法名稱 ：AEAD_AES_256_CBC_HMAC_SHA_256

動作開始：

1. 建立資料庫

USE master

GO

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

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

CREATE DATABASE AlwaysEncryptedDB

GO

2. 針對資料行主要金錀選取憑證 (CMK)

USE [AlwaysEncryptedDB]
GO

CREATE COLUMN MASTER KEY DEFINITION [CMK]
WITH
(

        KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',

        --金鑰路徑
        KEY_PATH = N'CurrentUser/My/5B05854FBE33D54C74532E1379558CC45EF893FC'

)
GO

3. 針對資料行加密金錀選取憑證 (CEK)

USE [AlwaysEncryptedDB]

GO

CREATE COLUMN ENCRYPTION KEY [CEK]
WITH VALUES
(

        COLUMN MASTER KEY DEFINITION = [CMK],
        ALGORITHM = 'RSA_OAEP',
        ENCRYPTED_VALUE = 一整串的太長就不貼了
)
GO

PS.以上動作也可以用右鍵選擇精靈的方式進行增加

4. 建立一資料表含加密資料行

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,                                                                                                                                                

        [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,

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

)

GO

5. 建立一AP來存取此加密資料庫 (記得此AP要匯入憑證)，此段用一般方式存取即可，並於Web.config連線字串加上 Column Encryption Setting=Enabled。

6. 如果AP SERVER中的程式已新增資料的話會於DB SERVER 的MSSQL看到資料行已被加密，如要看到實際內容則必須於登入SQL時>其他連線參數>輸入「Column Encryption Setting = Enabled」即可看到解密後資料。

7. 如欲在MSSQL中進行新增/修改，則可參照以下，不同以往可直接使用TSQL一行指令新增，需要以參數的方式新增，需特別留意的是SSMS13是無法用此方式新增的，並且如果使用SSMS18 (也許13後就可以我是直接用18)，要開啟底下參數 ：查詢>查詢選項>執行底下的進階>啟用always Encrypted的參數化才可。

--Insert Data
declare @p1 nvarchar(5) = 'Yuyin'
declare @p2 varchar(50) = 'yuyin@gmail.com'
declare @p3 nvarchar(50) = 'Louzu'
declare @p4 varchar(10) = '9521'
declare @p5 int = 10000
insert into [Customers] ([Name],Email,[Address],ID,Salary) 
values (@p1,@p2,@p3,@p4,@p5)


--Update Data
declare @p6 int = 20000
Update [Customers]
set Salary=@p6
where Name='Yuyin'

--Query
select * from [Customers]

--檢視金錀
select * from sys.column_master_keys
select * from sys.column_encryption_keys
select * from sys.column_encryption_key_values

資料參考來源：

https://blogs.technet.microsoft.com/technet_taiwan/2015/07/06/sql-server-2016-always-encrypted/