您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

在 Azure 虚拟机上配置 SQL Server 的 Azure Key Vault 集成 (Resource Manager)Configure Azure Key Vault Integration for SQL Server on Azure Virtual Machines (Resource Manager)

概述Overview

SQL Server 加密功能多种多样,包括透明数据加密 (TDE)列级加密 (CLE)备份加密There are multiple SQL Server encryption features, such as transparent data encryption (TDE), column level encryption (CLE), and backup encryption. 这些加密形式要求管理和存储用于加密的加密密钥。These forms of encryption require you to manage and store the cryptographic keys you use for encryption. Azure Key Vault (AKV) 服务专用于在一个高度可用的安全位置改进这些密钥的安全性和管理。The Azure Key Vault (AKV) service is designed to improve the security and management of these keys in a secure and highly available location. SQL Server 连接器使 SQL Server 能够使用 Azure 密钥保管库中的这些密钥。The SQL Server Connector enables SQL Server to use these keys from Azure Key Vault.

如果在本地计算机上运行 SQL Server,请按照此处步骤通过本地 SQL Server 计算机访问 Azure 密钥保管库If you are running SQL Server with on-premises machines, there are steps you can follow to access Azure Key Vault from your on-premises SQL Server machine. 但对于 Azure VM 中的 SQL Server,可以使用 Azure 密钥保管库集成功能节省时间。But for SQL Server in Azure VMs, you can save time by using the Azure Key Vault Integration feature.

启用此功能后,它会自动安装 SQL Server 连接器,配置 EKM 提供程序以访问 Azure Key Vault,并创建允许你访问保管库的凭据。When this feature is enabled, it automatically installs the SQL Server Connector, configures the EKM provider to access Azure Key Vault, and creates the credential to allow you to access your vault. 在前面提到的本地文档列出的步骤中,可以看到此功能自动完成步骤 2 和步骤 3。If you looked at the steps in the previously mentioned on-premises documentation, you can see that this feature automates steps 2 and 3. 仍需手动执行的唯一操作是创建密钥保管库和密钥。The only thing you would still need to do manually is to create the key vault and keys. 之后,自动进行 SQL VM 的整个设置。From there, the entire setup of your SQL VM is automated. 在此功能完成设置后,可以执行 T-SQL 语句,以按照通常的方式加密数据库或备份。Once this feature has completed this setup, you can execute T-SQL statements to begin encrypting your databases or backups as you normally would.

准备 AKV 集成Prepare for AKV Integration

若要使用 Azure 密钥保管库集成来配置 SQL Server VM,有以下几个先决条件:To use Azure Key Vault Integration to configure your SQL Server VM, there are several prerequisites:

  1. 安装 Azure PowerShellInstall Azure Powershell
  2. 创建 Azure Active DirectoryCreate an Azure Active Directory
  3. 创建密钥保管库Create a key vault

以下各节描述了这些先决条件,以及稍后运行 PowerShell cmdlet 需要收集的信息。The following sections describe these prerequisites and the information you need to collect to later run the PowerShell cmdlets.

备注

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

安装 Azure PowerShellInstall Azure PowerShell

请确保已安装了最新的 Azure PowerShell 模块。Make sure you have installed the latest Azure PowerShell module. 有关详细信息,请参阅如何安装和配置 Azure PowerShellFor more information, see How to install and configure Azure PowerShell.

将应用程序注册到 Azure Active DirectoryRegister an application in your Azure Active Directory

首先,订阅中需要有 Azure Active Directory (AAD)。First, you need to have an Azure Active Directory (AAD) in your subscription. 其优点之一是允许为特定用户和应用程序授予对密钥保管库的权限。Among many benefits, this allows you to grant permission to your key vault for certain users and applications.

然后,将应用程序注册到 Azure AAD。Next, register an application with AAD. 这会提供一个服务主体帐户,使你有权访问 VM 所需的密钥保管库。This will give you a Service Principal account that has access to your key vault, which your VM will need. 在 Azure Key Vault 文章中,用户可以在将应用程序注册到 Azure Active Directory 部分中找到这些步骤,或者可以在此博客文章的获取应用程序的标识部分中看到这些步骤以及屏幕截图 。In the Azure Key Vault article, you can find these steps in the Register an application with Azure Active Directory section, or you can see the steps with screenshots in the Get an identity for the application section of this blog post. 在完成这些步骤之前,需要在注册期间收集以下信息,之后在 SQL VM 上启用 Azure Key Vault 集成时需要这些信息。Before completing these steps, you need to collect the following information during this registration that is needed later when you enable Azure Key Vault Integration on your SQL VM.

  • 添加应用程序后,在“已注册应用”边栏选项卡上找到“应用程序 ID” 。After the application is added, find the Application ID on the Registered app blade. 稍后会将该应用程序 ID 分配给 PowerShell 脚本中的 $spName(服务主体名称)参数,以启用 Azure 密钥保管库集成 。The application ID is assigned later to the $spName (Service Principal name) parameter in the PowerShell script to enable Azure Key Vault Integration.

    应用程序 ID

  • 在执行这些步骤期间,请在创建密钥时复制密钥的密码,如下面的屏幕截图中所示。During these steps when you create your key, copy the secret for your key as is shown in the following screenshot. 稍后会将此密钥密码分配给 PowerShell 脚本中的 $spSecret(服务主体密码)参数。This key secret is assigned later to the $spSecret (Service Principal secret) parameter in the PowerShell script.

    AAD 密码

  • 应用程序 ID 和机密将还可用于在 SQL Server 中创建凭据。The application ID and the secret will also be used to create a credential in SQL Server.

  • 必须为此新的客户端 ID 授予以下访问权限:获取密钥换行取消密钥换行You must authorize this new client ID to have the following access permissions: get, wrapKey, unwrapKey. 可通过 Set-AzKeyVaultAccessPolicy cmdlet 实现此操作。This is done with the Set-AzKeyVaultAccessPolicy cmdlet. 有关详细信息,请参阅 Azure 密钥保管库概述For more information, see Azure Key Vault overview.

创建密钥保管库Create a key vault

要使用 Azure 密钥保管库来存储用于在 VM 中加密的密钥,需要对密钥保管库的访问权限。In order to use Azure Key Vault to store the keys you will use for encryption in your VM, you need access to a key vault. 如果尚未设置密钥保管库,请按照开始使用 Azure Key Vault 一文中的步骤创建一个。If you have not already set up your key vault, create one by following the steps in the Getting Started with Azure Key Vault article. 在完成这些步骤之前,需要在设置期间收集一些信息,之后在 SQL VM 上启用 Azure Key Vault 集成时需要这些信息。Before completing these steps, there is some information you need to collect during this set up that is needed later when you enable Azure Key Vault Integration on your SQL VM.

New-AzKeyVault -VaultName 'ContosoKeyVault' -ResourceGroupName 'ContosoResourceGroup' -Location 'East Asia'

进行创建密钥保管库的步骤时,请注意返回的 vaultUri 属性,它是密钥保管库 URL。When you get to the Create a key vault step, note the returned vaultUri property, which is the key vault URL. 下面显示了该步骤中提供的示例,其中的密钥保管库名称是 ContosoKeyVault,因此密钥保管库 URL 为 https://contosokeyvault.vault.azure.net/In the example provided in that step, shown below, the key vault name is ContosoKeyVault, therefore the key vault URL would be https://contosokeyvault.vault.azure.net/.

稍后会将该密钥保管库 URL 分配给 PowerShell 脚本中的 $akvURL 参数,以启用 Azure 密钥保管库集成。The key vault URL is assigned later to the $akvURL parameter in the PowerShell script to enable Azure Key Vault Integration.

创建密钥保管库后,需要向密钥保管库添加密钥,稍后在 SQL Server 中创建非对称密钥时,会引用此密钥。After the key vault is created, we need to add a key to the key vault, this key will be referred when we create an asymmetric key create in SQL Server later.

备注

EKM 提供程序版本 1.0.4.0 通过 SQL IaaS 扩展安装在 SQL Server VM 上。EKM Provider version 1.0.4.0 is installed on the SQL Server VM through the SQL IaaS extension. 更新 SQL IaaS 扩展将不会更新提供程序版本。Upgrading the SQL IaaS Extension will not update the provider version. 如有需要(例如,迁移到 SQL 托管实例时),请考虑手动更新 EKM 提供程序版本。Please considering manually upgrading the EKM provider version if needed (for example, when migrating to a SQL Managed Instance).

启用和配置 AKV 集成Enabling and configuring AKV integration

为现有 VM 预配或配置 AKV 集成时可以启用该集成。You can enable AKV integration during provisioning or configure it for existing VMs.

新的 VMNew VMs

如果使用资源管理器预配新的 SQL Server 虚拟机,Azure 门户提供了启用 Azure 密匙保管库集成的方式。If you are provisioning a new SQL Server virtual machine with Resource Manager, the Azure portal provides a way to enable Azure Key Vault integration. Azure 密钥保管库功能仅适用于企业版、开发人员版和评估版的 SQL Server。The Azure Key Vault feature is available only for the Enterprise, Developer, and Evaluation Editions of SQL Server.

SQL Azure 密钥保管库集成

有关预配的详细演练,请参阅在 Azure 门户中预配 SQL Server 虚拟机For a detailed walkthrough of provisioning, see Provision a SQL Server virtual machine in the Azure portal.

现有 VMExisting VMs

备注

以下屏幕截图来自 Azure 门户中的SQL 虚拟机资源。The following screenshots are from the SQL virtual machines resource within the Azure portal. 对于不在SQL VM 资源提供程序中注册的支持结束 (EOS) sql Server vm 和 SQL Server vm, 请改用SQL Server 配置选项卡来管理 SQL Server VM。For end-of-support (EOS) SQL server VMs, and SQL Server VMs that have not been registered with the SQL VM resource provider, use the SQL Server configuration tab to manage your SQL Server VM instead.

对于现有 SQL Server 虚拟机, 请打开SQL 虚拟机资源, 然后选择 "设置" 下的 "安全性"。For existing SQL Server virtual machines, open your SQL virtual machines resource and select Security under Settings. 选择 "启用" 以启用 Azure Key Vault 集成。Select Enable to enable Azure Key Vault integration.

现有 VM 的 SQL AKV 集成

完成后, 选择 "安全" 页底部的 "应用" 按钮保存所做的更改。When finished, select the Apply button on the bottom of the Security page to save your changes.

备注

我们在此处创建的凭据名称将在稍后映射到 SQL 登录名。The credential name we created here will be mapped to a SQL login later. 这允许 SQL 登录名访问密钥保管库。This allows the SQL login to access the key vault.

备注

还可以使用模板配置 AKV 集成。You can also configure AKV integration using a template. 有关详细信息,请参阅用于 Azure 密钥保管库集成的 Azure 快速入门模板For more information, see Azure quickstart template for Azure Key Vault integration.

后续步骤Next steps

启用 Azure 密钥保管库集成之后,可以在 SQL VM 上启用 SQL Server 加密。After enabling Azure Key Vault Integration, you can enable SQL Server encryption on your SQL VM. 首先,需要在密钥保管库内创建一个非对称密钥,并在 VM 上的 SQL Server 中创建一个对称密钥。First, you will need to create an asymmetric key inside your key vault and a symmetric key within SQL Server on your VM. 然后,将能够执行 T-SQL 语句,启用对数据库和备份的加密。Then, you will be able to execute T-SQL statements to enable encryption for your databases and backups.

可以利用以下几种形式的加密:There are several forms of encryption you can take advantage of:

以下 Transact-SQL 脚本提供针对每种形式的示例。The following Transact-SQL scripts provide examples for each of these areas.

先决条件示例Prerequisites for examples

每个示例基于两个先决条件:密钥保管库中名为 CONTOSO_KEY 的非对称密钥,以及 AKV 集成功能创建的名为 Azure_EKM_TDE_cred 的凭据。Each example is based on the two prerequisites: an asymmetric key from your key vault called CONTOSO_KEY and a credential created by the AKV Integration feature called Azure_EKM_TDE_cred. 以下 Transact-SQL 命令设置这些运行示例所需的先决条件。The following Transact-SQL commands setup these prerequisites for running the examples.

USE master;
GO

--create credential
--The <<SECRET>> here requires the <Application ID> (without hyphens) and <Secret> to be passed together without a space between them.
CREATE CREDENTIAL sysadmin_ekm_cred
    WITH IDENTITY = 'keytestvault', --keyvault
    SECRET = '<<SECRET>>'
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;


--Map the credential to a SQL login that has sysadmin permissions. This allows the SQL login to access the key vault when creating the asymmetric key in the next step.
ALTER LOGIN [SQL_Login]
ADD CREDENTIAL sysadmin_ekm_cred;


CREATE ASYMMETRIC KEY CONTOSO_KEY
FROM PROVIDER [AzureKeyVault_EKM_Prov]
WITH PROVIDER_KEY_NAME = 'KeyName_in_KeyVault',  --The key name here requires the key we created in the key vault
CREATION_DISPOSITION = OPEN_EXISTING;

透明数据加密 (TDE)Transparent Data Encryption (TDE)

  1. 创建数据库引擎将用于 TDE 的 SQL Server 登录名,然后向其添加凭据。Create a SQL Server login to be used by the Database Engine for TDE, then add the credential to it.

    USE master;
    -- Create a SQL Server login associated with the asymmetric key
    -- for the Database engine to use when it loads a database
    -- encrypted by TDE.
    CREATE LOGIN EKM_Login
    FROM ASYMMETRIC KEY CONTOSO_KEY;
    GO
    
    -- Alter the TDE Login to add the credential for use by the
    -- Database Engine to access the key vault
    ALTER LOGIN EKM_Login
    ADD CREDENTIAL Azure_EKM_cred;
    GO
    
  2. 创建将用于 TDE 的数据库加密密钥。Create the database encryption key that will be used for TDE.

    USE ContosoDatabase;
    GO
    
    CREATE DATABASE ENCRYPTION KEY 
    WITH ALGORITHM = AES_128 
    ENCRYPTION BY SERVER ASYMMETRIC KEY CONTOSO_KEY;
    GO
    
    -- Alter the database to enable transparent data encryption.
    ALTER DATABASE ContosoDatabase
    SET ENCRYPTION ON;
    GO
    

加密备份Encrypted backups

  1. 创建数据库引擎将用于加密备份的 SQL Server 登录名,然后向其添加凭据。Create a SQL Server login to be used by the Database Engine for encrypting backups, and add the credential to it.

    USE master;
    -- Create a SQL Server login associated with the asymmetric key
    -- for the Database engine to use when it is encrypting the backup.
    CREATE LOGIN EKM_Login
    FROM ASYMMETRIC KEY CONTOSO_KEY;
    GO
    
    -- Alter the Encrypted Backup Login to add the credential for use by
    -- the Database Engine to access the key vault
    ALTER LOGIN EKM_Login
    ADD CREDENTIAL Azure_EKM_cred ;
    GO
    
  2. 备份数据库,同时使用密钥保管库中存储的非对称密钥指定加密。Backup the database specifying encryption with the asymmetric key stored in the key vault.

    USE master;
    BACKUP DATABASE [DATABASE_TO_BACKUP]
    TO DISK = N'[PATH TO BACKUP FILE]'
    WITH FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,
    ENCRYPTION(ALGORITHM = AES_256, SERVER ASYMMETRIC KEY = [CONTOSO_KEY]);
    GO
    

列级加密 (CLE)Column Level Encryption (CLE)

此脚本创建一个受密钥保管库中的非对称密钥保护的对称密钥,然后使用该对称密钥对数据库中的数据进行加密。This script creates a symmetric key protected by the asymmetric key in the key vault, and then uses the symmetric key to encrypt data in the database.

CREATE SYMMETRIC KEY DATA_ENCRYPTION_KEY
WITH ALGORITHM=AES_256
ENCRYPTION BY ASYMMETRIC KEY CONTOSO_KEY;

DECLARE @DATA VARBINARY(MAX);

--Open the symmetric key for use in this session
OPEN SYMMETRIC KEY DATA_ENCRYPTION_KEY
DECRYPTION BY ASYMMETRIC KEY CONTOSO_KEY;

--Encrypt syntax
SELECT @DATA = ENCRYPTBYKEY(KEY_GUID('DATA_ENCRYPTION_KEY'), CONVERT(VARBINARY,'Plain text data to encrypt'));

-- Decrypt syntax
SELECT CONVERT(VARCHAR, DECRYPTBYKEY(@DATA));

--Close the symmetric key
CLOSE SYMMETRIC KEY DATA_ENCRYPTION_KEY;

其他资源Additional resources

有关如何使用这些加密功能的详细信息,请参阅将 EKM 用于 SQL Server 加密功能For more information on how to use these encryption features, see Using EKM with SQL Server Encryption Features.

请注意,本文中的步骤假定用户已经具有在 Azure 虚拟机上运行的 SQL Server。Note that the steps in this article assume that you already have SQL Server running on an Azure virtual machine. 如果没有,请参阅在 Azure 中预配 SQL Server 虚拟机If not, see Provision a SQL Server virtual machine in Azure. 有关在 Azure VM 中运行 SQL Server 的其他指南,请参阅 Azure 虚拟机上的 SQL Server 概述For other guidance on running SQL Server on Azure VMs, see SQL Server on Azure Virtual Machines overview.