关于sql的问题及解释：MSSQL-最佳实践-AlwaysEncrypted

小君 2023-01-26 19:57:42 893

关于sql的问题及解释：MSSQL-最佳实践-AlwaysEncrypted-- 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'

摘要

在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技术的详细实现步骤。

当然，您也可以使用SSMS的IDE来查看Column Master Key和Column Encryption Key，方法是：

展开需要检查的数据库 -> Security -> Always Encrypted Keys -> 展开Column Master Keys和 Column Encryption Keys。如下图所示：

关于sql的问题及解释：MSSQL-最佳实践-AlwaysEncrypted(1)

创建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的问题及解释：MSSQL-最佳实践-AlwaysEncrypted(2)

导出向导中输入私钥保护密码。

关于sql的问题及解释：MSSQL-最佳实践-AlwaysEncrypted(3)

选择存放路径。

关于sql的问题及解释：MSSQL-最佳实践-AlwaysEncrypted(4)

最后导出成功。

应用程序端测试

SQL Server服务端配置完毕后，我们需要在测试应用程序端导入证书，然后测试应用程序。

客户端导入证书

客户端导入证书方法与服务端证书导出方法入口是一致的，方法是：Control Panel –> Internet Options -> Content -> Certificates -> Import。如下截图所示：

关于sql的问题及解释：MSSQL-最佳实践-AlwaysEncrypted(5)

然后输入私钥文件加密密码，导入成功。

测试应用程序

我们使用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的问题及解释：MSSQL-最佳实践-AlwaysEncrypted(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，非常简单。为了方便大家观察，我把这个属性配置放到了连接字符串的第一个位置，如下图所示：

关于sql的问题及解释：MSSQL-最佳实践-AlwaysEncrypted(7)

运行我们的测试应用程序，展示结果如下图所示：

关于sql的问题及解释：MSSQL-最佳实践-AlwaysEncrypted(8)

从应用程序的测试结果来看，我们可以正常读、写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)

展示结果如下截图：

关于sql的问题及解释：MSSQL-最佳实践-AlwaysEncrypted(9)

然后，使用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'

如下截图：

关于sql的问题及解释：MSSQL-最佳实践-AlwaysEncrypted(10)