因應資安要求，提升資料安全性，剛好公司可能會需要，因此做點小功課
測試環境：
SQL：2016
SSMS：13(✖) & 18
先於vs的命令提示字元產生一憑證於個人憑證區如下：
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/
