SQL Server 2016 始终加密：易于实施，难以破解

已发表: 2022-03-11

数据是任何公司的关键资产，尤其是包含财务或健康记录等商业机密的交易数据。数据在存储它的服务器和请求它的客户端之间传输时最容易受到攻击。

确保安全的标准方法是加密服务器上的数据并使用启用 SSL 的 HTTPS 协议来保护传输中的数据。但是，如果我们可以进一步提高安全级别，通过使用 HTTPS 并通过通信线路以加密格式发送数据，只对拥有有效证书的客户端上的数据进行解密呢？这种方法将使传统的中间人 (MITM) 攻击变得更加困难。

Microsoft 对此问题的解决方案是始终加密，这是一种通过管道发送加密数据并仅由有权访问有效证书的用户解密的方法。因此，即使攻击者获得了数据，如果没有在客户端计算机上存储适当的证书，数据也将毫无用处。

本文介绍如何设置和使用 Always Encrypted，建议通过公共通信线路发送重要数据的任何人阅读，即使他们使用 SSL 保护。

始终加密背后的概念

Always Encrypted 是 Microsoft 在 SQL Server 2016 中引入的一种客户端加密技术。Always Encrypted 不仅在写入数据时自动加密数据，而且在被批准的应用程序读取数据时也会自动加密。与实时加密磁盘上的数据和日志文件但允许任何查询数据的应用程序读取数据的透明数据加密不同，Always Encrypted 要求您的客户端应用程序使用启用 Always Encrypted 的驱动程序与数据库。通过使用此驱动程序，应用程序将加密数据安全地传输到数据库，然后只能由有权访问加密密钥的应用程序解密。查询数据的任何其他应用程序也可以检索加密值，但该应用程序不能在没有加密密钥的情况下使用数据，从而使数据无用。由于这种加密体系结构，SQL Server 实例永远不会看到数据的未加密版本。

目前，唯一启用 Always Encrypted 的驱动程序是用于 SQL Server 的 .NET Framework 数据提供程序，它需要在客户端计算机上安装 .NET Framework 4.6 版，以及 JDBC 6.0 驱动程序。这可能会及时改变，但这些是截至 2017 年 4 月的官方 Always Encrypted 要求。

但我们为什么需要这项技术？应该使用 Always Encrypted 有几个很好的理由：

安全性——数据始终需要安全。现在 SSL 受到威胁，Always Encrypted 用另一层传输管道保护填补了空白。
监管支持——数据需要加密，并防止 DBA 被越来越多的行业监管（主要是金融和电信行业）窥探。这在 PII 标准（“个人身份信息”）中有所描述，该标准规定必须保护信用卡号、社会保险号、姓名和地址等信息，否则数据所有者可能会受到严厉处罚。

如何使用始终加密

使用 Always Encrypted 需要在存储加密表的数据库服务器中进行少量准备工作。准备工作分为两步：

创建列主键定义
创建列加密密钥

列主键

那么什么是列主键？

列主密钥是存储在 Windows 证书存储中的证书（在演示中用作证书存储选项），第三方硬件安全模块（用于安装、管理和使用的第三方解决方案的通用名称证书）或 Azure Key Vault（Microsoft 的基于云的证书管理解决方案）。

加密数据的应用程序使用列主密钥来保护处理数据库表列中数据加密的各种列加密密钥。使用来自 SQL Server 的证书存储（有时称为Enterprise Key Manager ）需要使用 SQL Server Enterprise Edition。

在本文中，我们描述了存储在 Windows 操作系统的 Microsoft 证书存储中的自签名证书的使用。虽然这种方法不是最佳配置，但它展示了始终加密的概念——但还需要说明的是，这种方法对于生产环境是不可接受的，在生产环境中，证书管理必须使用单独的、安全的用户帐户进行，最好是，在不同的服务器上。

您可以使用 SQL Server Management Studio (SSMS) 中的图形界面或使用 T-SQL 创建列主键定义。在 SSMS 中，连接到要在其中使用 Always Encrypted 保护数据库表的 SQL Server 2016 数据库实例。

创建和使用列主键

在对象资源管理器中，首先导航到数据库，然后导航到 Security，然后展开 Always Encrypted Keys 文件夹以显示其两个子文件夹，如下图所示：

在 SSMS 中创建密钥。

打开一个新的列主键对话框。

检查密钥在 Windows 证书存储中是否存在。

要创建列主密钥，请右键单击Column Master Keys文件夹并选择New Column Master Key 。在“ New Column Master Key对话框中，键入列主密钥的名称，指定是将密钥存储在当前用户或本地计算机的证书存储区还是 Azure Key Vault 中，然后在列表中选择一个证书。如果没有证书，或者如果您想使用新的自签名证书，请单击“ Generate Certificate ”按钮，然后单击“ OK ”。此步骤创建一个自签名证书并将其加载到运行 SSMS 的当前用户帐户的证书存储中。

注意：您应该在受信任的计算机上执行这些步骤，而不是在托管 SQL Server 实例的计算机上。 这样，即使主机计算机受到威胁，数据仍会在 SQL Server 中受到保护。

因此，在创建证书并将其配置为列主密钥后，您必须将其导出并分发到托管需要访问数据的客户端的所有计算机。如果客户端应用程序是基于 Web 的，您必须在 Web 服务器上加载证书。如果它是安装在用户计算机上的应用程序，那么您必须将证书单独部署到每个用户的计算机上。

您可以在以下 URL 找到有关为您的操作系统导出和导入证书的适用说明：

导出证书
- Windows 7 和 Windows Server 2008 R2
- Windows 8 和 Windows Server 2012
- Windows 8.1 和 Windows Server 2012 R2
- Windows 10 和 Windows Server 2016
导入证书
- Windows 7 和 Windows Server 2008 R2
- Windows 8 和 Windows Server 2012
- Windows 8.1 和 Windows Server 2012 R2
- Windows 10 和 Windows Server 2016

当您将证书导入具有加密和解密数据的应用程序的计算机上的证书存储区时，您必须将证书导入到计算机证书存储区或运行该应用程序的域帐户的证书存储区。

列加密密钥

创建列主密钥后，您就可以为特定列创建加密密钥了。 SQL Server 2016 ADO.NET 驱动程序使用列加密密钥在将数据发送到 SQL Server 之前对其进行加密，并在从 SQL Server 2016 实例检索数据后解密数据。与列主密钥一样，您可以使用 T-SQL 或 SSMS 创建列加密密钥。虽然使用 T-SQL 更容易创建列主密钥，但使用 SSMS 更容易创建列加密密钥。

要创建列加密密钥，请使用Object Explorer连接到数据库实例，导航到数据库，然后导航到Security ，然后展开Always Encrypted Keys文件夹。右键单击Column Encryption Keys ，然后选择New Column Encryption Key 。在“ New Column Encryption Key对话框中，键入新加密密钥的名称，在下拉列表中选择“ Column Master Key Definition ”，然后单击“ OK ”。您现在可以在新表的定义中使用列加密密钥。

列加密密钥创建

创建具有加密值的表

创建列主密钥定义和列加密密钥后，您可以创建一个表来保存加密值。

在执行此操作之前，您必须确定要使用的加密类型、要加密的列以及是否可以索引这些列。使用Always Encrypted功能，您可以正常定义列大小，SQL Server 根据加密设置调整列的存储大小。创建表后，您可能需要更改应用程序以使用Always Encrypted在此表上执行命令。

SQL Server 2016 加密类型

在创建包含加密值的表之前，您必须决定是否应加密每一列。

首先，该列将用于查找值还是仅返回这些值？

如果该列将用于查找，则该列必须使用确定性加密类型，它允许相等操作。但是，搜索已使用Always Encrypted功能加密的数据存在限制。 SQL Server 2016 仅支持相等操作，包括equal to 、 not equal to 、 joins （使用相等）以及使用GROUP BY子句中的值。不支持使用LIKE进行任何搜索。此外，必须在应用程序级别对使用Always Encrypted 加密的数据进行排序，因为 SQL Server 将根据加密值而不是解密值进行排序。

如果该列不用于定位记录，则该列应使用随机加密类型。这种类型的加密更安全，但它不支持搜索、连接或分组操作。

创建包含加密列的表

创建表时，您可以使用普通的CREATE TABLE语法和列定义中的一些附加参数。 CREATE TABLE语句的ENCRYPTED WITH语法中使用了三个参数。

其中第一个是ENCRYPTION_TYPE参数，它接受RANDOMIZED或DETERMINISTIC的值。第二个是ALGORITHM参数，它只接受RAEAD_AES_256_CBC_HMAC_SHA_256的值。第三个参数是COLUMN_ENCRYPTION_KEY ，它是您用来加密值的加密密钥。

 CREATE TABLE [dbo].[Customers] ( [CustomerId] [int] IDENTITY(1,1), [TaxId] [varchar](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = YOUR_COLUMN_ENCRYPTION_KEY) NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [MiddleName] [nvarchar](50) NULL, [Address1] [nvarchar](50) NULL, [Address2] [nvarchar](50) NULL, [Address3] [nvarchar](50) NULL, [City] [nvarchar](50) NULL, [PostalCode] [nvarchar](10) NULL, [State] [char](2) NULL, [BirthDate] [date] ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = YOUR_COLUMN_ENCRYPTION_KEY) NOT NULL PRIMARY KEY CLUSTERED ([CustomerId] ASC) ON [PRIMARY] ); GO

始终加密的索引

包含加密数据的列可以用作索引中的键列，前提是这些列是使用DETERMINISTIC加密类型加密的。当您尝试在这些列上创建索引时，使用RANDOMIZED加密类型加密的列会返回错误消息。使用任一加密类型加密的列可以用作非聚集索引中的INCLUDE列。

由于加密值可以是索引，因此对于使用 Always Encrypted 加密的值，除了您通常执行的索引和调整之外，不需要额外的性能调整措施。额外的网络带宽和更大的 I/O 是返回值大小增加的唯一副作用。

始终加密的性能

性能始终是一个关键因素，尤其是在这种情况下，当我们将加密开销添加到通常的数据库流量中时。测试性能的最佳站点是 SQL Performance，它测试了各种场景下的查询执行和磁盘使用情况：

SQL Server Always Encrypted 性能结果测试。

由于需要在加密和解密过程中执行 CPU 和硬盘驱动器工作，因此对使用的存储空间量和查询持续时间有明显影响。由于这会影响您的环境（CPU、RAM 和磁盘功能），您应该测试这是否会在生产中出现问题。

注意：如果您想了解有关 Microsoft SQL Server 性能优化的更多信息，请查看我们之前的一篇文章，如何调整 Microsoft SQL Server 以提高性能。

应用程序更改

您需要做什么才能在遗留代码中正确实现 Always Encrypted？

SQL Server 2016 的 Always Encrypted 功能的优点之一是，已经使用存储过程、ORM 或参数化 T-SQL 命令的应用程序不需要更改应用程序即可使用 Always Encrypted，除非已经使用了非相等操作。在应用程序中将 SQL 语句构建为动态 SQL 并直接对数据库执行这些命令的应用程序需要修改以使用其查询的参数化，这是所有应用程序的推荐安全最佳实践，然后才能利用 Always Encrypted 功能。

使 Always Encrypted 工作所需的另一项更改是在连接到数据库的应用程序的连接字符串中添加了一个连接字符串属性： Column Encryption Setting=enabled 。

将此设置添加到连接字符串后，ADO.NET 驱动程序会询问 SQL Server 正在执行的命令是否包含任何加密列，如果包含，则询问哪些列已加密。对于高负载应用程序，使用此设置可能不是最佳实践，尤其是在大部分执行命令不包含加密值的情况下。

因此，.NET Framework 在 SqlConnection 对象上提供了一个名为SqlCommandColumnEncryptionSetting的新方法，它具有三个可能的值：

Disabled — 没有 Always Encrypted 列或参数可用于使用此连接对象执行的查询。
已Enabled — 对于使用此连接对象执行的查询，存在始终加密的列和/或参数。
ResultSet — 没有 Always Encrypted 参数。但是，使用此连接对象执行查询会返回使用 Always Encrypted 加密的列。

注意：请注意，使用此方法可能需要对您的应用程序代码进行大量更改。 另一种方法是重构您的应用程序以使用不同的连接。

为获得 SQL Server 的最佳性能，明智的做法是只为使用 Always Encrypted 的查询请求有关 Always Encrypted 的元数据。这意味着在大部分查询使用 Always Encrypted 的应用程序中，应启用连接字符串，并且应用程序中的特定查询应将SqlCommandColumnEncryptionSetting指定为Disabled 。对于大多数查询不使用 Always Encrypted 值的应用程序，不应启用连接字符串，并且应根据需要为使用 Always Encrypted 列的那些查询将SqlCommandColumnEncryptionSetting设置为Enabled或ResultSet 。在大多数情况下，应用程序可以简单地启用连接字符串属性，并且在使用加密数据时应用程序性能将保持不变。

始终加密是否值得努力？

简短的回答？当然是！

它不仅有助于防止许多潜在的安全问题并为 SQL 开发人员提供附加的安全功能，而且还使您的系统更加合规，这在从电信到银行和保险的多个行业中至关重要。同样重要的是要注意，鉴于本文中提到的技术先决条件， Always Encrypted 可以通过对现有系统的最小应用程序更改来实现。

尽管您可以使用自定义解决方案来获得相同的效果，但该技术与新版本的 SQL Server 捆绑在一起，并且可以开箱即用。还需要注意的是，由于这是一项新技术，它的使用仍然存在一些限制，并且它增加了一些额外的硬件缺陷。

但是，除非它们会破坏您的环境，并且您的应用程序分布在公司的 Intranet 之外，否则几乎没有理由不使用 Always Encrypted。