教程：Always Encrypted 入门

适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例

本教程介绍了如何开始使用 Always Encrypted。 它将介绍：

• 如何加密数据库中的选定列。
• 如何查询加密列。

备注

要详细了解 具有安全 Enclave 的 Always Encrypted，请参阅以下教程：

• 开始使用具有安全 enclave 的 Always Encrypted
• 教程：在 SQL Server 中开始使用具有安全 enclave 的 Always Encrypted

先决条件

对于本教程的内容，你需要：

• Azure SQL 数据库、Azure SQL 托管实例或 SQL Server 中的“空”数据库。 以下操作说明假定数据库名称为 ContosoHR。 你需要是数据库的所有者（db_owner 角色的成员）。 有关如何创建数据库的信息，请参阅 快速入门：创建单一数据库 - Azure SQL 数据库 或 在 SQL Server 中创建数据库。
• （可选但建议使用）尤其是在数据库位于 Azure 中时：Azure Key Vault 中的密钥保管库。 有关如何创建密钥保管库的信息，请参阅快速入门：使用 Azure 门户创建密钥保管库。
  • 如果密钥保管库使用访问策略权限模型，请确保你在密钥保管库中具有以下密钥权限：get、list、create、unwrap key、wrap key、verify、sign。 请参阅分配密钥保管库访问策略。
  • 如果使用 Azure 基于角色的访问控制 (RBAC) 权限模型，请确保你是密钥保管库的密钥保管库加密管理人员角色的成员。 请参阅使用 Azure 基于角色的访问控制提供对密钥保管库密钥、证书和机密的访问权限。
• 最新版本 SQL Server Management Studio (SSMS) 或最新版本 SqlServer 和 Az PowerShell 模块。 仅当使用 Azure Key Vault 时，才需要 Az PowerShell 模块。

步骤 1：创建和填充数据库架构

在此步骤中，你将创建 HR 架构和 Employees 表。 然后使用一些数据填充该表。

SSMS

1. 连接到数据库。 有关如何从 SSMS 连接到数据库的说明，请参阅 快速入门：使用 SQL Server Management Studio (SSMS) 连接和查询 Azure SQL 数据库或 Azure SQL 托管实例 或 快速入门：使用 SQL Server Management Studio (SSMS) 连接和查询 SQL Server 实例。

2. 打开 ContosoHR 数据库的新查询窗口。

3. 粘贴并执行以下语句以创建名为 Employees 的新表。

   CREATE SCHEMA [HR];
   GO
   
   CREATE TABLE [HR].[Employees]
   (
       [EmployeeID] [int] IDENTITY(1,1) NOT NULL
       , [SSN] [char](11) NOT NULL
       , [FirstName] [nvarchar](50) NOT NULL
       , [LastName] [nvarchar](50) NOT NULL
       , [Salary] [money] NOT NULL
   ) ON [PRIMARY];
   

4. 粘贴并执行以下语句，以将一些员工记录添加到 Employees 表。

   INSERT INTO [HR].[Employees]
   (
       [SSN]
       , [FirstName]
       , [LastName]
       , [Salary]
   )
   VALUES
   (
       '795-73-9838'
       , N'Catherine'
       , N'Abel'
       , $31692
   );
   
   INSERT INTO [HR].[Employees]
   (
       [SSN]
       , [FirstName]
       , [LastName]
       , [Salary]
   )
   VALUES
   (
       '990-00-6818'
       , N'Kim'
       , N'Abercrombie'
       , $55415
   );
   

PowerShell

在 PowerShell 会话中执行以下命令。 请务必使用服务器地址和对数据库有效的身份验证设置更新连接字符串。

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "Server = myServerAddress; Database = ContosoHR; ..."

# Create a new table, named Employees.
$query = @'
    CREATE SCHEMA [HR];
    GO
    
    CREATE TABLE [HR].[Employees]
    (
        [EmployeeID] [int] IDENTITY(1,1) NOT NULL
        , [SSN] [char](11) NOT NULL
        , [FirstName] [nvarchar](50) NOT NULL
        , [LastName] [nvarchar](50) NOT NULL
        , [Salary] [money] NOT NULL
    ) ON [PRIMARY];
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

# Add a few rows to the Employees table.
$query = @'
    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '795-73-9838'
        , N'Catherine'
        , N'Abel'
        , $31692
    );

    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '990-00-6818'
        , N'Kim'
        , N'Abercrombie'
        , $55415
    );
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

步骤 2：加密列

此步骤将预配 Always Encrypted 的列主密钥和列加密密钥。 然后将加密 Employees 表中的 SSN 列和 Salary 列。

SSMS

SSMS 提供了一个向导，通过设置列主密钥、列加密密钥并加密选定的列即可轻松地配置 Always Encrypted。

1. 在“对象资源管理器”中，展开“数据库”>“ContosoHR”>“表”。

2. 右键单击“Employees”表，然后选择“加密列”以打开 Always Encrypted 向导。

   

3. 在向导的“简介”页上，选择“下一步”。

4. 在“列选择”页上。

   1. 选择“SSN”和“Salary”列。 为 SSN 列选择“确定性加密”，并为“Salary”列选择“随机加密”。 确定性加密支持查询，例如对加密列进行相等比较的点查找搜索。 随机加密不支持在加密列上进行任何计算。
   2. 对于这两列的列加密密钥，保留“CEK-Auto1（新建）”。 此密钥尚不存在，将由向导生成。
   3. 选择下一步。

   

5. 在“主密钥配置”页上，配置向导将生成的新列主密钥。 首先需要选择要用于存储列主密钥的位置。 向导支持两种密钥存储库类型：

   • Azure Key Vault - 如果数据库位于 Azure 中，则建议使用此选项
   • Windows 证书存储

   通常建议使用 Azure Key Vault 选项，尤其是在数据库位于 Azure 中时。

   • 使用 Azure Key Vault：

     1. 选择“Azure 密钥保管库”。
     2. 选择“登录”并完成登录到 Azure 的步骤。
     3. 登录后，页面将显示你有权访问的订阅和密钥保管库列表。 选择包含要使用的密钥保管库的 Azure 订阅。
     4. 选择密钥保管库。
     5. 选择下一步。

     

   • 使用 Windows 证书存储：

     1. 选择“Windows 证书存储”。

     2. 保留默认选择值“当前用户” - 这将指示向导在“当前用户”存储中生成证书（新的列主密钥）。

        

     3. 选择下一步。

6. 在“就地加密设置”页上，不需要其他配置，因为数据库未启用 Enclave。 选择下一步。

7. 在“运行设置”页上，系统会询问是要继续加密还是生成稍后要执行的 PowerShell 脚本。 保留默认设置，然后选择“下一步”。

8. 在“摘要”页上，向导会通知将执行的操作。 检查所有信息是否正确，然后选择“完成”。

9. 在“结果”页上，可以监视向导操作的进度。 等待所有操作成功完成，然后选择“关闭”。

   

10. （可选）浏览向导在数据库中所做的更改。

    1. 展开“ContosoHR”>“安全性”>“Always Encrypted 密钥”，以浏览列主密钥元数据对象以及向导创建的列加密。

    2. 还可以针对包含密钥元数据的系统目录视图运行以下查询。

       SELECT * FROM sys.column_master_keys;
       SELECT * FROM sys.column_encryption_keys
       SELECT * FROM sys.column_encryption_key_values
       

    3. 在对象资源管理器中，右键单击“Employees”表，然后对于“脚本表”，选择“>创建到>新建查询编辑器窗口”。 这将打开一个新的查询窗口，其中包含 Employees 表的 CREATE TABLE 语句。 请注意 SSN 和 Salary 列的定义中显示的“ENCRYPTED WITH”子句。

    4. 还可以针对 sys.columns 运行以下查询，以检索两个加密列的列级加密元数据。

       SELECT
       [name]
       , [encryption_type]
       , [encryption_type_desc]
       , [encryption_algorithm_name]
       , [column_encryption_key_id]
       FROM sys.columns
       WHERE [encryption_type] IS NOT NULL;
       

PowerShell

1. 在密钥存储库中创建列主密钥。

   • 如果使用 Azure Key Vault，请执行以下命令，以在密钥保管库中创建非对称密钥。 请务必提供正确的订阅 ID、包含密钥保管库的资源组名称和密钥保管库名称。

     Import-Module "Az"
     Connect-AzAccount
     $subscriptionId = "<your Azure subscription ID"
     $resourceGroup = "your resource group name containing your key vault"
     $keyVaultName = "your vault name"
     $keyVaultKeyName = "your key name"
     
     # Switch to your subscription.
     Set-AzConteXt -SubscriptionId $subscriptionId
     
     # To validate the above key vault settings, get the key vault properties.
     Get-AzKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroup 
     
     # Create a key in the key vault.
     $keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
     $keyVaultKey
     

   • 如果使用 Windows 证书存储，请执行以下命令，以在“当前用户”存储中创建证书。

     $cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange
     

2. 使用 SqlServer PowerShell 模块连接到数据库。 请务必为数据库提供有效的连接字符串。

   $database = Get-SqlDatabase -ConnectionString $connectionString
   $database
   

3. 在数据库中预配列主密钥元数据对象（该对象将引用在密钥存储库中创建的物理列主密钥）。

   • 如果使用 Azure Key Vault，请执行以下命令。

     # Sign in to Azure for the SqlServer PowerShell module
     Add-SqlAzureAuthenticationContext -Interactive
     
     # Create a SqlColumnMasterKeySettings in-memory object referencing the key you've created in your key vault. 
     $cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid
     
     # Create column master key metadata object (referencing your certificate), named CMK1, in the database.
     $cmkName = "CMK1"
     New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
     

   • 如果使用 Windows 证书存储，请执行以下命令。

     # Create a SqlColumnMasterKeySettings in-memory object referencing your certificate.
     $cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint
     
     # Create column master key metadata object, named CMK1, in the database.
     $cmkName = "CMK1"
     New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
     

4. 生成列加密密钥，使用列主密钥对其加密，并在数据库中创建列加密密钥元数据。

   $cekName = "CEK1"
   New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
   

5. 加密 Employees 表中的 SSN 列和 Salary 列。 为 SSN 列选择“确定性加密”，并为“Salary”列选择“随机加密”。 确定性加密支持查询，例如对加密列进行相等比较的点查找搜索。 随机加密不支持在加密列上进行任何计算。

   # Encrypt the SSN and Salary columns 
   $ces = @()
   $ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
   $ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
   Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .
   
   

6. （可选）浏览你在数据库中所做的更改。

   • 运行以下命令，查询包含有关列主密钥元数据和所创建列加密密钥的系统目录视图。

     $query = @'
     SELECT * FROM sys.column_master_keys;
     SELECT * FROM sys.column_encryption_keys
     SELECT * FROM sys.column_encryption_key_values
     '@
     
     Invoke-SqlCmd -ConnectionString $connectionString -Query $query
     

   • 运行以下命令查询 sys.columns，以检索两个加密列的列级加密元数据。

     $query = @'
     SELECT
     [name]
     , [encryption_type]
     , [encryption_type_desc]
     , [encryption_algorithm_name]
     , [column_encryption_key_id]
     FROM sys.columns
     WHERE [encryption_type] IS NOT NULL;
     '@
     
     Invoke-SqlCmd -ConnectionString $connectionString -Query $query
     

步骤 3：查询加密列

SSMS

1. 连接到数据库，并在连接中禁用 Always Encrypted。

   1. 打开一个新的查询窗口。
   2. 在查询窗口中右键单击任意位置，然后选择“连接”和“更改连接”>。 这将打开“连接到数据库引擎”对话框。
   3. 选择“选项”。<< 这将显示“连接到数据库引擎”对话框中的其他选项卡。
   4. 选择“Always Encrypted”选项卡。
   5. 务必不要选中“启用 Always Encrypted（列加密）”。
   6. 选择“连接” 。

   

2. 粘贴并执行以下查询。 查询应会返回二进制加密数据。

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

   

3. 连接到数据库，并在连接中启用 Always Encrypted。

   1. 在查询窗口中右键单击任意位置，然后选择“连接”和“更改连接”>。 这将打开“连接到数据库引擎”对话框。
   2. 选择“选项”。<< 这将显示“连接到数据库引擎”对话框中的其他选项卡。
   3. 选择“Always Encrypted”选项卡。
   4. 选择“启用 Always Encrypted（列加密）”。
   5. 选择“连接” 。

   

4. 重新运行同一查询。 由于在连接时已为数据库连接启用了 Always Encrypted，因此 SSMS 中的客户端驱动程序将尝试解密两个加密列中存储的数据。 如果使用 Azure Key Vault，系统可能会提示登录到 Azure。

   

5. 启用“Always Encrypted 参数化”。 使用此功能，可以运行按加密列筛选数据（或将数据插入加密列）的查询。

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

   

6. 粘贴并执行以下查询，该查询按加密的 SSN 列筛选数据。 查询应会返回一行，其中包含纯文本值。

   DECLARE @SSN [char](11) = '795-73-9838'
   SELECT [SSN], [Salary] FROM [HR].[Employees]
   WHERE [SSN] = @SSN
   

7. （可选）如果使用 Azure Key Vault 并且配置了访问策略权限模型，请按照以下步骤操作，看看用户在无权访问保护数据的列主密钥的情况下尝试从加密列中检索纯文本数据时会发生什么情况。

   1. 在密钥保管库的访问策略中移除自己的密钥 unwrap 权限。 有关详细信息，请参阅分配 Key Vault 访问策略。
   2. 由于 SSMS 中的客户端驱动程序会将从密钥保管库获取的列加密密钥缓存 2 小时，请关闭 SSMS 后重新将其打开。 这将确保密钥缓存为空。
   3. 连接到数据库，并在连接中启用 Always Encrypted。
   4. 粘贴并执行以下查询。 查询应会失败并显示错误消息，指示缺少所需的 unwrap 权限。

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

PowerShell

1. 连接到数据库并禁用 Always Encrypted，然后运行查询以从加密列读取数据。 查询应会返回二进制数组形式的加密数据。

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString $connectionString -Query $query
   

2. 连接到数据库并启用 Always Encrypted，然后运行查询以从加密列读取数据。 由于有权访问保护加密列的列主密钥，因此查询应会返回纯文本数据。

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
   

备注

Invoke-SqlCmd 不支持可以按加密列筛选数据或插入数据的查询。 此类查询需要参数化，但 Invoke-SqlCmd 不支持参数化查询。

后续步骤

• 使用 Always Encrypted 开发应用程序

另请参阅

• Always Encrypted 文档
• 具有安全 Enclave 的 Always Encrypted 文档
• 使用 SQL Server Management Studio 预配 Always Encrypted 密钥
• 使用 PowerShell 配置“始终加密”功能
• 始终加密向导
• 通过 SQL Server Management Studio 查询使用 Always Encrypted 的列