Configure Azure Key Vault integration for SQL Server on Azure VMs (Resource Manager)

Applies to: SQL Server on Azure VM

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:

1. Install Azure PowerShell
2. Create a Microsoft Entra tenant
3. Create a key vault

The following sections describe these prerequisites and the information you need to collect to later run the PowerShell cmdlets.

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 Microsoft Entra ID

First, you need to have an Microsoft Entra tenant 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 Microsoft Entra ID. This gives you a Service Principal account that has access to your key vault, which your VM needs. In the Azure Key Vault article, you can find these steps in the Register an application with Microsoft Entra ID 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 ClientID or AppID) on the Registered app pane. 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'll 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's 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 later, 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.

Enable and configure Key Vault integration

You can enable Key Vault integration during provisioning or configure it for existing VMs.

New 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.

For a detailed walkthrough of provisioning, see Provision a SQL virtual machine in the Azure portal.

Existing VMs

For existing SQL virtual machines, open your SQL virtual machines resource and select Security under Settings. Select Enable to enable Azure Key Vault integration.

The following screenshot shows how to enable Azure Key Vault in the portal for an existing SQL Server VM (this SQL Server instance uses a non-default port 1401):

When you're finished, select the Apply button on the bottom of the Security page to save your changes.

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:

• Transparent Data Encryption (TDE)
• Encrypted backups
• Column Level Encryption (CLE)

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)

1. 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. 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. 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
   

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

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 Overview of SQL Server on Azure Windows Virtual Machines.

Next steps

For more security information, review Security considerations for SQL Server on Azure VMs.