深圳做积分商城网站公司,it外包服务网,wordpress主动推送代码,打码网站建设摘要
在SQL Server安全系列专题月报分享中#xff0c;往期我们已经陆续分享了#xff1a;如何使用对称密钥实现SQL Server列加密技术、使用非对称密钥实现SQL Server列加密、使用混合密钥实现SQL Server列加密技术、列加密技术带来的查询性能问题以及相应解决方案、行级别安…摘要
在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 NULLCREATE 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 NMSSQL_CERTIFICATE_STORE,KEY_PATH NCurrentUser/My/C3C1AFCDA7F2486A9BBB16232A052A6A1431ACB0
)GO
创建列加密密钥
然后我们创建列加密密钥Column Encryption Key简写为CEK。
-- Step 3 - Create a column encryption key
USE [AlwaysEncrypted]
GOCREATE 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 columnUSE [AlwaysEncrypted]
GO
IF OBJECT_ID(dbo.CustomerInfo, U) IS NOT NULLDROP 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_BIN2ENCRYPTED 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 CatalogAlwaysEncrypted;Trusted_ConnectionYes;MultipleActiveResultSetsTrue;;public static void Main(string[] args){ListCustomer Customers QueryCustomerListCustomer(SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK));// there is no recordif(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 QueryCustomerListCustomer(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 ListT QueryCustomerListT(string queryText){// input variable checkingif (queryText null || queryText ){return new ListT();}try{using (IDbConnection dbConn new SqlConnection(CONN_STRING)){// if connection is closed, open itif (dbConn.State ConnectionState.Closed){dbConn.Open();}// return the query result data set to list.return dbConn.QueryT(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 listreturn new ListT();}}public static bool DoExecuteSql(String execSql, object parms){bool rt false;// input parameters checkingif (string.IsNullOrEmpty(execSql)){return rt;}if (!string.IsNullOrEmpty(CONN_STRING)){// try to add event file targettry{using (IDbConnection dbConn new SqlConnection(CONN_STRING)){// if connection is closed, open itif (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 itll 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的原理及实现方法以此来保证存储在云端的数据库中数据永远保持加密状态即便是云服务提供商也看不到数据库中的明文数据以此来保证客户云数据库的数据绝对安全解决了云数据库场景中最重要的用户对云服务提供商信任问题。
原文链接 本文为云栖社区原创内容未经允许不得转载。
