Prashanth Jayaram

How to Configure a TDE database with AlwaysOn using the Azure Key Vault in SQL Server 2016

July 28, 2017 by

One of the recent tasks that I have undertaken seemed rather like an experiment to me. I set out to configure Transparent Data encryption (TDE) using asymmetric key protection with Azure Key Vault with AlwaysOn. That, to me, opened a whole different dimension on data security.

I must admit that at first, the task seemed daunting. But when I took the plunge, given that I had the key details with me, the process was surprisingly straightforward.

Before we proceed, please go through this MSDN article on SQL Server Transparent Data Encryption (TDE) using Azure Key Vault in order to understand the basics of Azure Key Vault configuration and Integration. That would serve as a base, which this article can build upon.

Background

The databases encryption can also be implemented with asymmetric key (separate keys for encryption and decryption) using third-party provider interface named Extensible Key Management (EKM), also known as EKM provider. Encryption of the database with an asymmetric key using an EKM provider is another feature available in SQL Server that adds the ability to store the encryption keys used for TDE on a hardware module, specifically designed for key security and management—think of this as a safe key chest. Such devices are called High-Security Modules (HSM). Most EKM providers do sell HSMs.

This article talks about implementing TDE with asymmetric key with AlwaysOn using SQL Connector, which is in turn implemented using Azure Key Vault; the EKM provider. SQL Connector needs to be installed and registered to the SQL Server database to enable TDE. Key Management vendors should ideally provide a platform with a rich set of software libraries and tools, which could easily handle the encryption key management tasks as an extension to the SQL Server database.

Azure Key Vault

Azure Key Vault is a secure key management feature that is essential to secure and protect data in the Azure cloud. We use Azure Key Vault to encrypt keys and small secrets like passwords that use keys stored in hardware high-security modules (HSMs). For added assurance, import or generate keys in HSMs, and let Microsoft process your keys in FIPS 140-2 Level 2 validated HSMs (hardware and firmware).

Introduction

SQL Server running on an Azure Virtual Machine can use an asymmetric key from the Key Vault. When running SQL Server in an Azure VM, SQL Server can use keys stored in the Azure Key Vault using EKM. This article covers the role of a DBA in setting up SQL Server 2016 TDE database on Azure VM with AlwaysOn, using the Azure Key Vault. It also outlines the prerequisites and required details for seamless implementation of protecting the data using an asymmetric key.

Let’s proceed, with the assumption that we have the necessary details from the Azure Key Manager such as:

  • KeyValutName
  • Active Directory Application Client ID
  • Active Directory Client Secret
  • Key Encryption Key Name (KEK)

Let’s also add another set of assumptions from the DBA side:

  • Always-On Availability Group site that is already configured

For example,

Name Description Value
ResourceGroupName Key Vault Resource Group Name az-prod-sql-001
VaultName Key Vault Name az-kv-sql-001
AADApp AAD Application Name az-aadapp-kv-001
AADObjectID Azure Active Directory Application Client ID 2db602bd-4x4x-4322-8xxf-d128c143c8a9
AADClientSecret Active Directory Client Secret FZCzXY3K8RpZoK12MxF/WFxxAw6aOxxPU2ix
xEkQBbc=
Secret The SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together 2db602bd4x4x43228xxfd128c143c8a9FZCz
XY3K8RpZoK12MxF/WFxxAw6aOxxPU2ixxEkQBbc=
Key Encryption
Key Name
Key Encryption Key Name (KEK) az-kek-sql-001

SQL Server Connector

The SQL Server Connector for Microsoft Azure Key Vault enables SQL Server encryption to use the Azure Key Vault service as an Extensible Key Management (EKM) provider to protect SQL Server encryption keys.

Download the SQL Server Connector from the Microsoft Download Center, and follow the steps below to complete the installation.

  • Browse the installation folder
    • The Connector, by default, gets installed at C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault
    • The location of the cryptographic EKM provider DLL can be changed, provided we ensure to adjust the path parameter in the CREATE CRYPTOGRAPHIC PROVIDER  statement, which is discussed below
  • Click Next
  • Click the agree terms and license agreement radio button
  • Select the location for libraries to install
  • Click Next and then, Finish

The SQL Server Connector installation also allows you to optionally download sample scripts to help with SQL Server encryption.

Configure SQL Server

In this section, we shall see how we could add a TDE-encrypted database to AlwaysOn Availability Group site that is already configured. Assume that we have configured the AlwaysOn Availability Group SQLAG2 that contains three replicas.

In the following screenshot, iServerReportingDB is going to be configure for TDE. Let’s see the steps to configure TDE and use of EKM on AlwaysOn setup.

On the Principal

  • Setup EKM
  • Setup Credentials
  • Configure TDE
  • Add database to AlwaysOn availability group
  • Perform full database backup
  • Perform log backup

On the Secondary, the steps are a little different from the procedure above

  • Create the database
  • Setup EKM
  • Setup credentials
  • Configure TDE
  • Restore the database with replace option
  • Restore the log
  • Join the database to AlwaysOn availability group

Let’s look at all of the above steps in detail and configure TDE with asymmetric key on the Availability Group using Azure Vault

Check the database’s encryption configuration

This indicates that no databases are configured for TDE.

Step 1: EKM Setup

Steps to create the cryptographic provider

  • The Connector, EKM provider for the Azure Key Vault, and the DLL registered with SQL Server
  • The below sample uses AzureKey Vault_EKM_Provider as the name.
  • The DLL file location is where you installed the SQL Connector. In our case, it’s the default location.

STEP 2: Setup Credentials

Create a credential from Azure Active Directory (AAD) Client ID and Secret that you can use to grant an SQL Server account access to your Azure key vault

  • The value of IDENTITY here is the name of your Azure key vault.
  • SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together

  • Add the credentials to the SQL Server administrator’s domain login.


    STEP 3: Create asymmetric Key and SQL Login

    • Use the EKM to open the asymmetric KEK
    • Give the Key Encryption Key a name

    Step 4: Create SQL Server Login





    Enable TDE on the SQL Database by Using Transact-SQL

    • Connect to the master database
    • Execute the following SQL statement to encrypt the database


    • Monitor the progress of encryption


    STEP 5: Add Database to an AlwaysOn Group

    On the primary, add the database to the Availability Group using ALTER AVAILABILITY GROUPADD DATABASE


    Backup the database (Full and Log backup)

    • Initiate full and log backup on the primary replica using the following SQL statements:


    On the secondary

    • Create the database iServerReportingDB
    • Move the database backup file to a location where AlwaysOn Replica #1 can restore the file
    • Run steps 1 through 4 from the last section
    • Repeat the process for each AlwaysOn Replica node
    • Restore the full database backup

    • Restore the log database backup

    • Join the database to the availability group using the ALTER AVAILABILITY GROUP T-SQL

    The AlwaysOn Group status

    The below screenshot shows that iServerReportingDB is configured successfully with EKM provider, Azure Key vault.

    Conclusion

    In an Always-On-with-Azure-Key-Vault scenario, enabling TDE on one or more secondary replicas is indeed a tedious task. One has to get the help of Azure Key Manager to get all the required key information.

    This article articulates every step required to setup and configure asymmetric TDE with AlwaysOn using Azure Key Vault. If you are able to configure the setup on Primary then similar steps should be applied to Secondary, with a few steps being different (which are also covered in this article). You will be performing a backup and adding the database to Availability Group in case of the former, whereas you would restore the database and join the database to the Availability Group in the latter: the secondary nodes.

    See more

    Consider these Free tools and community editions for Azure SQL Database

    References



    Prashanth Jayaram

    Prashanth Jayaram

    I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

    My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

    View all posts by Prashanth Jayaram
    Prashanth Jayaram
SQL Azure, SQL Server 2016

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

599 Views