Configure Azure Key Vault integration for SQL Server on Azure VMs (Resource Manager)
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. The Azure Key Vault service is designed to improve the security and management of these keys in a secure and highly available location. The SQL Server Connector enables SQL Server to use these keys from Azure Key Vault.
If you are running SQL Server on-premises, there are steps you can follow to access Azure Key Vault from your on-premises SQL Server instance. But for SQL Server on Azure VMs, you can save time by using the Azure Key Vault Integration feature.
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. 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. From there, the entire setup of your SQL Server VM is automated. Once this feature has completed this setup, you can execute Transact-SQL (T-SQL) statements to begin encrypting your databases or backups as you normally would.
Prepare for AKV Integration
To use Azure Key Vault Integration to configure your SQL Server VM, there are several prerequisites:
The following sections describe these prerequisites and the information you need to collect to later run the PowerShell cmdlets.
This article has been updated to use the new Azure PowerShell Az module. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. For Az module installation instructions, see Install Azure PowerShell.
Install Azure PowerShell
Make sure you have installed the latest Azure PowerShell module. For more information, see How to install and configure Azure PowerShell.
Register an application in your Azure Active Directory
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.
Next, register an application with AAD. This will give you a Service Principal account that has access to your key vault, which your VM will need. 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. 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.
After the application is added, find the Application ID (also known as AAD ClientID or AppID) on the Registered app blade. The application ID is assigned later to the $spName (Service Principal name) parameter in the PowerShell script to enable Azure Key Vault Integration.
During these steps when you create your key, copy the secret for your key as is shown in the following screenshot. This key secret is assigned later to the $spSecret (Service Principal secret) parameter in the PowerShell script.
The application ID and the secret will also be used to create a credential in SQL Server.
You must authorize this new application ID (or client ID) to have the following access permissions: get, wrapKey, unwrapKey. This is done with the Set-AzKeyVaultAccessPolicy cmdlet. For more information, see Azure Key Vault overview.
Create a key vault
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. 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. 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'
When you get to the Create a key vault step, note the returned vaultUri property, which is the key vault URL. 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/.
The key vault URL is assigned later to the $akvURL parameter in the PowerShell script to enable Azure Key Vault Integration.
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.
Extensible Key Management (EKM) Provider version 184.108.40.206 is installed on the SQL Server VM through the SQL infrastructure as a service (IaaS) extension. Upgrading the SQL IaaS extension will not update the provider version. Please considering manually upgrading the EKM provider version if needed (for example, when migrating to a SQL Managed Instance).
Enabling and configuring Key Vault integration
You can enable Key Vault integration during provisioning or configure it for existing VMs.
If you are provisioning a new SQL virtual machine with Resource Manager, the Azure portal provides a way to enable Azure Key Vault integration. The Azure Key Vault feature is available only for the Enterprise, Developer, and Evaluation Editions of SQL Server.
For a detailed walkthrough of provisioning, see Provision a SQL virtual machine in the Azure portal.
The following screenshots are from the SQL virtual machines resource within the Azure portal. 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.
For existing SQL virtual machines, open your SQL virtual machines resource and select Security under Settings. Select Enable to enable Azure Key Vault integration.
When you're finished, select the Apply button on the bottom of the Security page to save your changes.
The credential name we created here will be mapped to a SQL login later. This allows the SQL login to access the key vault.
You can also configure Key Vault integration by using a template. For more information, see Azure quickstart template for Azure Key Vault integration.
After enabling Azure Key Vault Integration, you can enable SQL Server encryption on your SQL VM. First, you will need to create an asymmetric key inside your key vault and a symmetric key within SQL Server on your VM. 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:
The following Transact-SQL scripts provide examples for each of these areas.
Prerequisites for examples
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_cred. 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 Azure_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 Azure_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;
Transparent Data Encryption (TDE)
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
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
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
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
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;
For more information on how to use these encryption features, see Using EKM with SQL Server Encryption Features.
Note that the steps in this article assume that you already have SQL Server running on an Azure virtual machine. If not, see Provision a SQL Server virtual machine in Azure. For other guidance on running SQL Server on Azure VMs, see SQL Server on Azure Virtual Machines overview.