title: MSSQL - 最佳實踐 - Always Encrypted
author: 風移
摘要
在SQL Server安全系列專題月報分享中，往期我們已經陸續分享了：
如何使用對稱密鑰實作SQL Server列加密技術、
使用非對稱密鑰實作SQL Server列加密 使用混合密鑰實作SQL Server列加密技術 列加密技術帶來的查詢性能問題以及相應解決方案 行級别安全解決方案 SQL Server 2016 dynamic data masking實作隐私資料列打碼技術和
使用證書做資料庫備份加密這七篇文章，直接點選以上文章前往檢視詳情。本期月報我們分享SQL Server 2016新特性Always Encrypted技術。
問題引入
在雲計算大行其道的如今，有沒有一種方法保證存儲在雲端的資料庫中資料永遠保持加密狀态，即便是雲服務提供商也看不到資料庫中的明文資料，以此來保證客戶雲資料庫中資料的絕對安全呢？答案是肯定的，就是我們今天将要談到的SQL Server 2016引入的始終加密技術(Always Encrypted)。
使用SQL Server Always Encrypted，始終保持資料處于加密狀态，隻有調用SQL Server的應用才能讀寫和操作加密資料，如此您可以避免資料庫或者作業系統管理者接觸到客戶應用程式敏感資料。SQL Server 2016 Always Encrypted通過驗證加密密鑰來實作了對用戶端應用的控制，該加密密鑰永遠不會通過網絡傳遞給遠端的SQL Server服務端。是以，最大限度保證了雲資料庫客戶資料安全，即使是雲服務提供商也無法準确獲知使用者資料明文。
具體實作
SQL Server 2016引入的新特性Always Encrypted讓使用者資料在應用端加密、解密，是以在雲端始終處于加密狀态存儲和讀寫，最大限制保證使用者資料安全，徹底解決客戶對雲服務提供商的信任問題。以下是SQL Server 2016 Always Encrypted技術的詳細實作步驟。
建立測試資料庫
為了測試友善，我們首先建立了測試資料庫AlwaysEncrypted。
--Step 1 - Create MSSQL sample database
USE master
GO
IF DB_ID('AlwaysEncrypted') IS NULL
CREATE DATABASE [AlwaysEncrypted];
GO
-- Not 100% require, but option adviced.
ALTER DATABASE [AlwaysEncrypted] COLLATE Latin1_General_BIN2; 建立列主密鑰
其次，在AlwaysEncrypted資料庫中，我們建立列主密鑰（Column Master Key，簡寫為CMK）。
-- Step 2 - Create a column master key
USE [AlwaysEncrypted]
GO
CREATE COLUMN MASTER KEY [AE_ColumnMasterKey]
WITH
(
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/C3C1AFCDA7F2486A9BBB16232A052A6A1431ACB0'
)
GO 建立列加密密鑰
然後，我們建立列加密密鑰（Column Encryption Key，簡寫為CEK）。
-- Step 3 - Create a column encryption key
USE [AlwaysEncrypted]
GO
CREATE COLUMN ENCRYPTION KEY [AE_ColumnEncryptionKey]
WITH VALUES
(
COLUMN_MASTER_KEY = [AE_ColumnMasterKey],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006300330063003100610066006300640061003700660032003400380036006100390062006200620031003600320033003200610030003500320061003600610031003400330031006100630062003000956D4610BE7DAEFC2E1B08D557BFF9E33FF23896BD76BB33A84560F5E4BE174D8798D86CC963BA57867404945B166D756CE87AFC9EB29EEB9E26B08115724C1724DCD449D0D14D4D5C4601A631899C733C7646EB845A816A17DB1D400B7C341C2EF5838731583B1C51A457E14692532FD7059B7F0AFF3D89BDF86FB3BB18880F6B49CD2EA6F346BA5EE130FCFCA69A71523722F824CD14B3CE2C29C9E46074F2FE36265450A0424F390C2BC32B724FAB674E2B58DB16347B842597AFEBE983C7F4F51BCC088292219BD6F6E1F092BD77C5AD80331770E0B0B8BF6428D2719560AF56780ECE8805F7B425818F31CF54C84FF11114DB693B6CB7D499B1490B8E155749329C9A7AF4417E2A17D0EACA92CBB59A4EE314C54BCD83F80E8D6363F9CF66D8608772DCEB5D3FF4C8A131E21984C2370AB0788E38CB330C1D6190A7513BE1179432705C0C38B9430FC7A8D10BBDBDBA4AC7A7E24D2E257A0B8B79AC2B6D7E0C2F2056F58579E96009C488F2C1C691B3DC9E2F5D538D2E96BB4E8DB280F3C0461B18ADE30A3A5C5279C6861E3109C8EEFE4BC8192338137BBF7D5BFD64A689689B40B5E1FB7A157D06F6674C807515255C0F124ED866D9C0E5294759FECFF37AEEA672EF5C3A7649CAA8B55288526DF6EF8EB2D7485601E9A72CFA53D046E200320BAAD32AD559C644018964058BBE9BE5A2BAFB28E2FF7B37C85B49680F
)
GO 檢查CMK和CEK
接下來，我們檢查下剛才建立的列主密鑰和列加密密鑰，方法如下：
-- Step 4 - CMK & CEK Checking
select * from sys.column_master_keys
select * from sys.column_encryption_keys
select * from sys.column_encryption_key_values 一切正常，如下截圖所示：
當然，您也可以使用SSMS的IDE來檢視Column Master Key和Column Encryption Key，方法是：
展開需要檢查的資料庫 -> Security -> Always Encrypted Keys -> 展開Column Master Keys和 Column Encryption Keys。如下圖所示：
建立Always Encryped測試表
下一步，我們建立Always Encrypted測試表，代碼如下：
-- Step 5 - Create a table with an encrypted column
USE [AlwaysEncrypted]
GO
IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULL
DROP TABLE dbo.CustomerInfo
GO
CREATE TABLE dbo.CustomerInfo
(
CustomerId INT IDENTITY(10000,1) NOT NULL PRIMARY KEY,
CustomerName NVARCHAR(100) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
) NOT NULL,
CustomerPhone NVARCHAR(11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
) NOT NULL
)
;
GO 在建立Always Encrypted測試表過程中，對于加密字段，我們指定了：
加密類型：DETERMINISTIC和RANDOMIZED。
算法：AEAD_AES_256_CBC_HMAC_SHA_256是Always Encrypted專有算法。
加密密鑰：建立的加密密鑰名字。
導出伺服器端證書
最後，我們将服務端的證書導出成檔案，方法如下：
Control Panel –> Internet Options -> Content -> Certificates -> Export。如下圖所示：
導出向導中輸入私鑰保護密碼。
選擇存放路徑。
最後導出成功。
應用程式端測試
SQL Server服務端配置完畢後，我們需要在測試應用程式端導入證書，然後測試應用程式。
用戶端導入證書
用戶端導入證書方法與服務端證書導出方法入口是一緻的，方法是：Control Panel –> Internet Options -> Content -> Certificates -> Import。如下截圖所示：
然後輸入私鑰檔案加密密碼，導入成功。
測試應用程式
我們使用VS建立一個C#的Console Application做為測試應用程式，使用NuGet Package功能安裝Dapper，做為我們SQL Server資料庫操作的工具。
注意：僅.NET 4.6及以上版本支援Always Encrypted特性的SQL Server driver，是以，請確定您的項目Target framework至少是.NET 4.6版本，方法如下：右鍵點選您的項目 -> Properties -> 在Application中，切換你的Target framework為.NET Framework 4.6。
為了簡單友善，我們直接在SQL Server服務端測試應用程式，是以您看到的連接配接字元串是連接配接本地SQL Server服務。如果您需要測試遠端SQL Server，修改連接配接字元串即可。整個測試應用程式代碼如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using System.Data.SqlClient;
namespace AlwaysEncryptedExample
{
public class AlwaysEncrypted
{
public static readonly string CONN_STRING = "Column Encryption Setting = Enabled;Server=.,1433;Initial Catalog=AlwaysEncrypted;Trusted_Connection=Yes;MultipleActiveResultSets=True;";
public static void Main(string[] args)
{
List<Customer> Customers = QueryCustomerList<Customer>(@"SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK)");
// there is no record
if(Customers.Count == 0)
{
Console.WriteLine("************There is no record.************");
string execSql = @"INSERT INTO dbo.CustomerInfo VALUES (@customerName, @cellPhone);";
Console.WriteLine("************Insert some records.************");
DynamicParameters dp = new DynamicParameters();
dp.Add("@customerName", "CustomerA", dbType: DbType.String, direction: ParameterDirection.Input, size: 100);
dp.Add("@cellPhone", "13402871524", dbType: DbType.String, direction: ParameterDirection.Input, size: 11);
DoExecuteSql(execSql, dp);
Console.WriteLine("************re-generate records.************");
Customers = QueryCustomerList<Customer>(@"SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK)");
}
else
{
Console.WriteLine("************There are a couple of records.************");
}
foreach(Customer cus in Customers)
{
Console.WriteLine(string.Format("Customer name is {0} and cell phone is {1}.", cus.CustomerName, cus.CustomerPhone));
}
Console.ReadKey();
}
public static List<T> QueryCustomerList<T>(string queryText)
{
// input variable checking
if (queryText == null || queryText == "")
{
return new List<T>();
}
try
{
using (IDbConnection dbConn = new SqlConnection(CONN_STRING))
{
// if connection is closed, open it
if (dbConn.State == ConnectionState.Closed)
{
dbConn.Open();
}
// return the query result data set to list.
return dbConn.Query<T>(queryText, commandTimeout: 120).ToList();
}
}
catch (Exception ex)
{
Console.WriteLine("Failed to execute {0} with error message : {1}, StackTrace: {2}.", queryText, ex.Message, ex.StackTrace);
// return empty list
return new List<T>();
}
}
public static bool DoExecuteSql(String execSql, object parms)
{
bool rt = false;
// input parameters checking
if (string.IsNullOrEmpty(execSql))
{
return rt;
}
if (!string.IsNullOrEmpty(CONN_STRING))
{
// try to add event file target
try
{
using (IDbConnection dbConn = new SqlConnection(CONN_STRING))
{
// if connection is closed, open it
if (dbConn.State == ConnectionState.Closed)
{
dbConn.Open();
}
var affectedRows = dbConn.Execute(execSql, parms);
rt = (affectedRows > 0);
}
}
catch (Exception ex)
{
Console.WriteLine("Failed to execute {0} with error message : {1}, StackTrace: {2}.", execSql, ex.Message, ex.StackTrace);
}
}
return rt;
}
public class Customer
{
private int customerId;
private string customerName;
private string customerPhone;
public Customer(int customerId, string customerName, string customerPhone)
{
this.customerId = customerId;
this.customerName = customerName;
this.customerPhone = customerPhone;
}
public int CustomerId
{
get
{
return customerId;
}
set
{
customerId = value;
}
}
public string CustomerName
{
get
{
return customerName;
}
set
{
customerName = value;
}
}
public string CustomerPhone
{
get
{
return customerPhone;
}
set
{
customerPhone = value;
}
}
}
}
} 我們在應用程式代碼中，僅需要在連接配接字元串中添加Column Encryption Setting = Enabled;屬性配置，即可支援SQL Server 2016新特性Always Encrypted，非常簡單。為了友善大家觀察，我把這個屬性配置放到了連接配接字元串的第一個位置，如下圖所示：
運作我們的測試應用程式，展示結果如下圖所示：
從應用程式的測試結果來看，我們可以正常讀、寫Always Encrypted測試表，應用程式工作良好。那麼，假如我們抛開應用程式使用其它方式能否讀寫該測試表，看到又是什麼樣的資料結果呢？
測試SSMS
假設，我們使用SSMS做為測試工具。首先讀取Always Encrypted測試表中的資料：
-- try to read Always Encrypted table and it'll show us encrypted data instead of the plaintext.
USE [AlwaysEncrypted]
GO
SELECT * FROM dbo.CustomerInfo WITH(NOLOCK) 展示結果如下截圖：
然後，使用SSMS直接往測試表中插入資料：
-- try to insert records to encrypted table, will be fail.
USE [AlwaysEncrypted]
GO
INSERT INTO dbo.CustomerInfo
VALUES ('CustomerA','13402872514'),('CustomerB','13880674722')
GO 會報告如下錯誤：
Msg 206, Level 16, State 2, Line 74
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'AE_ColumnEncryptionKey', column_encryption_key_database_name = 'AlwaysEncrypted') collation_name = 'Chinese_PRC_CI_AS' 如下截圖：
由此可見，我們無法使用測試應用程式以外的方法讀取和操作Always Encrypted表的明文資料。
測試結果分析
從應用程式讀寫測試和使用SSMS直接讀寫Always Encrypted表的測試結果來看，使用者可以使用前者正常讀寫測試表，工作良好；而後者無法讀取測試表明文，僅可檢視測試表的加密後的密文資料，加之寫入操作直接報錯。
測試應用源代碼
如果您需要本文的測試應用程式源代碼，請
點選下載下傳。
最後總結
本期月報，我們分享了SQL Server 2016新特性Always Encrypted的原理及實作方法，以此來保證存儲在雲端的資料庫中資料永遠保持加密狀态，即便是雲服務提供商也看不到資料庫中的明文資料，以此來保證客戶雲資料庫的資料絕對安全，解決了雲資料庫場景中最重要的使用者對雲服務提供商信任問題。