Our customers often ask about encryption key management for the Microsoft SQL Azure Database on the Azure cloud. SQL Azure Database is the Microsoft Azure Database-as-a-Service offering based on SQL Server. It is a natural question because SQL Server has a convenient interface for plugging in a key management solution through their Extensible Key Management (EKM) interface. And our Alliance Key Manager has supported this for more than a decade and is available in the Azure marketplace.
Here’s the rub:
Unlike normal SQL Server, the Azure SQL Database offering does not support the normal SQL Server key management interface. It does support encryption of the database, but only by using the Microsoft Key Vault service. So Azure customers are locked out of managing and controlling the encryption keys when using SQL Azure Database.
This is not a problem with Azure itself! We have customers who have deployed SQL
Server in a virtual machine on Azure and use our Alliance Key Manager in Azure with no problems! Microsoft does not allow the use of a key manager and only allows the Azure Key Vault or a Bring Your Own Key (BYOK) option..
Is there anything you can do?
Sure! Let me describe one approach you can use in a web application that uses SQL Azure Database that gives you exclusive control and access to your encryption keys, and supports a real time mirroring of encryption keys to a key server outside of the cloud. And a bonus is that if you are mirroring data out of the cloud to an on-premise SQL Server database, the key management synchronization and failover will be automatic.
Here is what to do in Azure:
First, deploy Alliance Key Manager right from the Azure Marketplace. It will automatically license for a 30 day no-cost evaluation period (Azure charges may apply). When you access the key manager in Azure Marketplace you will have a link to documentation, and you will be eligible for technical support. Create an AES key to use for encrypting data in SQL Azure Database. Here is the quick start guide to help you get started:
https://docs.townsendsecurity.com/akm_for_microsoft_azure_quick_start_guide
Then, modify your Windows .NET application to make a call to Alliance Key Manager to encrypt or decrypt information using the AES key you created before you insert or update data in a column. Alliance Key Manager provides a simple Windows .NET SDK to make this easy. There is no charge for the SDK and you can download it from the Townsend Security website. Here is the link to the Windows .NET SDK:
https://docs.townsendsecurity.com/akm_guide_for_windows_dot_net_developers
Backups of the Azure SQL Database and all data you copy out of Azure will now be encrypted and under your control.
What to do in your data center:
You can easily mirror encryption keys from Azure to your own data center. Download Alliance Key Manager for VMware, launch it in your VMware environment, and set up mirroring between Alliance Key Manager in Azure and Alliance Key Manager in your data center. Keys are mirrored in real time and your on-premise applications can use the same logic as in the cloud to decrypt data as needed. Here is the VMware quick start guide:
https://docs.townsendsecurity.com/akm_for_vmware_quick_start_guide
Your applications in your on-premise deployment can now use the same Windows .NET SDK as mentioned above to do decryption when needed.
Voila!
You now have your data encrypted in SQL Azure Database, in your on-premise SQL Server database, and you have full control of your encryption keys! You also have a lot more flexibility about your choice of Cloud Service Providers.
A few more thoughts:
Triggers, UDFs and Stored Procedures
If modifying your applications is not feasible or costly, consider adding Triggers and Stored Procedures to the database to achieve encryption and decryption tasks. This can be much easier to implement than making code changes. See the resources below to get started.
How to implement User Defined Functions and Stored Procedures in Azure SQL Database:
And
https://www.sqlshack.com/executing-stored-procedures-from-data-pipelines-in-azure-data-factory/
And Alliance Key Manager provides guidance on Triggers and Stored Procedures:
https://docs.townsendsecurity.com/akm_guide_for_windows_dot_net_developers
Mirroring keys in the cloud
Sometimes you are not mirroring SQL Azure Database data to your on-premise database. If you have a backup strategy that involves failover to another Azure availability zone be aware that you can run a second copy of Alliance Key Manager in that zone. Alliance Key Manager will mirror encryption keys across any availability zones and regions.
Mirroring keys to AWS
If you really want to mirror your encryption keys out of the Azure cloud, but don’t want to bring the keys in-house, you can mirror them to AWS! Alliance Key Manager is also available in AWS and fully supports cross-cloud key mirroring.
