教程：在使用随机加密且已启用 enclave 的列上创建并使用索引

  • 项目

适用于： SQL Server 2019 (15.x) 及更高版本 - 仅限 Windows Azure SQL 数据库

本教程将介绍如何在使用含安全 enclave 的 Always Encrypted 中支持的随机加密且已启用 enclave 的列上创建和使用索引。 它将介绍：

  • 如何在有权访问保护列的密钥（列主密钥和列加密密钥）时创建索引。
  • 如何在无权访问保护列的密钥时创建索引。

先决条件

下载最新版 SQL Server Management Studio (SSMS)。

在按照本教程中的以下步骤操作之前，请确保已完成将 Always Encrypted 与安全 enclave 配合使用入门教程之一。

步骤 1：在数据库中启用加速数据库恢复 (ADR)

注意

此步骤仅适用于 SQL Server。 如果使用的是 Azure SQL 数据库，请跳过此步骤。 Azure SQL 数据库中自动启用 ADR，且不支持禁用它。

Microsoft 强烈建议，先在数据库中启用 ADR，再在使用随机加密且已启用 enclave 的列上创建首个索引。 请参阅含安全 enclave 的 Always Encrypted 中的数据库恢复部分。

  1. 关闭在上一教程中使用的任何 SSMS 实例。 关闭 SSMS 将关闭已打开的数据库连接，这是启用 ADR 所必需的。

  2. 以 sysadmin 身份打开新 SSMS 实例，并连接到 SQL Server 实例，无需为数据库连接启用 Always Encrypted。

    1. 启动 SSMS。
    2. 在“连接到服务器”对话框中，指定服务器名称，选择身份验证方法，并指定凭据。
    3. 选择“选项 >>”，并选择“Always Encrypted”选项卡。
    4. 务必取消选中“启用 Always Encrypted (列加密)”复选框。
    5. 选择“连接”。

  3. 打开新查询窗口，并执行以下语句来启用 ADR。

    ALTER DATABASE ContosoHR SET ACCELERATED_DATABASE_RECOVERY = ON;

步骤 2：在不使用角色分隔的情况下创建和测试索引

这一步将在加密列上创建和测试索引。 你将是一个同时具有以下两个角色的用户：管理数据库的 DBA 和有权访问保护数据的密钥的数据所有者。

  1. 打开新 SSMS 实例，并连接到已为数据库连接启用 Always Encrypted 的 SQL Server 实例。

    1. 启动新 SSMS 实例。
    2. 在“连接到服务器”对话框中，指定服务器名称，选择身份验证方法，并指定凭据。
    3. 选择“选项 >>”，并选择“Always Encrypted”选项卡。
    4. 选中“启用Always Encrypted (列加密) ”和“启用安全 Enclave”复选框
    5. 如果对数据库使用证明，请选择一个值“Enclave 证明协议”，该值表示证明服务 (主机保护者服务或 Microsoft Azure 证明) ，并填写 enclave 证明 URL。 否则，请选择“无”。
    6. 选择“连接” 。
    7. 如果系统提示为Always Encrypted查询启用参数化，请选择“启用”。

  2. 如果系统没有提示启用 Always Encrypted 参数化，请验证它是否已启用。

    1. 在 SSMS 的主菜单中，选择“工具”。
    2. 选择“选项...”。
    3. 导航到“查询执行”>“SQL Server”>“高级”。
    4. 务必选中“启用 Always Encrypted 参数化”。
    5. 选择“确定”。

  3. 打开查询窗口，并执行下面的语句，以加密“Employees”表中的“LastName”列。 后面的步骤将介绍如何在此列上创建并使用索引。

    ALTER TABLE [HR].[Employees]
ALTER COLUMN [LastName] [nvarchar](50) COLLATE Latin1_General_BIN2 
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO   
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

  4. 在“LastName”列上创建索引。 由于你是在已启用 Always Encrypted 的情况下连接到数据库，因此 SSMS 内部的客户端驱动程序以透明方式将 CEK1（保护“LastName”列的列加密密钥）提供给创建索引所需的 enclave。

    CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
GO

  5. 对“LastName”列运行丰富查询，并验证 SQL Server 是否在执行查询时使用索引。

    1. 在同一或新查询窗口中，确保工具栏上的“包括实时查询统计信息”按钮处于启用状态。

    2. 执行下面的查询。

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO

    3. 在“ 实时查询统计信息 ”选项卡中，观察查询是否使用索引。

步骤 3：在使用角色分隔的情况下创建索引

在这一步中，你将假装为两个不同的用户，在加密列上创建索引。 一个用户是需要创建索引但无权访问密钥的 DBA。 另一个用户是有权访问密钥的数据所有者。

  1. 使用未启用 Always Encrypted 的 SSMS 实例来执行下面的语句，以删除“LastName”列上的索引。

    DROP INDEX IX_LastName ON [HR].[Employees]; 
GO

  2. 以数据所有者（或有权访问密钥的应用程序）身份，使用 CEK1 填充 enclave 内的缓存。

    注意

    除非你在第 2 步：在不使用角色分隔的情况下创建和测试索引后重启了 SQL Server 实例，否则此为冗余步骤，因为缓存中已有 CEK1。 之所以添加它是为了展示数据所有者如何向 enclave 提供密钥（如果 enclave 中尚无的话）。

    1. 在已启用 Always Encrypted 的 SSMS 实例中，在查询窗口中执行下面的语句。 此语句将所有已启用 enclave 的列加密密钥都发送到 enclave。 有关详细信息，请参阅 sp_enclave_send_keys。

      EXEC sp_enclave_send_keys;
GO

    2. 作为上述存储过程的替代方法，可以运行对“LastName”列使用 enclave 的 DML 查询。 这只会使用 CEK1 填充 enclave。

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO

  3. 以 DBA 身份创建索引。

    1. 在未启用 Always Encrypted 的 SSMS 实例中，在查询窗口中执行下面的语句。

      CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
GO

  4. 以数据所有者身份对“LastName”列运行丰富查询，并验证 SQL Server 是否在执行查询时使用索引。

    1. 在已启用 Always Encrypted 的 SSMS 实例中，选择现有查询窗口或打开新查询窗口，并确保工具栏上的“包括实时查询统计信息”按钮处于启用状态。

    2. 执行下面的查询。

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO

    3. 在 “实时查询统计信息”中，观察查询是否使用 索引。

后续步骤

另请参阅