MSSQL-很棒的实践-Always Encrypted

发布时间：2023-03-13 11:16:59 所属栏目：MsSql教程来源：

导读：我们首先创建了测试数据库AlwaysEncrypted。

--Step 1 - Create MSsql sample database
USE master
GO
IF DB_ID('AlwaysEncrypted') IS NULL
CREATE DATABASE [AlwaysEncrypted];
GO
-- Not 10

我们首先创建了测试数据库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）。

检查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() 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表的明文数据。

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!