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

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

概述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 密钥保管库 (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.

重要

Azure 提供两个不同的部署模型用于创建和处理资源:Resource Manager 和经典模型Azure has two different deployment models for creating and working with resources: Resource Manager and Classic. 本文介绍如何使用经典部署模型。This article covers using the Classic deployment model. Microsoft 建议大多数新部署使用资源管理器模型。Microsoft recommends that most new deployments use the Resource Manager model.

如果在本地计算机上运行 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. 通过使用几个 Azure PowerShell cmdlet 来启用此功能,可以自动为 SQL VM 进行必要的配置以便访问密钥保管库。With a few Azure PowerShell cmdlets to enable this feature, you can automate the configuration necessary for a SQL VM to access your key vault.

启用此功能后,它会自动安装 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.

配置 AKV 集成Configure AKV Integration

使用 PowerShell 来配置 Azure 密钥保管库集成。Use PowerShell to configure Azure Key Vault Integration. 以下各节概述了所需的参数,并提供了一个示例 PowerShell 脚本。The following sections provide an overview of the required parameters and then a sample PowerShell script.

安装 SQL Server IaaS 扩展Install the SQL Server IaaS Extension

首先,请安装 SQL Server IaaS 扩展First, install the SQL Server IaaS Extension.

了解输入参数Understand the input parameters

下表列出在下一节中运行 PowerShell 脚本所需的参数。The following table lists the parameters required to run the PowerShell script in the next section.

参数Parameter DescriptionDescription 示例Example
$akvURL$akvURL 密钥保管库 URLThe key vault URL "https://contosokeyvault.vault.azure.net/""https://contosokeyvault.vault.azure.net/"
$spName$spName 服务主体名称Service Principal name “fde2b411-33d5-4e11-af04eb07b669ccf2”"fde2b411-33d5-4e11-af04eb07b669ccf2"
$spSecret$spSecret 服务主体密码Service Principal secret “9VTJSQwzlFepD8XODnzy8n2V01Jd8dAjwm/azF1XDKM =”"9VTJSQwzlFepD8XODnzy8n2V01Jd8dAjwm/azF1XDKM="
$credName$credName 凭据名称:AKV 集成在 SQL Server 内创建一个凭据,使 VM 具有对密钥保管库的访问权限。Credential name: AKV Integration creates a credential within SQL Server, allowing the VM to have access to the key vault. 为此凭据选择一个名称。Choose a name for this credential. “mycred1”"mycred1"
$vmName$vmName 虚拟机名称:以前创建的 SQL VM 的名称。Virtual machine name: The name of a previously created SQL VM. “myvmname”"myvmname"
$serviceName$serviceName 服务名称:与 SQL VM 关联的云服务名称。Service name: The Cloud Service name that is associated with the SQL VM. “mycloudservicename”"mycloudservicename"

使用 PowerShell 启用 AKV 集成Enable AKV Integration with PowerShell

New-AzureVMSqlServerKeyVaultCredentialConfig cmdlet 为 Azure 密钥保管库集成功能创建配置对象。The New-AzureVMSqlServerKeyVaultCredentialConfig cmdlet creates a configuration object for the Azure Key Vault Integration feature. Set-AzureVMSqlServerExtension 通过 KeyVaultCredentialSettings 参数配置此集成。The Set-AzureVMSqlServerExtension configures this integration with the KeyVaultCredentialSettings parameter. 以下步骤显示如何使用这些命令。The following steps show how to use these commands.

  1. 在 Azure PowerShell 中,首先使用特定的值配置输入参数,如本主题前面各节中所述。In Azure PowerShell, first configure the input parameters with your specific values as described in the previous sections of this topic. 以下脚本是一个示例。The following script is an example.

     $akvURL = "https:\//contosokeyvault.vault.azure.net/"
     $spName = "fde2b411-33d5-4e11-af04eb07b669ccf2"
     $spSecret = "9VTJSQwzlFepD8XODnzy8n2V01Jd8dAjwm/azF1XDKM="
     $credName = "mycred1"
     $vmName = "myvmname"
     $serviceName = "mycloudservicename"
    
  2. 然后使用以下脚本来配置和启用 AKV 集成。Then use the following script to configure and enable AKV Integration.

     $secureakv =  $spSecret | ConvertTo-SecureString -AsPlainText -Force
     $akvs = New-AzureVMSqlServerKeyVaultCredentialConfig -Enable -CredentialName $credname -AzureKeyVaultUrl $akvURL -ServicePrincipalName $spName -ServicePrincipalSecret $secureakv
     Get-AzureVM -ServiceName $serviceName -Name $vmName | Set-AzureVMSqlServerExtension -KeyVaultCredentialSettings $akvs | Update-AzureVM
    

SQL IaaS 代理扩展将使用此新配置来更新 SQL VM。The SQL IaaS Agent Extension will update the SQL VM with this new configuration.

后续步骤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.