Share via

Using the Key Vault for SQL Server Encryption

  • Article

Welcome!

In our first blog Azure Key Vault - Making the cloud safer, we introduced you to the Azure Key Vault and then in the second blog Azure Key Vault – Step by Step we got your hands dirty in setting one up for yourself. If you were on the more adventurous side, we even got you to build a Key Vault enlightened application using our simple sample project. Today we are going to introduce you to using the Key Vault with Microsoft SQL Server in an Azure Virtual Machine. We suggest that you review the previous blogs in this series before moving on.

Along with the Key Vault preview release, we are previewing the SQL Server Connector . It is available as a download now on the Microsoft Download Center (more on the setup below). This connector enables SQL Server encryption to leverage the Key Vault as an Extensible Key Management (EKM) module for more secure key protection. Sunil will discuss how these two products combined enable SQL Server applications with sensitive data to move into the cloud. You will be setting up both an Azure Key Vault and an Azure VM running SQL Server. Have fun!

As always, thanks for your support!

   Dan (twitter) on behalf of the Azure Key Vault Team

We value your input so please take a moment to follow us , join our advisory board, send us private feedback, and/or visit our forum .

 

Hi, Sunil here. I’m a Principal Program Manager on the Azure Security team. I’ll setup a bit of context and then we will quickly dive into real work configurations.

Today, organizations can use SQL Server encryption to protect sensitive data at rest. SQL Server encryption includes transparent data encryption (TDE), column level encryption (CLE), and backup encryption. In all of these cases the data is encrypted using a symmetric data encryption key. The symmetric data encryption key is further protected by using a hierarchy of keys on the SQL Server machine. Alternatively, the EKM provider architecture lets SQL Server use an asymmetric key stored outside of SQL Server in an external cryptographic provider to protect its symmetric data encryption keys. This adds additional security by allowing organizations to implement separation of duties in the management of keys and data.

This is where Azure Key Vault comes in. Given all the benefits it provides, we have wired up the Azure Key Vault as an EKM module using the SQL Server Connector. The connector thus permits SQL Server to use the Key Vault’s tightly controlled and monitored FIPS-validated Hardware security modules for higher level of protection for the all-important asymmetric keys used by the SQL Server TDE, CLE, and backup encryption features. It also enables IT to separate the management of keys from the management of SQL server data encryption. That is a pretty big deal!

Let’s next review how Contoso can use the Key Vault to protect its SQL Server workloads…

Contoso wants to be in cloud. They also want to preserve security over sensitive SQL Server workloads

Like most organizations, the Contoso IT team has a number of SQL Server Line of Business applications. Given the initial success of shifting applications to Azure, there is a desire to shift SQL Server workloads as well to Azure Virtual machines (VMs). The SQL Server databases on-premises in these sensitive workloads have been encrypted at rest in keeping with internal compliance requirements using encryption keys protected on the SQL Server machines. Contoso IT has always wanted to improve the security of this implementation by shifting the SQL Server encryption keys off-box to an EKM but have never setup HSMs on premises to achieve this. With the move to Azure Virtual machines, IT has however drawn a line in the sand and mandated this separation.

Contoso has two options for this:

  1. They can run their SQL Server in an Azure VM and install and use an on-premises HSM to store the master key, if that HSM vendor supports this configuration. There are some additional best practices to keep in mind to secure this configuration; but that is not the focus of this article.
  2. They can run their SQL Server in an Azure virtual machine and configure it to use Azure Key Vault to store the key. The rest of this article describes how to do that.

Contoso’s LOB application developer learns about the SQL Server Connector.  

The SQL Server developer, Sarah, knows of EKM. She reads about the Azure Key Vault service and the new SQL Server Connector for the Key Vault. The Extensible key management using Key Vault (SQL Server) topic helps her with a step-by-step guide on how to set up SQL Server Encryption using the Azure Key Vault as an EKM. The graphic below helps her understand all the moving parts:

Sarah next decides to prototype configuring an Azure VM hosting SQL Server with the SQL Server Connector for Microsoft Azure Key Vault to use the Azure Key Vault as an EKM provider. The steps she takes are pretty simple:

  1. Spins up a SQL Server 2014 Enterprise image on an Azure Virtual Machine in a matter of minutes and then loads a test database on this.

  2. Downloads and installs the SQL Server Connector on the virtual machine along with a set of sample scripts. The install also opens a help topic on the web.

  3. Uses the "EKM Setup Sample" script to register the connector with the SQL Server instance to enable the use of the Azure Key Vault as a cryptographic provider.

  4. Creates a new test key vault, cleverly named ‘SarahsTestVault’

  5. Creates an inexpensive software-protected asymmetric key in this new test vault.

  6. Registers the SQL Server instance with Azure AD, getting credentials to identify the instance to Azure AD.

  7. Uses this SQL Server instance Azure AD identity to authorize it to use the test vault giving it very specific permissions.

  8. Uses the "Setup Credentials" script provided to set up credentials in the SQL Server instance to be able to identify the instance to Azure AD and use the test vault.

  9. Finally, she uses the "TDE sample" script provided to open the asymmetric key in the test vault, create a database encryption key protected by this asymmetric key and turn on TDE for the database. 

  10. Voila, within 15-20 minutes she is done!

With transparent data encryption (TDE) enabled for the databases, Sarah feels confident that she has met the goals set for her by the IT leadership team. Knowing that the same connector also works with SQL Server column level encryption (CLE) and backup encryption as well, she can offer those services to the IT team as well. In fact, for her other application, she plans to leverage column level encryption (CLE) to protect Social Security Numbers given that is the only sensitive data in that database. Again in this situation, her CLE key protection will be delegated to the Azure Key Vault.

Contoso CIO gives the go-ahead for further testing

Impressed with the storyline of IT running SQL Server in an Azure VM while having its ‘root of trust’ managed by IT staff in the (more trustworthy) CSO’s organization, the CIO authorizes rapid migration of key workloads to the cloud. Deeper testing now begins.  

At this point the CSO formalizes a team of Vault Administrators for the Contoso High Business Impact (HBI) workloads. The CIO’s IT manager notifies his general IT staff about this new segregation of duty. The general IT team is happy to let the ‘security experts’ deal with the crypto stuff so they can get on with the real work of actually deploying, managing, and maintaining these High Business Impact (HBI) workloads in Azure. The teams begin their usual practice of ‘war games’ to see if anyone can get past their respective safety nets:

  1. IT sets up the Azure VM. They do not grant permission to the VMs, databases, and supporting services to anyone outside of their team.

  2. The CSO’s team sets up Vaults (and logs, when available) as well as asymmetric keys to use for SQL Encryption. They do not grant permission to the Vaults or logs to anyone outside of their team. 

  3. When both teams are ready, the IT staff registers their application in Azure AD and request the CSO’s team trust those applications to access the Vault. After this is done they turn on SQL Server Encryption.

All systems are now up. IT staff cannot see the keys used by their SQL Server VMs. They cannot abuse keys (without the logs indicating the abuse).

  1. One IT leader copies the database file and tries to mount it on another database server. They cannot decrypt it since they cannot use the key (unauthorized application).

  2. Another IT Leader tries to finds ways to export the all-important asymmetric keys and the Key Vault provider rejects this operation (key vault does not allow this).

  3. Another IT leader tries to delete the asymmetric key from the vault to render the databases unreadable and is again foiled by the vault (no permissions granted). 

  4. And so on and so forth….

  5. Finally, the CSO and IT do an incident drill and successfully revoke access for the application to the vault in case of a suspected compromise. 

The final production deployment

It is show time now! The same teams perform the same operations, just now they use HSM-backed keys to roll out the application in production. 

Hey, the cloud can even make my on premises offering better!

With all of this great learning, several of the IT staff realize that this implementation is far better than the standard operating procedures (SOPs) they had in place for on premises SQL Server workloads. Because they had not invested in HSMs so far, segregation of management of data and keys did not exist at all. With the success of using Azure Key Vault for SQL Server in Azure VMs, one of the IT staff suggests, “Why don’t we just use the Azure Key Vault for our remaining on premises workloads too!”? The argument being on premises databases could benefit from their TDE, CLE, and backup encryption keys being managed more securely in the Key Vault. No added risk was introduced in this model and tons of extra benefits leveraged the work they already did for the cloud-hosted workloads. It is a big win-win!!

We hope that you will agree with us when we say that the SQL Server Connector showcases some of the incredible benefits of using the Azure Key Vault. With that, we will close by offering links that will help you explore the SQL Server Connector for yourself:

Thank you for your interest and support! We are looking forward to your feedback. Please take a moment to send us feedback and to join our advisory board.

Also, watch out for our next blog that will cover how CloudLink SecureVM can be integrated with Azure Key Vault and help enhance data security for Azure Virtual Machines as well as for SQL Server Encryption.

   Sunil on behalf of the Azure Key Vault Team

Comments

  • Anonymous
    January 01, 2003
    Hello Bob,
    Azure SQL DB does not support TDE yet. You will have to use SQL IaaS if you want to use SQL Server with TDE today, and you can do that with Azure Key Vault or an on-premises HSM.

    Here is the voting link for this feature. You are certainly not the first to ask!http://feedback.azure.com/forums/217321-sql-database/suggestions/402425-enable-transparent-data-encryption
  • Anonymous
    January 01, 2003
    Hello Ada,
    Yes it does. The steps to connect and authenticate to the Azure Key Vault are the same.
  • Anonymous
    January 01, 2003
    Kalpana - Can you please send email to azurekeyvault@Microsoft.com? We will need more information to help troubleshoot your issue.
  • Anonymous
    January 22, 2015
    Is there any chance of Key Store being used to help encrypt data in SQL Azure?
  • Anonymous
    February 13, 2015
    thanks for the great article. one question - does azure key vault support on-premises sql servers (as long as the vault url can be reached by the sql server) or those located at other cloud providers? i cannot find a clear information on this subject anywhere.
  • Anonymous
    February 19, 2015
    thanks for this superb article. i have followed the same steps on-premises db but while trying to create Asymetric key i'm getting the error "Cannot open session for cryptographic provider' . why it is so?
  • Anonymous
    June 25, 2015
    Great article, we are looking to implement this in our Azure environment. Does this support in house AD on servers hosted in Azure or does it require AzureAD?
  • Anonymous
    June 25, 2015
    Nick - authentication to Key Vault requires Azure AD, which you get with your Azure subscription.
  • Anonymous
    June 25, 2015
    Nick (and others) - btw if you have further questions please use AzureKeyVault@microsoft.com or the MSDN forum for Key Vault. Those will get the quickest responses (this blog site doesn't alert us).
  • Anonymous
    August 19, 2015
    Today’s blog post comes courtesy of Sunil Pai and myself. As a SQL database administrator you know that
  • Anonymous
    October 27, 2015
    If you are currently using the SQL Server Connector to interface with your Azure Key Vault, please make