SQL Server 2016在安全性上做了相當多的改良，其中最嚴謹的就屬Always Encrypted。
話說嚴謹自然在配置上就有限制，實作上可是相當嚴苛的。
無論如何，使用這個功能時，Key可別丟失了！
SQL Server 2016終於在資料安全性上加強了許多地方, 讓過去一直未能實現的資料加密或是資料遮罩的功能得以實現. 而這次提出的針對敏感資料加密的功能之一, 就是針對敏感性資料加密. 不過在測試後發現, 目前能夠使用的條件算是嚴苛的, 至少目前知道的, 要符合下列四個條件:
* 需要使用 .NET Framework 4.6 版本 (應該是指之後的版本).
* 連接字串要加入 Column Encryption Setting=enabled.
* 需要使用參數化查詢來下語法.
* 當然必須完成建立相關的設定, 也就是下列要提到的COLUMN MASTER KEY DEFINITION.
這有段針對Always Encrypted的影片 (Channel 9)
http://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-Always-Encrypted
關於Always Encrypted的官方文件
Always Encrypted (Database Engine)
https://msdn.microsoft.com/en-us/library/mt163865(v=sql.130).aspx
要實現Always Encrypted, 那麼要做的設定有三個項目:
* 建立主要金鑰定對 (Column Master Key Definition)
* 設定資料行加密金鑰 (Column Encrypted Key)
* 建立資料表中之欄位使用加密金鑰
建立測試資料庫: AlwaysEncrypted
資料庫檔案位置: 預設路徑
**************************************************/
CREATE DATABASE AlwaysEncrypted
ON PRIMARY
( NAME = N'AlwaysEncrypted_data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AlwaysEncrypted_data.mdf')
LOG ON
( NAME = N'AlwaysEncrypted_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AlwaysEncrypted_log.ldf');
GO
再來就是建立一個資料行主要金鑰定義 (Column Master Key Definition).
1. 指向資料庫AlwaysEncrypted下的 [安全性] / [一律加密的金鑰] / [資料行主要金鑰定義] 點右鍵, 再選擇 [新增資料行主要金鑰定義].
2. 開啟新增資料行主要金鑰定義. (老實說, 介面簡單到不知道怎麼玩)
這時候試著定義一個名稱, 點選 [確定] 後會發生下列錯誤, 提示需要一個 certificate.
因此要先點下方的 [產生自我簽署憑證], 然後再給個名稱, 按 [確定] 即可.
若是想用語法來設定, 是比較不容易, 因為自我簽署憑證的指紋 (??) 是亂數產生的, 除非這個憑證是手動建立的. 參考語法:
/*****想用語法就要先知道KEY PATH*****/
CREATE COLUMN MASTER KEY DEFINITION [CMK]
WITH
(
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/DF413D0484FCE9D293C54148B59A90AABD3BC946'
)
GO
偶知道偶知道, 一定又有人想問...... 啊要怎麼手動建立, 語法偶是這麼下的, 可以自己去玩玩看.
makecert -r -pe -n "CN=Always Encrypted Certificate" -b 01/01/2010 -e 01/01/2020 -eku 1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11 -len 2048 -a sha256 -sp "Microsoft Enhanced RSA and AES Cryptographic Provider" -sy 24 -sky exchange "c:\temp\cert.cer" -sv "c:\temp\cert.pvk"
pvk2pfx -pvk "c:\temp\cert.pvk" -spc "c:\temp\cert.cer" -pfx "c:\temp\cert.pfx"
3. 確認已建立好CMK後, 就針對 [資料行加密金鑰] 點右鍵, 選擇 [新增資料行加密金鑰] 來開啟設定.
4. 這裡更簡單的介面了, 不過只要已經設定好資料行主要金鑰定義, 就很容易完成, 給個名稱選取定義就好了.
5. 接下來就在AlwaysEncrypted資料庫中, 使用加密金鑰來建立欄位. 以下是範例:
GO
CREATE TABLE AfterEnableAE
(
name nvarchar(10),
sex bit,
phone varchar(20) COLLATE Chinese_Taiwan_Stroke_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK) NOT NULL,
zipcode int,
birthday datetime
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = CEK)
);
GO
有關加密的參數, 可以參考MSDN中的說明:
https://msdn.microsoft.com/en-us/library/ms174979(v=sql.130).aspx
ENCRYPTED WITH
Specifies encrypting columns by using the Always Encrypted feature.
COLUMN_ENCRYPTION_KEY = key_name
Specifies the column encryption key. For more information, see CREATE COLUMN ENCRYPTION KEY (Transact-SQL).
ENCRYPTION_TYPE = { DETERMINISTIC | RANDOMIZED }
Deterministic encryption uses a method which always generates the same encrypted value for any given plain text value. Using deterministic encryption allows searching using equality comparison, grouping, and joining tables using equality joins based on encrypted values, but can also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column. Joining two tables on columns encrypted deterministically is only possible if both columns are encrypted using the same column encryption key. Deterministic encryption must use a column collation with a binary2 sort order for character columns.
Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents equality searches, grouping, and joining on encrypted columns. Columns using randomized encryption cannot be indexed.
Use deterministic encryption for columns that will be search parameters or grouping parameters, for example a government ID number. Use randomized encryption, for data such as a credit card number, which is not grouped with other records, or used to join tables, and which is not searched for because you use other columns (such as a transaction number) to find the row which contains the encrypted column of interest.
Columns must be of a qualifying data type.
ALGORITHM
Must be 'AEAD_AES_256_CBC_HMAC_SHA_256'.
至此, 已經算是完成相關的建立動作. 之後試著在SSMS中手動寫入資料, 會遇到錯誤訊息:
(name, sex, phone, zipcode, birthday)
VALUES
(N'王小明 ', 1, '0912345678', 100, getdate()),
(N'陳小華', 0, '0234567891', 200, getdate());
/***** ERROR MESSAGE OUTPUT *****/
/*
訊息 206，層級 16，狀態 2，行 89
運算元類型衝突: datetime 與 datetime encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK', column_encryption_key_database_name = 'AlwaysEncrypted') 不相容
*/
也就是說, 這種類型的資料, 已經不能透過簡易的Query語法來寫入, 而需要透過參數化的方式來寫入資料.
依照下列文件的說明, 試著透過 .NET Framework 4.6中的C#來寫入.
http://blogs.msdn.com/b/sqlsecurity/archive/2015/06/04/getting-started-with-always-encrypted.aspx
把原本要寫入的資料透過程式寫入後, 再從SSMA去查詢, 就會看到這樣子的結果:
而依照其他人測試的結果, 透過SQL Server Profiler取資料, 一樣也是不會顯示值, 而是加密過的 (這個偶就沒有測試了)
