Set up SQL Server TDE Extensible Key Management by using Azure Key Vault
Applies to: SQL Server (all supported versions)
In this article, you install and configure the SQL Server Connector for Azure Key Vault.
Before you begin using Azure Key Vault with your SQL Server instance, be sure that you've met the following prerequisites:
You must have an Azure subscription.
Create an Azure Active Directory (Azure AD) instance.
Familiarize yourself with the principles of Extensible Key Management (EKM) storage with Azure Key Vault by reviewing EKM with Azure Key Vault (SQL Server).
Install the version of Visual Studio C++ Redistributable that's based on the version of SQL Server that you're running:
SQL Server version Visual Studio C++ Redistributable version 2008, 2008 R2, 2012, 2014 Visual C++ Redistributable packages for Visual Studio 2013 2016 Visual C++ Redistributable for Visual Studio 2015
Step 1: Set up an Azure AD service principal
To grant your SQL Server instance access permissions to your Azure key vault, you need a service principal account in Azure AD.
Sign in to the Azure portal, and do either of the following:
Select the Azure Active Directory button.
Select More services and then, in the All services box, type Azure Active Directory.
Register an application with Azure Active Directory by doing the following. (For detailed step-by-step instructions, see the "Get an identity for the application" section of the Azure Key Vault blog post.)
a. On the Azure Active Directory Overview pane, select App registrations.
b. On the App registrations pane, select New registration.
c. On the Register an application pane, enter the user-facing name for the app, and then select Register.
d. In the left pane, select Certificates & secrets, and then select New client secret.
e. Under Add a client secret, enter a description and an appropriate expiration, and then select Add.
f. On the Certificates & secrets pane, under "Value", select the Copy button next to the value of the client secret to be used to create an asymmetric key in SQL Server.
g. In the left pane, select Overview and then, in the Application (client) ID box, copy the value to be used to create an asymmetric key in SQL Server.
Step 2: Create a key vault
Select the method you want to use to create a key vault.
Create a key vault by using the Azure portal
You can use the Azure portal to create the key vault and then add an Azure AD principal to it.
Create a resource group.
All Azure resources that you create via the Azure portal must be contained in a resource group, which you create to house your key vault. The resource name in this example is ContosoDevRG. Choose your own resource group and key vault name, because all key vault names must be globally unique.
On the Create a resource group pane, under Project details, enter the values, and then select Review + create.
Create a key vault.
On the Create key vault pane, select the Basics tab, enter the appropriate values, and then select Review + create.
On the Access policies pane, select Add Access Policy.
On the Add access policy pane, do the following:
a. In the Configure from template (optional) drop-down list, select Key Management.
b. In the left pane, select the Key permissions tab, and then verify the Get, List, Unwrap Key, and Wrap Key check boxes are selected.
c. Select Add.
In the left pane, select the Select principal tab, and then do the following:
a. In the Principal pane, under Select, start typing the name of your Azure AD application and then, in the results list, select the application you want to add.
b. Select the Select button to add the principal to your key vault.
c. At the lower left, select Add to save your changes.
On the Key Vault pane, select Keys and enter a key vault name. Use key type RSA and RSA Key Size 2048. Set activation and expiration dates as appropriate and set Enabled? as Yes.
On the Access policies pane, select Save.
Step 3: Install the SQL Server Connector
Download the SQL Server Connector from the Microsoft Download Center. The download should be done by the administrator of the SQL Server computer.
- SQL Server Connector versions 188.8.131.520 and older have been replaced and are no longer supported in production environments and using the instructions on the SQL Server Connector Maintenance & Troubleshooting page under Upgrade of SQL Server Connector.
- Starting with version 184.108.40.206, the SQL Server Connector reports relevant error messages to the Windows event logs for troubleshooting.
- Starting with version 220.127.116.11, there is support for private Azure clouds, including Azure China, Azure Germany, and Azure Government.
- There is a breaking change in version 18.104.22.168 in terms of the thumbprint algorithm. You may experience database restore failures after upgrading to 22.214.171.124. For more information, see KB article 447099.
- Starting with version 126.96.36.199 (TimeStamp: September 2020), the SQL Server Connector supports filtering messages and network request retry logic.
- Starting with updated version 188.8.131.52 (TimeStamp: November 2020), the SQL Server Connector supports RSA 2048, RSA 3072, RSA-HSM 2048 and RSA-HSM 3072 keys.
By default, the Connector is installed at C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault. This location can be changed during setup. If you do change it, adjust the scripts in the next section.
There's no interface for the Connector, but if it's installed successfully, the Microsoft.AzureKeyVaultService.EKM.dll is installed on the machine. This assembly is the cryptographic EKM provider DLL that needs to be registered with SQL Server 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 the SQL Server Connector, see:
- A. Maintenance Instructions for the SQL Server Connector
- C. Error Code Explanations for the SQL Server Connector
Step 4: Configure SQL Server
For a note about the minimum permission levels needed for each action in this section, see B. Frequently Asked Questions.
Run sqlcmd.exe or open SQL Server Management Studio.
Configure SQL Server to use EKM by running the following Transact-SQL script:
-- Enable advanced options. USE master; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO -- Enable EKM provider EXEC sp_configure 'EKM provider enabled', 1; GO RECONFIGURE;
Register the SQL Server Connector as an EKM provider with SQL Server.
Create a cryptographic provider by using the SQL Server Connector, which is an EKM provider for the Azure key vault. In this example, the provider name is
CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll'; GO
The file path length can't exceed 256 characters.
Set up a SQL Server credential for a SQL Server login to use the key vault.
A credential must be added to each login that will perform encryption by using a key from the key vault. This might include:
A SQL Server administrator login that uses the key vault to set up and manage SQL Server encryption scenarios.
Other SQL Server logins that might enable TDE or other SQL Server encryption features.
There is one-to-one mapping between credentials and logins. That is, each login must have a unique credential.
Modify this Transact-SQL script in the following ways:
ContosoEKMKeyVault) to point to your Azure key vault.
- If you're using global Azure, replace the
IDENTITYargument with the name of your Azure key vault from Step 2: Create a key vault.
- If you're using a private Azure cloud (for example, Azure Government, Azure China 21Vianet, or Azure Germany), replace the
IDENTITYargument with the Vault URI that's returned in step 3 of the Create a key vault and key by using PowerShell section. Don't include "https://" in the Vault URI.
- If you're using global Azure, replace the
Replace the first part of the
SECRETargument with the Azure Active Directory Client ID from Step 1: Set up an Azure AD service principal. In this example, the Client ID is
Be sure to remove the hyphens from the App (Client) ID.
Complete the second part of the
SECRETargument with Client Secret from Step 1: Set up an Azure AD service principal. In this example, the Client Secret is
08:k?[:XEZFxcwIPvVVZhTjHWXm7w1?m. The final string for the
SECRETargument will be a long sequence of letters and numbers, without hyphens.
USE master; CREATE CREDENTIAL sysadmin_ekm_cred WITH IDENTITY = 'ContosoEKMKeyVault', -- for public Azure -- WITH IDENTITY = 'ContosoEKMKeyVault.vault.usgovcloudapi.net', -- for Azure Government -- WITH IDENTITY = 'ContosoEKMKeyVault.vault.azure.cn', -- for Azure China 21Vianet -- WITH IDENTITY = 'ContosoEKMKeyVault.vault.microsoftazure.de', -- for Azure Germany --<----Application (Client) ID ---><--Azure AD app (Client) ID secret--> SECRET = '9A57CBC54C4C40E2B517EA677E0EFA0008:k?[:XEZFxcwIPvVVZhTjHWXm7w1?m' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM; -- 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 CREDENTIALargument and programmatically removing the hyphens from the Client ID, see CREATE CREDENTIAL (Transact-SQL).
Open your Azure key vault key in your SQL Server instance.
Whether you created a new key or imported an asymmetric key, as described in Step 2: Create a key vault, you will need to open the key. Open it by providing your key name in the following Transact-SQL script.
EKMSampleASYKeywith the name you'd like the key to have in SQL Server.
ContosoRSAKey0with the name of your key in your Azure key vault.
CREATE ASYMMETRIC KEY EKMSampleASYKey FROM PROVIDER [AzureKeyVault_EKM] WITH PROVIDER_KEY_NAME = 'ContosoRSAKey0', CREATION_DISPOSITION = OPEN_EXISTING;
Create a new login by using the asymmetric key in SQL Server that you created in the preceding step.
--Create a Login that will associate the asymmetric key to this login CREATE LOGIN TDE_Login FROM ASYMMETRIC KEY EKMSampleASYKey;
Create a new login from the asymmetric key in SQL Server. Drop the credential mapping from Step 4: Configure SQL Server so that the credentials can be mapped to the new login.
--Now drop the credential mapping from the original association ALTER LOGIN [<domain>\<login>] DROP CREDENTIAL sysadmin_ekm_cred;
Alter the new login, and map the EKM credentials to the new login.
--Now add the credential mapping to the new Login ALTER LOGIN TDE_Login ADD CREDENTIAL sysadmin_ekm_cred;
Create a test database that will be encrypted with the Azure key vault key.
--Create a test database that will be encrypted with the Azure key vault key CREATE DATABASE TestTDE
Create a database encryption key by using the ASYMMETRIC KEY (EKMSampleASYKey).
--Create an ENCRYPTION KEY using the ASYMMETRIC KEY (EKMSampleASYKey) CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER ASYMMETRIC KEY EKMSampleASYKey;
Encrypt the test database. Enable TDE by setting ENCRYPTION ON.
--Enable TDE by setting ENCRYPTION ON ALTER DATABASE TestTDE SET ENCRYPTION ON;
Clean up the test objects. Delete all the objects that were created in this test script.
-- CLEAN UP USE Master ALTER DATABASE [TestTDE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE [TestTDE] ALTER LOGIN [TDE_Login] DROP CREDENTIAL [sysadmin_ekm_cred] DROP LOGIN [TDE_Login] DROP CREDENTIAL [sysadmin_ekm_cred] USE MASTER DROP ASYMMETRIC KEY [EKMSampleASYKey] DROP CRYPTOGRAPHIC PROVIDER [AzureKeyVault_EKM]
For sample scripts, see the blog at SQL Server Transparent Data Encryption and Extensible Key Management with Azure Key Vault.
Now that you've completed the basic configuration, see Use the SQL Server Connector with SQL encryption features.