1. 首页
  2. 博客
  3. SQL Server 中的增强功能

SQL Server 中的增强功能

SQL Server 后来的几个主要版本，这个功能是如何演变的，今天是否更容易使用？

解决方案

客户端工具中引入了有关始终加密的一些更有用的增强功能。例如，现在使用 SQL Server Management Studio (SSMS) 加密列要容易得多。

启用始终加密

让我们从创建一个简单的表开始：

CREATE DATABASE AlwaysEncrypted;
GO
USE AlwaysEncrypted;
GO
CREATE TABLE dbo.Employees
(
  EmployeeID  int IDENTITY(1,1) NOT NULL,
  FirstName   nvarchar(64)      NOT NULL,
  LastName    nvarchar(64)      NOT NULL,
  BirthDate   date              NOT NULL,
  Salary      int               NOT NULL,
  NationalID  char(9)           NOT NULL,
  CONSTRAINT  PK_Employees PRIMARY KEY (EmployeeID)
);

我们将插入一行：

INSERT dbo.Employees(FirstName, LastName, BirthDate, Salary, NationalID)
  VALUES(N'A', N'B', '19770101', 50000, '992993994');

然后我们可以使用 SSMS 中的向导来应用 Always Encrypted。右键单击表并选择“加密列...”

表上下文菜单“加密列...”

首先，您将看到一个可供选择的列列表，包括是否使用确定性加密或随机加密以及要使用的列加密密钥。列加密密钥用于加密实际数据。理论上，您可以对不同的列使用不同的键，但为了简单起见，我们将在此处使用单个键：

始终加密向导中的列选择屏幕

需要注意的是，字符串列可能需要转换为与加密兼容的排序规则，例如 Latin1_General_BIN2。

接下来，系统会要求您提供主加密密钥，用于保护列密钥。主密钥需要存储在受信任的密钥存储中。在上一篇技巧中，我使用了本地证书存储，这使得从同一台计算机上的 SSMS 连接时一切正常。你需要使用中央证书存储或 Azure Key Vault进行远程访问。在本例中，我们将使用 Azure Key Vault。您将需要一个有效的 Azure 订阅、一个 Key Vault 以及一个具有 Key Vault 加密官角色（如果使用基于角色的访问策略）或具有 get 、 list 、 create 、unwrap key 、 wrapper key的用户或 身份、验证和 签署权限（如果使用权限访问策略模型）。

Always Encrypted 向导的主密钥配置屏幕

如果您要对现有表中的列应用加密，则会出现有关在维护时段执行此工作的警告，以防万一：

当加密/解密正在进行时，不应在表上执行写操作。如果执行写入操作，则可能会丢失数据。建议在计划的维护时段内安排此加密/解密操作。

否则，您可以单击“下一步”和“完成”，加密过程应该在我们创建的示例表上快速完成。

查询数据

创建表后，您可以在 SSMS 中针对数据库启动新的查询窗口并运行简单的 SELECT 查询。您将看到加密的列返回二进制值：

查询结果中的加密输出

默认情况下，SSMS 未设置为支持始终加密。您可以使用自定义连接字符串属性覆盖旧版本中的行为。但现在，通过“选项”面板中用于连接的简单复选框，一切变得更加容易。您可以右键单击查询窗口的文本编辑器部分，选择“连接”>“更改连接”，按“选项”，然后在“始终加密”选项卡上选中“启用始终加密”框：

“连接”对话框的“始终加密”选项卡

请记住，这可能会更改您与主服务器的连接，因此请记住发出另一个 USE 命令或在“连接属性”选项卡上更改数据库。

当您再次运行相同的查询时，系统会提示您连接到 Azure，只有当您像我一样拥有许多帐户和/或订阅时，这才会变得复杂。如果连接成功，您应该在输出中看到解密的值：

查询结果中的解密值

当您在任何后续 SSMS 会话中首次查询任何此类数据时，系统会收到类似的提示。

对于确定性列，我们可以执行相等搜索。例如：

DECLARE @BirthDate date = '19780101';
SELECT * FROM dbo.Employees WHERE BirthDate = @BirthDate;

针对确定性列的相等搜索结果

蓝色波浪线显示一个工具提示，其中包含以下文本，解释如何参数化变量：@BirthDate 将转换为具有以下属性的 Microsoft.Data.SqlClient.SqlParameter 对象：SqlDbType = Date、Size = 0、Precision = 0 ，比例 = 0，SqlValue = 1/1/1978 12:00:00 AM

但我们无法执行范围或不等式搜索：

DECLARE @BirthDate date = '19780101';
SELECT * FROM dbo.Employees WHERE BirthDate >= @BirthDate;

结果：

Msg 33277, Level 16, State 2
Encryption scheme mismatch for columns/variables '@BirthDate', 'BirthDate'. The encryption scheme for the columns/variables is (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'AlwaysEncrypted') and the expression near line '5' expects it to be RANDOMIZED, a BIN2 collation for string data types, and an enclave-enabled column encryption key, or PLAINTEXT.

对于随机列，我们甚至无法执行相等搜索：

DECLARE @Salary int = 60000;
SELECT * FROM dbo.Employees WHERE Salary = @Salary;

我们在这里得到类似的结果：

Msg 33277, Level 16, State 2
Encryption scheme mismatch for columns/variables 'Salary', '@Salary'. The encryption scheme for the columns/variables is (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'AlwaysEncrypted') and the expression near line '5' expects it to be DETERMINISTIC, or RANDOMIZED, a BIN2 collation for string data types, and an enclave-enabled column encryption key, or PLAINTEXT.

请记住，如果您的字符串列在加密之前使用不区分大小写的排序规则，则相等搜索现在将区分大小写。

操纵数据

执行包含 Always Encrypted 列的插入有点棘手。如果我们尝试与前一个类似的插入语句：

INSERT dbo.Employees(FirstName, LastName, BirthDate, Salary, NationalID)
  VALUES(N'C', N'D', '19780101', 60000, '892994994');

Management Studio 不知道如何处理纯文本值，会产生如下消息：

Msg 206, Level 16, State 2
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 = 'CEK_Auto1', column_encryption_key_database_name = 'AlwaysEncrypted') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

相反，我们需要使用具有匹配类型的参数或局部变量。例如：

DECLARE @BirthDate  date    = '19780101',
        @Salary     int     =  60000,
        @NationalID char(9) = '892994994';
INSERT dbo.Employees(FirstName, LastName, BirthDate, Salary, NationalID)
  VALUES(N'C', N'D', @BirthDate, @Salary, @NationalID);xxxxxxxxxx DECLARE @BirthDate  date    = '19780101',        @Salary     int     =  60000,        @NationalID char(9) = '892994994';INSERT dbo.Employees(FirstName, LastName, BirthDate, Salary, NationalID)  VALUES(N'C', N'D', @BirthDate, @Salary, @NationalID);声明 @BirthDate 日期 = '19780101',        @Salary int = 60000，        @NationalID char(9) = '892994994';INSERT dbo.Employees（名字、姓氏、出生日期、工资、国民 ID）  VALUES(N'C', N'D', @BirthDate, @Salary, @NationalID);

要运行此语句，我们需要启用 SSMS 来正确参数化变量。如果尚未启用此选项，系统将提示我们这样做（使用 SQL Server Management Studio 配置始终加密）：

提示启用局部变量参数化

启用后，我们现在可以按预期插入数据。如果我们在同一会话中查询该表，我们会看到：

添加第二行后解密的值

如果我们转到未启用“始终加密”的会话，我们将再次看到加密值：

添加第二行后解密的值

其他考虑因素

加密数据并不是最容易使用的，我什至没有了解如何 从应用程序管理这些数据，该应用程序还必须通过 Azure 身份验证才能使用存储在其中的密钥，特别是如果应用程序和/或数据库不在 Azure 中。因此，请务必权衡始终加密是否是保护数据的正确机制。

考虑诸如出生日期之类的信息（通常用于不敏感的功能，例如每月生日公告）是否需要加密或以其他方式（例如列权限或动态数据屏蔽）进行保护。也许您可以使用权限锁定列，以更易于访问的方式单独公开月份（例如，计算列）。

您还可以考虑 SQL Server 2019 中的新功能 secure enclaves是否可以通过使加密列功能更加丰富且易于访问来提供帮助。Always Encrypted 列的许多限制（例如无法匹配模式或排序）是因为解密发生在客户端。安全飞地增强功能提供了一种在服务器上的安全内存区域中执行某些操作的方法，消除了许多使采用变得更加困难的限制。 。

原文链接：codingdict.net

热门标签