title

Setup TDE Extensible Key Management with Azure Key Vault

description

Steps to install and configure SQL Server Connect for Azure Key Vault.

ms.custom

seo-lt-2019

ms.date

09/12/2019

ms.prod

sql

ms.reviewer

vanto

ms.technology

security

ms.topic

conceptual

helpviewer_keywords

EKM, with key vault setup

SQL Server Connector, setup

SQL Server Connector

ms.assetid

c1f29c27-5168-48cb-b649-7029e4816906

author

jaszymas

ms.author

jaszymas

SQL Server TDE Extensible Key Management Using Azure Key Vault - Setup Steps

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]

The following steps walk through the installation and configuration of the [!INCLUDEssNoVersion] Connector for Azure Key Vault.

Before You Start

To use Azure Key Vault with your SQL Server, there are a few prerequisites:

You must have an Azure subscription
Install the latest Azure PowerShell (5.2.0 or higher).
Create an Azure Active Directory
Familiarize yourself with the principals of EKM storage using the Azure Key Vault by reviewing Extensible Key Management Using Azure Key Vault (SQL Server).
Have the appropriate version of the Visual Studio C++ redistributable installed based on the version of SQL Server that you are running:

SQL Server Version	Redistributable Install Link
2008, 2008 R2, 2012, 2014	Visual C++ Redistributable Packages for Visual Studio 2013
2016	Visual C++ Redistributable for Visual Studio 2015

Part I: Set up an Azure Active Directory service principal

In order to grant SQL Server access permissions to your Azure Key Vault, you will need a Service Principal account in Azure Active Directory (AAD).

Go to the Azure Portal, and sign in.
Register an application with Azure Active Directory. For detailed step-by-step instructions to register an application, see the Get an identity for the application section of the Azure Key Vault blog post.
Copy the Client ID and Client Secret for a later step, where they will be used to grant [!INCLUDEssNoVersion] access to your key vault.

Part II: Create a Key Vault and Key

The key vault and key created here will be used by the SQL Server Database Engine for encryption key protection.

Important

The subscription where the key vault is created must be in the same default Azure Active Directory where the Azure Active Directory service principal was created. If you want to use an Active Directory other than your default Active Directory for creating a service principal for the SQL Server Connector, you must change the default Active Directory in your Azure account before creating your key vault. To learn how to change the default Active Directory to the one you'd like to use, please refer to the SQL Server Connector FAQs.

Open PowerShell and Sign in

Install and start the latest Azure PowerShell (5.2.0 or higher). Sign in to your Azure account with the following command:
```
Connect-AzAccount  
```
The statement returns:
```
Environment           : AzureCloud  
Account               : <account_name>  
TenantId              : <tenant_id>  
SubscriptionId        : <subscription_id>  
CurrentStorageAccount :  
```
[!NOTE]
If you have multiple subscriptions and want to specify a specific one to use for the vault, then use Get-AzSubscription to see the subscriptions and Select-AzSubscription to choose the correct subscription. Otherwise, PowerShell will select one for you by default.
Create a new resource group

All Azure resources created via Azure Resource Manager must be contained in resource groups. Create a resource group to house your key vault. This example uses ContosoDevRG. Choose your own unique resource group and key vault name as all key vault names are globally unique.
```
New-AzResourceGroup -Name ContosoDevRG -Location 'East Asia'  
```
The statement returns:
```
ResourceGroupName: ContosoDevRG  
Location         : eastasia  
ProvisioningState: Succeeded  
Tags             :   
ResourceId       : /subscriptions/<subscription_id>/  
                    resourceGroups/ContosoDevRG  
```
[!NOTE]
For the -Location parameter, use the command Get-AzureLocation to identify how to specify an alternative location to the one in this example. If you need more information, type: Get-Help Get-AzureLocation

Create a Key Vault

The New-AzKeyVault cmdlet requires a resource group name, a key vault name, and a geographic location. For example, for a key vault named ContosoDevKeyVault, type:

New-AzKeyVault -VaultName 'ContosoDevKeyVault' `  
   -ResourceGroupName 'ContosoDevRG' -Location 'East Asia'

Record the name of your key vault.

The statement returns:

Vault Name                       : ContosoDevKeyVault  
Resource Group Name              : ContosoDevRG  
Location                         : East Asia  
ResourceId                       : /subscriptions/<subscription_id>/  
                                    resourceGroups/ContosoDevRG/providers/  
                                    Microsoft/KeyVault/vaults/ContosoDevKeyVault  
Vault URI: https://ContosoDevKeyVault.vault.azure.net  
Tenant ID                        : <tenant_id>  
SKU                              : Standard  
Enabled For Deployment?          : False  
Enabled For Template Deployment? : False  
Enabled For Disk Encryption?     : False  
Access Policies                  :  
         Tenant ID              : <tenant_id>  
         Object ID              : <object_id>  
         Application ID         :   
         Display Name           : <display_name>  
         Permissions to Keys    : get, create, delete, list, update, import,   
                                  backup, restore  
         Permissions to Secrets : all  
Tags                             :

Grant Permission for the Azure Active Directory Service Principal to Access the Key Vault

You can authorize other users and applications to use your key vault.
In this case, let's use the Azure Active Directory service principal created in Part I to authorize the [!INCLUDEssNoVersion] instance.

[!IMPORTANT]
The Azure Active Directory service principal must have at least the get, wrapKey, and unwrapKey permissions for the key vault.

As shown below, use the Client ID from Part I for the ServicePrincipalName parameter. The Set-AzKeyVaultAccessPolicy runs silently with no output if it runs successfully.
```
Set-AzKeyVaultAccessPolicy -VaultName 'ContosoDevKeyVault'`  
  -ServicePrincipalName EF5C8E09-4D2A-4A76-9998-D93440D8115D `  
  -PermissionsToKeys get, wrapKey, unwrapKey  
```
Call the Get-AzKeyVault cmdlet to confirm the permissions. In the statement output under 'Access Policies,' you should see your AAD application name listed as another tenant that has access to this key vault.
Generate an Asymmetric Key in the Key Vault

There are two ways to generate a key in Azure Key Vault: 1) Import an existing key or 2) create a new key.

[!NOTE] > SQL Server only supports 2048-bit RSA keys.

Best Practice:

To ensure quick key recovery and be able to access your data outside of Azure, we recommend the following best practice:
1. Create your encryption key locally on a local HSM device. (Make sure this is an asymmetric, RSA 2048 key so it's is supported by SQL Server.)
2. Import the encryption key to Azure Key Vault. See the steps below for how to do that.
3. Before using the key in Azure Key Vault for the first time, take an Azure Key Vault key backup. Learn more about the Backup-AzureKeyVaultKey command.
4. Whenever any changes are made to the key (for example add ACLs, add tags, add key attributes), be sure to take another Azure Key Vault key backup.
  
  [!NOTE]
  Backing up a key is an Azure Key Vault key operation which returns a file that can be saved anywhere.
Types of keys:

There are two types of keys you can generate in Azure Key Vault that will work with SQL Server. Both are asymmetric 2048-bit RSA keys.
- Software-protected: Processed in software and encrypted at rest. Operations on software-protected keys occur on Azure Virtual Machines. Recommended for keys not used in a production deployment.
- HSM-protected: Created and protected by a hardware security module (HSM) for additional security. Cost is about $1 per key version.
  
  [!IMPORTANT]
  The SQL Server Connector requires the key name to only use the characters "a-z", "A-Z", "0-9", and "-", with a 26-character limit.
  Different key versions under the same key name in Azure Key Vault will not work with [!INCLUDEssNoVersion] Connector. To rotate an Azure Key Vault key that's being used by [!INCLUDEssNoVersion], please refer to the Key Rollover steps in the SQL Server Connector Maintenance & Troubleshooting.
Import an Existing Key

If you have an existing 2048-bit RSA software-protected key, you can upload the key to Azure Key Vault. For example, if you had a .PFX file saved to your C:\\ drive in a file named softkey.pfx that you want to upload to Azure Key Vault, type the following to set the variable securepfxpwd for a password of 12987553 for the .PFX file:
```
$securepfxpwd = ConvertTo-SecureString -String '12987553' `  
  -AsPlainText -Force  
```
Then you can type the following to import the key from the .PFX file, which protects the key by hardware (recommended) in the Key Vault service:
```
    Add-AzureKeyVaultKey -VaultName 'ContosoKeyVault' `  
      -Name 'ContosoFirstKey' -KeyFilePath 'c:\softkey.pfx' `  
      -KeyFilePassword $securepfxpwd $securepfxpwd  -Destination 'HSM'  
```
[!IMPORTANT]
Importing the asymmetric key is highly recommended for production scenarios because it allows the administrator to escrow the key in a key escrow system. If the asymmetric key is created in the vault, it cannot be escrowed because the private key can never leave the vault. Keys used to protect critical data should be escrowed. The loss of an asymmetric key will result in permanent data loss.

Create a new key

Example:

Alternatively, you can create a new encryption key directly in Azure Key vault and have it be either software-protected or HSM-protected. In this example, let's create a software-protected key using the Add-AzureKeyVaultKey cmdlet:
```
Add-AzureKeyVaultKey -VaultName 'ContosoDevKeyVault' `  
  -Name 'ContosoRSAKey0' -Destination 'Software'  
```
The statement returns:
```
Attributes : Microsoft.Azure.Commands.KeyVault.Models.KeyAttributes  
Key        :  {"kid":"https:contosodevKeyVault.azure.net/keys/  
               ContosoRSAKey0/<guid>","dty":"RSA:,"key_ops": ...  
VaultName  : contosodevkeyvault  
Name       : contosoRSAKey0  
Version    : <guid>  
Id         : https://contosodevkeyvault.vault.azure.net:443/  
             keys/ContosoRSAKey0/<guid>  
```

Important

The key vault supports multiple versions of the same named key, but keys to be used by [!INCLUDEssNoVersion] Connector should not be versioned or rolled. If the administrator wants to roll the key used for [!INCLUDEssNoVersion] encryption, a new key with a different name should be created in the vault and used to encrypt the DEK.

Part III: Install the [!INCLUDEssNoVersion] Connector

Download the SQL Server Connector from the Microsoft Download Center. (This should be done by the administrator of the [!INCLUDEssNoVersion] computer.)

Note

Versions 1.0.0.440 and older have been replaced and are no longer supported in production environments. Upgrade to version 1.0.1.0 or later by visiting the Microsoft Download Center and using the instructions on the SQL Server Connector Maintenance & Troubleshooting page under "Upgrade of SQL Server Connector."

Note

There is a breaking change in 1.0.5.0 version, in terms of the thumbprint algorithm. You may experience database restore failure after upgrading to 1.0.5.0 version. Please refer KB aritcle 447099.

By default, the connector installs at C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault. This location can be changed during setup. (If changed, adjust the scripts below.)

There is no interface for the Connector, but if it is installed successfully, the Microsoft.AzureKeyVaultService.EKM.dll is installed on the machine. This is the cryptographic EKM provider DLL that needs to be registered with [!INCLUDEssNoVersion] by using the CREATE CRYPTOGRAPHIC PROVIDER statement.

The SQL Server Connector installation also allows you to optionally download sample scripts for SQL Server encryption.

To view error code explanations, configuration settings, or maintenance tasks for SQL Server Connector, visit the appendix at the bottom of this topic:

Part IV: Configure [!INCLUDEssNoVersion]

Refer to B. Frequently Asked Questions to see a note about the minimum permission levels needed for each action in this section.

Launch sqlcmd.exe or [!INCLUDEssNoVersion] Management Studio

Configure [!INCLUDEssNoVersion] to use EKM

Execute the following [!INCLUDEtsql] script to configure the [!INCLUDEssDE] to use an EKM provider.

-- Enable advanced options.  
USE master;  
GO  

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  

-- Enable EKM provider  
sp_configure 'EKM provider enabled', 1;  
GO  
RECONFIGURE;

Register (create) the [!INCLUDEssNoVersion] Connector as an EKM provider with [!INCLUDEssNoVersion]

-- Create a cryptographic provider, using the [!INCLUDEssNoVersion] Connector, which is an EKM provider for the Azure Key Vault.
This example uses the name AzureKeyVault_EKM_Prov.
```
CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov   
FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';  
GO  
```
[!NOTE]
The file path length cannot exceed 256 characters.
Setup a [!INCLUDEssNoVersion] credential for a [!INCLUDEssNoVersion] login to use the key vault

A credential must be added to each login that will be performing encryption using a key from the Key Vault. This might include:
- A [!INCLUDEssNoVersion] administrator login who will use key vault in order to setup and manage [!INCLUDEssNoVersion] encryption scenarios.
- Other [!INCLUDEssNoVersion] logins who might enable Transparent Data Encryption (TDE), or other [!INCLUDEssNoVersion] encryption features.
There is one-to-one mapping between credentials and logins. That is, each login must have a unique credential.

Modify the [!INCLUDEtsql] script below in the following ways:
- Edit the IDENTITY argument (ContosoDevKeyVault) to point to your Azure Key Vault.
  - If you're using global Azure, replace the IDENTITY argument with the name of your Azure Key Vault from Part II.
  - If you're using a private Azure cloud (ex. Azure Government, Azure China, or Azure Germany), replace the IDENTITY argument with the Vault URI that is returned in Part II, step 3. Do not include "https://" in the Vault URI.
- Replace the first part of the SECRET argument with the Azure Active Directory Client ID from Part I. In this example, the Client ID is EF5C8E094D2A4A769998D93440D8115D.
  
  [!IMPORTANT]
  You must remove the hyphens from the Client ID.
- Complete the second part of the SECRET argument with Client Secret from Part I. In this example the Client Secret from Part 1 is Replace-With-AAD-Client-Secret. The final string for the SECRET argument will be a long sequence of letters and numbers, with no hyphens.
```
USE master;  
CREATE CREDENTIAL sysadmin_ekm_cred   
    WITH IDENTITY = 'ContosoDevKeyVault', -- for public Azure
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.usgovcloudapi.net', -- for Azure Government
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.azure.cn', -- for Azure China
    -- WITH IDENTITY = 'ContosoDevKeyVault.vault.microsoftazure.de', -- for Azure Germany   
    SECRET = 'EF5C8E094D2A4A769998D93440D8115DReplace-With-AAD-Client-Secret'   
FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;  

-- Add the credential to the SQL Server administrator's domain login   
ALTER LOGIN [<domain>\<login>]  
ADD CREDENTIAL sysadmin_ekm_cred;  
```
For an example of using variables for the CREATE CREDENTIAL arguments and programmatically removing the hyphens from the Client ID, see CREATE CREDENTIAL (Transact-SQL).
Open your Azure Key Vault key in [!INCLUDEssNoVersion]

Whether you created a new key, or imported an asymmetric key as described in Part II, you will need to open the key. Open the key by providing your key name in the following [!INCLUDEtsql] script.
- Replace CONTOSO_KEY with the name you'd like the key to have in [!INCLUDEssNoVersion].
- Replace ContosoRSAKey0 with the name of your key in Azure Key Vault.
```
CREATE ASYMMETRIC KEY CONTOSO_KEY   
FROM PROVIDER [AzureKeyVault_EKM_Prov]  
WITH PROVIDER_KEY_NAME = 'ContosoRSAKey0',  
CREATION_DISPOSITION = OPEN_EXISTING;  
```

Next Step

Now that you have completed the basic configuration, see how to Use SQL Server Connector with SQL Encryption Features

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQL Server TDE Extensible Key Management Using Azure Key Vault - Setup Steps

Before You Start

Part I: Set up an Azure Active Directory service principal

Part II: Create a Key Vault and Key

Best Practice:

Types of keys:

Import an Existing Key

Create a new key

Example:

Part III: Install the [!INCLUDEssNoVersion] Connector

Part IV: Configure [!INCLUDEssNoVersion]

Next Step

See Also

FilesExpand file tree

setup-steps-for-extensible-key-management-using-the-azure-key-vault.md

Latest commit

History

setup-steps-for-extensible-key-management-using-the-azure-key-vault.md

File metadata and controls

SQL Server TDE Extensible Key Management Using Azure Key Vault - Setup Steps

Before You Start

Part I: Set up an Azure Active Directory service principal

Part II: Create a Key Vault and Key

Best Practice:

Types of keys:

Import an Existing Key

Create a new key

Example:

Part III: Install the [!INCLUDEssNoVersion] Connector

Part IV: Configure [!INCLUDEssNoVersion]

Next Step

See Also