Rediger

Set up Transparent Data Encryption with Azure Key Vault for SQL Server

Applies to: SQL Server

In this article, you install and configure the SQL Server Connector for Azure Key Vault, and then configure Transparent Data Encryption (TDE) by using a key in Azure Key Vault.

Prerequisites

Before you begin using Azure Key Vault with your SQL Server instance, make sure that you meet the following prerequisites:

Note

In SQL Server 2022 (16.x) CU 12 and later versions, SQL Server on Linux supports TDE Extensible Key Management with Azure Key Vault. Steps 3 and 4 in this guide aren't required for SQL Server on Linux.

Quick flow

  1. Select an authentication model in Step 1: Set up the authentication model.
  2. Create a key vault and key in Step 2: Create a key vault.
  3. Install the connector in Step 3: Install the SQL Server Connector.
  4. Configure the registry prerequisite in Step 4: Add registry key to support EKM provider.
  5. Configure SQL Server and validate encryption in Step 5: Configure SQL Server.

Step 1: Set up the authentication model

Important

Choose your authentication model before you continue:

  • Use the Service principal tab for SQL Server on-premises.
  • Use the Managed identity tab for SQL Server on Azure VMs or SQL Server enabled by Azure Arc, where managed identity is supported.

Authentication model support matrix:

Authentication model SQL Server version Where SQL Server runs Supported
Service principal Supported versions covered by this article On-premises, Azure VM, SQL Server enabled by Azure Arc Yes
Managed identity SQL Server 2022 CU17 and later Azure VM Yes
Managed identity SQL Server 2025 and later SQL Server enabled by Azure Arc Yes
Managed identity Any On-premises No

To grant your SQL Server instance access permissions to your Azure key vault, you need a service principal account in Microsoft Entra ID.

  1. Sign in to the Azure portal, and do either of the following steps:

    • Select the Microsoft Entra ID button.

      Screenshot of the Azure services pane.

    • Select More services and then, in the All services pane, type Microsoft Entra ID.

  2. Register an application with Microsoft Entra ID by doing the following steps. For detailed step-by-step instructions, see the Get an identity for the application section of the Azure Key Vault blog post, Azure Key Vault – Step by Step.

    1. On the Manage section of your Microsoft Entra ID resource, select App registrations.

      Screenshot of the Microsoft Entra ID Overview page in the Azure portal.

    2. On the App registrations page, select New registration.

      Screenshot of the App registrations pane in the Azure portal.

    3. On the Register an application pane, enter the user-facing name for the app, and then select Register.

      Screenshot of the Register an application pane.

    4. In the left pane, select Certificates & secrets > Client secrets > New client secret.

      Screenshot of the Certificates & secrets pane for the App in the Azure portal.

    5. Under Add a client secret, enter a description and an appropriate expiration, and then select Add. You can't choose an expiration period greater than 24 months. For more information, see Add a client secret.

      Screenshot of the Add a client secret section for the App in the Azure portal.

    6. On the Certificates & secrets pane, under Value, select the Copy button next to the value of the client secret to use it to create an asymmetric key in SQL Server.

      Screenshot of the secret value in the Azure portal.

    7. In the left pane, select Overview and then, in the Application (client) ID box, copy the value to use it to create an asymmetric key in SQL Server.

      Screenshot of the Application (client) ID value on the Overview pane.

Step 2: Create a key vault

Select the method you want to use to create a key vault.

Note

Only Azure Key Vault and Azure Key Vault Managed HSM are supported. Azure Cloud HSM isn't supported.

Create a key vault by using the Azure portal

To create a key vault by using the Azure portal, see Quickstart: Create a key vault using the Azure portal.

Azure role-based access control

Use Azure role-based access control (RBAC) to manage access to the Azure Key Vault. Don't use legacy access policies. Legacy access policies have known security vulnerabilities, lack support for Privileged Identity Management (PIM), and shouldn't be used for critical data and workloads. For more information on Azure Key Vault RBAC permissions, see Azure built-in roles for Key Vault data plane operations.

  1. Go to the key vault resource that you created, and select the Access control (IAM) setting.

  2. Select Add > Add role assignment.

    Screenshot of the Add role assignment button on the Access control (IAM) pane in the Azure portal.

  3. The EKM application or managed identity needs the Key Vault Crypto Service Encryption User role to perform wrap and unwrap operations. Search for Key Vault Crypto Service Encryption User and select the role. Select Next.

    Screenshot of selecting a role assignment in the Azure portal.

  4. In the Members tab, select the Select members option, and then search for the Microsoft Entra application or managed identity that you created in Step 1. Select the application or managed identity and then the Select button.

    Screenshot of the Select members pane for adding a role assignment in the Azure portal.

  5. Select Review + assign twice to complete the role assignment.

Create a key

The user creating the key needs the Key Vault Administrator role. Just like the previous steps, add the member creating the key and assign the role.

  1. On the Key Vault pane, select Keys and then select the option Generate/Import. This action opens the Create a key pane. Select the Generate option, and enter a name for the key. 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.

  2. Use key type RSA and RSA key size as 2048. EKM currently only supports an RSA key. Set activation and expiration dates as appropriate and set Enabled as Yes.

    Screenshot of the Create Key pane.

Configure an Azure Key Vault Managed HSM (optional)

Azure Key Vault Managed HSM (Hardware Security Module) supports SQL Server and SQL Server on Azure Virtual Machines (VMs) when you use the latest version of the SQL Server Connector, and Azure SQL. Managed HSM is a fully managed, highly available, single-tenant HSM service. Managed HSM provides a secure foundation for cryptographic operations and key storage. Managed HSM is designed to meet the most stringent security and compliance requirements.

Step 2 shows how to create a key vault and key in Azure Key Vault. You can optionally use an Azure Key Vault Managed HSM to store or create a key for the SQL Server Connector. Follow these steps:

  1. Create an Azure Key Vault Managed HSM by using the Azure portal, the Azure CLI, PowerShell, or an ARM template.

  2. Activate the Managed HSM. Only the designated administrators assigned during creation can activate it. In the Azure portal, select the Managed HSM resource, and then select Download Security Domain in the Overview menu. Follow one of the quickstarts to activate your Managed HSM.

  3. Grant permissions for the Microsoft Entra service principal or managed identity to access the Managed HSM. The Managed HSM Administrator role doesn't give permissions to create a key. Similar to step 2, the EKM application or managed identity needs the Managed HSM Crypto User or Managed HSM Crypto Service Encryption User role to perform wrap and unwrap operations. For more information, see Local RBAC built-in roles for Managed HSM.

  4. In the Azure Key Vault Managed HSM service menu, under Setting, select Keys. In the Keys window, select Generate/Import/Restore Backup to create a key or import an existing key.

    Note

    Algorithms RSA-HSM_2048 and RSA-HSM_3072 are supported starting in SQL Server 2022 (16.x) Cumulative Update 13.

    Azure Key Vault Managed HSM supports automatic key rotation. For more information, see Configure key auto-rotation in Azure Managed HSM.

    Managed HSM supports private endpoint connections. For more information, see Integrate Managed HSM with Azure Private Link. In this configuration, you must enable the Microsoft trusted service bypass option in the Azure Key Vault Managed HSM Networking setting.

Step 3: Install the SQL Server Connector

Have a SQL Server administrator download the latest version of the SQL Server Connector for Microsoft Azure Key Vault from the Microsoft Download Center and run the installer.

Screenshot of the SQL Server Connector installation wizard.

By default, the Connector is installed at C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault. You can change this location during setup. If you change it, adjust the scripts in the next section.

A successful installation places Microsoft.AzureKeyVaultService.EKM.dll on the machine. This assembly is the cryptographic EKM provider DLL. Register it with SQL Server by using the CREATE CRYPTOGRAPHIC PROVIDER statement.

The installer also offers sample scripts for SQL Server encryption.

For error code explanations, configuration settings, or maintenance tasks, see:

Step 4: Add registry key to support EKM provider

Warning

Only a SQL Server administrator who knows exactly what they're doing should modify the registry. Incorrect changes can cause serious problems. Back up the registry before making any changes so you can restore it if a problem occurs.

  1. Run regedit to open the Registry Editor.

  2. Create a SQL Server Cryptographic Provider registry key at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQL Server Cryptographic Provider.

  3. Right-click the SQL Server Cryptographic Provider registry key, and then select Permissions.

  4. Grant Full Control on the SQL Server Cryptographic Provider key to the user account running the SQL Server service.

    Screenshot of the EKM registry key in Registry Editor.

  5. Select Apply and then OK.

  6. Close Registry Editor and restart the SQL Server service.

    Note

    If you use TDE with EKM or Azure Key Vault on a failover cluster instance, also add HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQL Server Cryptographic Provider to the Cluster Registry Checkpoint routine so the registry syncs across nodes, which facilitates database recovery after failover and key rotation.

    Run the following PowerShell command to add the registry key to the checkpoint routine:

    Add-ClusterCheckpoint -RegistryCheckpoint "SOFTWARE\Microsoft\SQL Server Cryptographic Provider" -Resourcename "SQL Server"

Step 5: Configure SQL Server

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

Phase 1: Configure cryptographic provider and credentials in master

Choose your authentication model and follow the matching steps.

  1. Run sqlcmd or open SQL Server Management Studio.

  2. 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;

  3. 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 AzureKeyVault_EKM.

    CREATE CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM
FROM FILE = 'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\Microsoft.AzureKeyVaultService.EKM.dll';
GO

    Note

    The file path length can't exceed 256 characters.

  4. Set up a SQL Server credential for a SQL Server login to use the key vault.

    Add a credential to each login that performs encryption by using a key from the key vault. There's a one-to-one mapping between credentials and logins - each login must have a unique credential.

    Modify this Transact-SQL script in the following ways:

    • Edit the IDENTITY argument (DocsSampleEKMKeyVault) 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 Step 2: Create a key vault.
      • If you're using a private Azure cloud (for example, Azure Government, Microsoft Azure operated by 21Vianet, or Azure Germany), replace the IDENTITY argument with the Vault URI returned in Create a key vault and key by using PowerShell. Don't include https:// in the key vault URI.

    • Replace the first part of the SECRET argument with the Microsoft Entra Client ID from Step 1: Set up the authentication model. In this example, the Client ID is d956f6b9xxxxxxx.

      Important

      Remove the hyphens from the App (Client) ID.

    • Complete the second part of the SECRET argument with the Client Secret from Step 1. The final string is a long sequence of letters and numbers without hyphens (except for any hyphens in the Client Secret itself).

    USE master;
CREATE CREDENTIAL sysadmin_ekm_cred
   -- Set IDENTITY to the vault name (public Azure) or full vault hostname without https:// (sovereign clouds / Managed HSM)
   -- See https://dotnet.territoriali.olinfo.it/azure/key-vault/general/about-keys-secrets-certificates#dns-suffixes-for-base-url
   WITH IDENTITY = 'DocsSampleEKMKeyVault',
         --<----Application (Client) ID ---><--Microsoft Entra app (Client) ID secret-->
   SECRET = 'd956f6b9xxxxxxxyrA8X~PldtMCvUZPxxxxxxxx'
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 and programmatically removing hyphens from the Client ID, see CREATE CREDENTIAL.

  5. Open the Azure Key Vault key in your SQL Server instance.

    Whether you created a new key or imported an asymmetric key in Step 2: Create a key vault, open the key in SQL Server by using CREATE ASYMMETRIC KEY.

    Important

    Complete the registry prerequisites before you perform this step.

    In the following examples:

    • Replace EKMSampleASYKey with the name that you want to use in SQL Server.
    • Replace ContosoRSAKey0 with your key name in Azure Key Vault or Managed HSM.

    Use a versionless key name (recommended for most scenarios):

    CREATE ASYMMETRIC KEY EKMSampleASYKey
   FROM PROVIDER [AzureKeyVault_EKM]
   WITH PROVIDER_KEY_NAME = 'ContosoRSAKey0',
      CREATION_DISPOSITION = OPEN_EXISTING;

    Use a specific key version when you need to pin operations to one version:

    CREATE ASYMMETRIC KEY EKMSampleASYKey
   FROM PROVIDER [AzureKeyVault_EKM]
   WITH PROVIDER_KEY_NAME = 'ContosoRSAKey0/1a4d3b9b393c4678831ccc60def75379',
      CREATION_DISPOSITION = OPEN_EXISTING;

    In this example, 1a4d3b9b393c4678831ccc60def75379 is the specific key version that SQL Server uses for database operations.

  6. Create a new login by using the asymmetric key in SQL Server that you created in the preceding step.

    -- Create a login that associates the asymmetric key with this login
CREATE LOGIN TDE_Login
    FROM ASYMMETRIC KEY EKMSampleASYKey;

  7. Move the credential mapping from the original administrator login to the login created from the asymmetric key.

    SQL Server uses the login created from the asymmetric key (TDE_Login) for EKM operations. To ensure SQL Server can access Azure Key Vault during encryption and recovery operations, map the credential to TDE_Login instead of the original setup login.

    -- Remove the service principal credential from the original setup login
ALTER LOGIN [<domain>\<login>]
   DROP CREDENTIAL sysadmin_ekm_cred;

-- Map the service principal credential to the login created from the asymmetric key
ALTER LOGIN TDE_Login
   ADD CREDENTIAL sysadmin_ekm_cred;

Phase 2: Encrypt and validate the user database

Configure the user database to be encrypted

  1. Create a test database to encrypt by using the Azure Key Vault key.

    -- Create a test database for the TDE example.
CREATE DATABASE TestTDE;

  2. Create a database encryption key by using the server asymmetric key (EKMSampleASYKey).

    USE TestTDE;
-- Create a DEK protected by the EKM asymmetric key.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY EKMSampleASYKey;

  3. Enable TDE on the database by setting ENCRYPTION ON.

    -- Enable TDE for the database.
ALTER DATABASE TestTDE
    SET ENCRYPTION ON;

Verify key usage and encryption state

  1. In the master database, execute the following Transact-SQL query to verify that the EKM asymmetric key exists and capture its thumbprint.

    SELECT name,
       algorithm_desc,
       thumbprint
FROM sys.asymmetric_keys;

    The statement returns output similar to the following:

    name            algorithm_desc    thumbprint
EKMSampleASYKey RSA_2048          <key thumbprint>

  2. In the user database (TestTDE), execute the following Transact-SQL query to verify that TDE is enabled and that the database encryption key is protected by an asymmetric key.

    SELECT encryptor_type,
       encryption_state_desc,
       encryptor_thumbprint
FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID('TestTDE');

    The statement returns output similar to the following:

    encryptor_type encryption_state_desc encryptor_thumbprint
ASYMMETRIC KEY ENCRYPTED             <key thumbprint>

    Confirm that encryptor_thumbprint matches the thumbprint returned from sys.asymmetric_keys. A mismatch usually indicates that the database encryption key is protected by a different key than expected.

Clean up

Use the cleanup steps that match the authentication model you configured.

  1. Clean up the test objects you created in this procedure.

    -- CLEAN UP: shared objects + service principal credential
USE master;
GO
ALTER DATABASE [TestTDE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [TestTDE];
GO

DROP LOGIN [TDE_Login];
GO

DROP ASYMMETRIC KEY [EKMSampleASYKey];
DROP CRYPTOGRAPHIC PROVIDER [AzureKeyVault_EKM];
GO

DROP CREDENTIAL [sysadmin_ekm_cred];
GO

  2. Review whether you should remove the SQL Server Cryptographic Provider registry key.

    Important

    The SQL Server Cryptographic Provider registry key isn't removed automatically after you delete EKM keys.

    Delete this registry key only when you no longer need EKM on the instance. Deleting it too early can break EKM functionality and recovery operations.

    Registry path: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQL Server Cryptographic Provider

Rotate asymmetric keys for TDE with Azure Key Vault

Use a dedicated operational guide for key rotation steps, including authentication-specific scripts, verification, and safety checks:

Important

Don't delete previous versions of the key after rotation. Earlier versions might still be required for restoring older backups, log files, and recovery artifacts.

Related content

  • Last updated on