MSSQL-最佳实践-Always Encrypted-经管之家官网！

坛友互助群

扫码加入各岗位、行业、专业交流群

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。

http://image.uczzd.cn/9341704479461603217.jpg?id=0&from=export

创建列主密钥

其次，在AlwaysEncrypted数据库中，我们创建列主密钥（Column Master Key，简写为CMK）。

http://image.uczzd.cn/8150465060378442388.jpg?id=0&from=export

创建列加密密钥

然后，我们创建列加密密钥（Column Encryption Key，简写为CEK）。

http://image.uczzd.cn/3261244068462263840.jpg?id=0&from=export

检查CMK和CEK

接下来，我们检查下刚才创建的列主密钥和列加密密钥，方法如下：

http://image.uczzd.cn/6342433478775231554.jpg?id=0&from=export

一切正常，如下截图所示：

http://image.uczzd.cn/13834815029665899812.jpg?id=0&from=exporthttp://image.uczzd.cn/4961107855451274773.jpg?id=0&from=export

当然，您也可以使用SSMS的IDE来查看Column Master Key和Column Encryption Key，方法是：展开需要检查的数据库 -> Security -> Always Encrypted Keys -> 展开Column Master Keys和 Column Encryption Keys。如下图所示：

创建Always Encryped测试表

下一步，我们创建Always Encrypted测试表，代码如下：

http://image.uczzd.cn/3891386251660756217.jpg?id=0&from=export

在创建Always Encrypted测试表过程中，对于加密字段，我们指定了：

 加密类型：DETERMINISTIC和RANDOMIZED。

 算法：AEAD_AES_256_CBC_HMAC_SHA_256是Always Encrypted专有算法。

 加密密钥：创建的加密密钥名字。

导出服务器端证书

http://image.uczzd.cn/3870521325151470818.jpg?id=0&from=export

最后，我们将服务端的证书导出成文件，方法如下：Control Panel –> Internet Options -> Content -> Certificates -> Export。如下图所示：

http://image.uczzd.cn/14138537528609611022.jpg?id=0&from=export

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

http://image.uczzd.cn/14859333508207643817.jpg?id=0&from=export

选择存放路径。

最后导出成功。

应用程序端测试

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

客户端导入证书

http://image.uczzd.cn/12729054840724808273.jpg?id=0&from=export

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

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

测试应用程序

http://image.uczzd.cn/7488667552733093544.jpg?id=0&from=export

我们使用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，修改连接字符串即可。整个测试应用程序代码如下：

http://image.uczzd.cn/8966891007966856539.jpg?id=0&from=exporthttp://image.uczzd.cn/3745159789459247876.jpg?id=0&from=exporthttp://image.uczzd.cn/17764602603105802851.jpg?id=0&from=exporthttp://image.uczzd.cn/14474145732768965687.jpg?id=0&from=exporthttp://image.uczzd.cn/989515065735693260.jpg?id=0&from=exporthttp://image.uczzd.cn/7261241589900153415.jpg?id=0&from=exporthttp://image.uczzd.cn/7503770252003736088.jpg?id=0&from=export

我们在应用程序代码中，仅需要在连接字符串中添加Column Encryption Setting = Enabled;属性配置，即可支持SQL Server 2016新特性Always Encrypted，非常简单。为了方便大家观察，我把这个属性配置放到了连接字符串的第一个位置，如下图所示：

http://image.uczzd.cn/10839676190965346051.jpg?id=0&from=exporthttp://image.uczzd.cn/4983272158542740855.jpg?id=0&from=export

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

从应用程序的测试结果来看，我们可以正常读、写Always Encrypted测试表，应用程序工作良好。那么，假如我们抛开应用程序使用其它方式能否读写该测试表，看到又是什么样的数据结果呢？

测试SSMS

假设，我们使用SSMS做为测试工具。首先读取Always Encrypted测试表中的数据：

http://image.uczzd.cn/1702671427350813321.jpg?id=0&from=export

展示结果如下截图：

http://image.uczzd.cn/1343745462223983354.jpg?id=0&from=export

然后，使用SSMS直接往测试表中插入数据：

http://image.uczzd.cn/5857855126167372750.jpg?id=0&from=export

会报告如下错误：

http://image.uczzd.cn/999227103520149447.jpg?id=0&from=export

如下截图：

http://image.uczzd.cn/8810740324353728943.jpg?id=0&from=export

由此可见，我们无法使用测试应用程序以外的方法读取和操作Always Encrypted表的明文数据。

测试结果分析

从应用程序读写测试和使用SSMS直接读写Always Encrypted表的测试结果来看，用户可以使用前者正常读写测试表，工作良好；而后者无法读取测试表明文，仅可查看测试表的加密后的密文数据，加之写入操作直接报错。

测试应用源代码

https://rdshelpattachments.oss-cn-beijing.aliyuncs.com/AlwaysEncrypted/AlwaysEncryptedExample.rar

最后总结

本期月报，我们分享了SQL Server 2016新特性Always Encrypted的原理及实现方法，以此来保证存储在云端的数据库中数据永远保持加密状态，即便是云服务提供商也看不到数据库中的明文数据，以此来保证客户云数据库的数据绝对安全，解决了云数据库场景中最重要的用户对云服务提供商信任问题。

MSSQL-最佳实践-Always Encrypted
http://news.uc.cn/a_1267358991742390609/?from=UCPC

扫码或添加微信号：坛友素质互助

「经管之家」APP：经管人学习、答疑、交友，就上经管之家！
免流量费下载资料----在经管之家app可以下载论坛上的所有资源，并且不额外收取下载高峰期的论坛币。
涵盖所有经管领域的优秀内容----覆盖经济、管理、金融投资、计量统计、数据分析、国贸、财会等专业的学习宝库，各类资料应有尽有。
来自五湖四海的经管达人----已经有上千万的经管人来到这里，你可以找到任何学科方向、有共同话题的朋友。
经管之家（原人大经济论坛），跨越高校的围墙，带你走进经管知识的新世界。
扫描下方二维码下载并注册APP