Set up SQL Server TDE Extensible Key Management by using Azure Key Vault

Applies to: SQL Server - Windows only

In this article, you install and configure the SQL Server Connector for Azure Key Vault. Extensible Key Management is not supported for SQL Server on Linux.

Note

Microsoft Entra ID is the new name for Azure Active Directory (Azure AD). We are updating documentation at this time.

Prerequisites

Before you begin using Azure Key Vault with your SQL Server instance, be sure that you've met the following prerequisites:

Step 1: Set up a Microsoft Entra service principal

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:

    • Select the Microsoft Entra ID button.

      Screenshot of the Azure services pane.

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

      Screenshot of the All Azure services pane.

  2. Register an application with Microsoft Entra ID 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.)

    1. On the Overview page 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.

    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.

    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.

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

      Screenshot of the secret value.

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

      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.

Create a key vault by using the Azure portal

You can use the Azure portal to create the key vault and then add a Microsoft Entra principal to it.

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

    Screenshot of the Create a resource group pane.

  2. Create a key vault.

    On the Create key vault pane, select the Basics tab, enter the appropriate values, and then select Review + create.

    Screenshot of the Create key vault pane.

  3. On the Access policies pane, select Add Access Policy.

    Screenshot of the Add Access Policy link on the Access policies pane.

    Note

    SQL Server Connector for Azure Key Vault doesn't support Azure Key Vault access using RBAC.

  4. On the Add access policy pane, do the following:

    1. In the Configure from template (optional) drop-down list, select Key Management.

    2. In the left pane, select the Key permissions tab, and then verify the Get, List, Unwrap Key, and Wrap Key check boxes are selected.

    3. Select Add.

      Screenshot of the Add access policy pane.

  5. In the left pane, select the Select principal tab, and then do the following:

    1. In the Principal pane, under Select, start typing the name of your Microsoft Entra application and then, in the results list, select the application you want to add.

      Screenshot of application search box on the Principal pane.

    2. Select the Select button to add the principal to your key vault.

      Screenshot of the Select button on the Principal pane.

    3. At the lower left, select Add to save your changes.

      Screenshot of the Add button on the Add access policy pane.

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

    Screenshot of the Create Key pane.

  7. On the Access policies pane, select Save.

    Screenshot of the Save button on the Add access policy pane.

Best practices

To ensure quick key recovery and be able to access your data outside of Azure, we recommend the following best practices:

  • Create your encryption key locally on a local hardware security module (HSM) device. Be sure to use an asymmetric RSA 2048 or 3072 key so that it's supported by SQL Server.

  • Import the encryption key to your Azure key vault. This process is described in the next sections.

  • Before you use the key in your Azure key vault for the first time, do an Azure key vault key backup using the Backup-AzureKeyVaultKey PowerShell cmdlet.

  • Whenever you make any changes to the key (for example, adding ACLs, tags, or key attributes), be sure to do 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.

    Using the SQL Server Connector for Azure Key Vault behind a firewall or proxy server can affect performance if traffic is delayed or blocked. Familiarize yourself with Access Azure Key Vault behind a firewall to ensure the correct rules are in place.

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.

Note

  • SQL Server Connector versions 1.0.0.440 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 1.0.3.0, the SQL Server Connector reports relevant error messages to the Windows event logs for troubleshooting.
  • Starting with version 1.0.4.0, there is support for private Azure clouds, including Azure operated by 21Vianet, Azure Germany, and Azure Government.
  • There is a breaking change in version 1.0.5.0 in terms of the thumbprint algorithm. You may experience database restore failures after upgrading to 1.0.5.0. For more information, see KB article 447099.
  • Starting with version 1.0.5.0 (TimeStamp: September 2020), the SQL Server Connector supports filtering messages and network request retry logic.
  • Starting with updated version 1.0.5.0 (TimeStamp: November 2020), the SQL Server Connector supports RSA 2048, RSA 3072, RSA-HSM 2048 and RSA-HSM 3072 keys.
  • SQL Server Connector supports Azure Key Vault access using Access Policies only and not RBAC.

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

Step 4: Add registry key to support EKM provider

Warning

Modifying the registry should be performed by users that know exactly what they are doing. Serious problems might occur if you modify the registry incorrectly. For added protection, back up the registry before you modify it. You can restore the registry if a problem occurs.

  1. Make sure that SQL Server is installed and running.

  2. Run regedit to open the Registry Editor.

  3. Create a SQL Server Cryptographic Provider registry key on HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft. The full path is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQL Server Cryptographic Provider.

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

  5. Give Full Control permissions on the SQL Server Cryptographic Provider registry key to the user account running the SQL Server service.

    Screenshot of the EKM registry key in Registry Editor.

  6. Select Apply and then OK.

  7. 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, you must complete an additional step to add HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQL Server Cryptographic Provider to the Cluster Registry Checkpoint routine, so the registry can sync across the nodes. Syncing facilitates database recovery after failover and key rotation.

    To add the registry key to the Cluster Registry Checkpoint routine, in PowerShell, run the following command:

    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.

Configure the master database

  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.

    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's 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:

    • Edit the IDENTITY argument (ContosoEKMKeyVault) 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 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.
    • Replace the first part of the SECRET argument with the Microsoft Entra Client ID from Step 1: Set up a Microsoft Entra service principal. In this example, the Client ID is 9A57CBC54C4C40E2B517EA677E0EFA00.

      Important

      Be sure to remove the hyphens from the App (Client) ID.

    • Complete the second part of the SECRET argument with Client Secret from Step 1: Set up a Microsoft Entra service principal. In this example, the Client Secret is 08:k?[:XEZFxcwIPvVVZhTjHWXm7w1?m. The final string for the SECRET argument will be a long sequence of letters and numbers, without hyphens (except for the Client Secret section, in case the Client Secret contains any 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 Microsoft Azure operated by 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 CREDENTIAL argument and programmatically removing the hyphens from the Client ID, see CREATE CREDENTIAL (Transact-SQL).

  5. 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 need to open the key. Open it by providing your key name in the following Transact-SQL script.

    Important

    Be sure to first complete the Registry prerequisites for this step.

    • Replace EKMSampleASYKey with the name you'd like the key to have in SQL Server.
    • Replace ContosoRSAKey0 with 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;
    

    Beginning with updated version 1.0.5.0 of the SQL Server connector, you can refer to a specific key version in the Azure key vault:

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

    In the preceding example script, 1a4d3b9b393c4678831ccc60def75379 represents the specific version of the key that will be used. If you use this script, it doesn't matter if you update the key with a new version. The key version (for example) 1a4d3b9b393c4678831ccc60def75379 will always be used 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 will associate the asymmetric key to this login
    CREATE LOGIN TDE_Login
    FROM ASYMMETRIC KEY EKMSampleASYKey;
    
  7. Create a new login from the asymmetric key in SQL Server. Drop the credential mapping from Step 5: 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;
    
  8. 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;
    

Configure the user database to be encrypted

  1. 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;
    
  2. Create a database encryption key by using the ASYMMETRIC KEY (EKMSampleASYKey).

    USE <DB Name>;
    --Create an ENCRYPTION KEY using the ASYMMETRIC KEY (EKMSampleASYKey)
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER ASYMMETRIC KEY EKMSampleASYKey;
    
  3. Encrypt the test database. Enable TDE by setting ENCRYPTION ON.

    --Enable TDE by setting ENCRYPTION ON
    ALTER DATABASE TestTDE
    SET ENCRYPTION ON;
    

Registry details

  1. Execute the following Transact-SQL query in the master database to show the asymmetric key used.

    SELECT name, algorithm_desc, thumbprint FROM sys.asymmetric_keys;
    

    The statement returns:

    name            algorithm_desc    thumbprint
    EKMSampleASYKey RSA_2048          <key thumbprint>
    
  2. In the user database (TestTDE), execute the following Transact-SQL query to show the encryption key used.

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

    The statement returns:

    encryptor_type encryption_state_desc encryptor_thumbprint
    ASYMMETRIC KEY ENCRYPTED             <key thumbprint>
    

Clean up

  1. Clean up the test objects. Delete all the objects that were created in this test script.

    -- CLEAN UP
    USE master;
    GO
    ALTER DATABASE [TestTDE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [TestTDE];
    GO
    
    ALTER LOGIN [TDE_Login] DROP CREDENTIAL [sysadmin_ekm_cred];
    DROP LOGIN [TDE_Login];
    GO
    
    DROP CREDENTIAL [sysadmin_ekm_cred];
    GO
    
    USE master;
    GO
    DROP ASYMMETRIC KEY [EKMSampleASYKey];
    DROP CRYPTOGRAPHIC PROVIDER [AzureKeyVault_EKM];
    GO
    

For sample scripts, see the blog at SQL Server Transparent Data Encryption and Extensible Key Management with Azure Key Vault.

  1. The SQL Server Cryptographic Provider registry key isn't cleaned up automatically after a key or all EKM keys are deleted. It must be cleaned up manually. Cleaning the registry key should be done with extreme caution, since cleaning the registry prematurely can break the EKM functionality. To clean up the registry key, delete the SQL Server Cryptographic Provider registry key on HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft.

Troubleshooting

If the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQL Server Cryptographic Provider isn't created or the required permissions aren't granted, the following DDL statement will fail:

CREATE ASYMMETRIC KEY EKMSampleASYKey
FROM PROVIDER [AzureKeyVault_EKM]
WITH PROVIDER_KEY_NAME = 'ContosoRSAKey0',
CREATION_DISPOSITION = OPEN_EXISTING;
Msg 33049, Level 16, State 2, Line 65
Key with name 'ContosoRSAKey0' does not exist in the provider or access is denied. Provider error code: 2058.  (Provider Error - No explanation is available, consult EKM Provider for details)

Client secrets that are about to expire

If the credential has a client secret that is about to expire, a new secret can be assigned to the credential.

  1. Update the secret originally created in Step 1: Set up a Microsoft Entra service principal.

  2. Alter the credential using the same identity and new secret using the following code. Replace <New Secret> with your new secret:

    ALTER CREDENTIAL sysadmin_ekm_cred
    WITH IDENTITY = 'ContosoEKMKeyVault',
    SECRET = '<New Secret>';
    
  3. Restart the SQL Server service.

Note

If you are using EKM in an availability group (AG), you will need to alter the credential and restart the SQL Server service on all nodes of the AG.

Rotate asymmetric key with a new AKV key or a new AKV key version

SQL Server doesn't have a mechanism to automatically rotate the certificate or asymmetric key used for TDE. The steps to rotate an asymmetric key manually are as follows.

  1. Create NEW_ASYMMETRIC_KEY pointing to same AKV key (points to most recent valid key version) or a new AKV key.

  2. Create a new login from the new asymmetric key:

    CREATE LOGIN TDE_LOGIN_NEW FROM ASYMMETRIC KEY NEW_ASYMMETRIC_KEY;
    
  3. Map AKV credential to the new login:

    ALTER LOGIN TDE_LOGIN_NEW;
    ADD CREDENTIAL AKV_Credential;
    
  4. Alter the database encryption key (DEK) to re-encrypt with the new asymmetric key:

    ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER ASYMMETRIC KEY NEW_ASYMMETRIC_KEY;
    

Note

Rotating the logical TDE protector for a server means switching to a new asymmetric key or certificate that protects the database encryption key (DEK). Key rotation is an online operation and should only take a few seconds to complete, because this only decrypts and re-encrypts the DEK, not the entire database.

Important

Don't delete previous versions of the key after rotation. When keys are rotated, some data is still encrypted with the previous keys, such as older database backups, backed-up log files, and transaction log files.

Next steps