Some organisations are more strict on security than others. Thats just the way of the world, whether it be local policy, industry policy, paranoia or worryingly...just not considering it a priority.
This is why Microsoft have to offer BYOK, no, not the famous Icelandic singer from the 90's and beyond either. I'm (very) tenuously referring to "Bring Your Own Key" which allows customers to let the encryption key to be handled by Microsoft for their encryption purposes, but create and use one of their own.
This is also required for functionality such as performing manual copy only backups on SQL Managed Instance Databases that have TDE, so your hand may be forced.
I've come across this more than once recently, so thought i'd share my homework. This post isn't for saying that using customer keys is bad or anything like that, but more for highlighting a couple of rather unpleasant "gotcha's" if you go down this route.
Worrying lines aside, lets get started. The below holds true for both Azure SQL Managed Instance and Azure SQL DB, in the examples below, we're going to be using it for SQL Transparent Data Encryption. (TDE)
First off, we need somewhere to store our keys, a vault of some sort. Oh look,
Key Vaults! Thats convenient.
Key Vault (AKV)
This can be created either from the portal 'clickey style' or with a bit of powershell as below which i'll be using. (With all the PS here, i've assumed you've Installed the Az powershell module, signed in and created your resource groups.)
There are some rules that have to be followed however if you are using AKV for SQL integration, these being:
Soft Delete must be enabled.
Purge Protection must be enabled.
New-AzKeyVault -Name AVKPrimary -ResourceGroupName SQLResources -Location UKWest -EnablePurgeProtection -EnabledForTemplateDeployment -EnabledForDeploymentCool, nice and simple so far. I've included a couple of additional switches which may prove useful in future:
EnabledForDeployment - Allows the Microsoft.Compute resource to access the vault when creating resources
EnabledForTemplateDeployment - Allows ARM (Azure Resource Manager) to access this fault when deploying from ARM templates.
You may not want these, but then again, you might.
Behind the scenes, Microsoft make this really quite robust, so it should always be available.
Key Vaults in most regions are replicated within Region AND to a secondary region at least 150miles away. In the case of the (cold/wet/miserable) UK, the partner regions are UK West and UK South.
The main points are:
In the event of a region failover, it may take a few minutes for the service to fail over. Requests made during this time before failover may fail.
If you're using private link to connect to your key vault, it may take up to 20 minutes for the connection to be re-established in the event of a failover.
During failover, your key vault is in read-only mode.
Partner Regions: Azure cross-region replication | Microsoft Learn
If you're in a region without a failover partner provided by Azure, then you will need multiple AVKs, and ensure HA is configured as required. Sorry, that's just how it is right now, but your weather is probably better than here anyway.
OK, OK, i'll follow up with another post for providing HA via multiple Key Vaults and doing that with Azure SQL DB/Managed Instance, just please stop hurting me....
You may even always want a Geo located secondary to always point to a Key Vault that is local to the DB its protecting, rather than traverse regions...see? Justified.
Keys
A vault is a bit pointless unless it has something to protect... so naturally we need a something to protect, in this case Keys.
Now I know what you're thinking, you use a key to unlock a vault usually... not here, the Keys are actually our valuables, so protect them with our lives etc.
In this case, its the Key that is used to lock up our SQL instances/DBs via TDE, so naturally this is what we REALLY need to keep hold of under pain of death.
In the Key Vault:
Again, you can create these through the portal via Generate/Import, or throw out a line of powershell like below.
Add-AzKeyVaultKey -VaultName AVKPrimary -Name SQLKey -Destination Software -KeyType RSA -Size 2048 You'll see from the above that the Key has to be RSA with a minimum length of 2048 for use with TDE, so that's how we've created it. The Software tag specifies that its exactly that, its not backed by a hardware (HSM) module.
You can add rotation options in to the key creation (the GUI options are quite straight forward so i'll not step through all of them), or choose split it out as below.
Key Rotation.
You may want to think about key rotation as well, (or your Security officers may think of it for you, no worries, we're all friends here) so that keys automatically generate new versions to keep things nicely secured. Microsoft have your back here as well.
Let's change our shiny new key to use an auto rotation policy of every 90 days.
Set-AzKeyVaultKeyRotationPolicy -VaultName AVKPrimary -KeyName SQLKey -KeyRotationLifetimeAction `
@{Action = "Rotate";TimeAfterCreate = (New-TimeSpan -Days 90)}Or have an expiry of 720 days, and rotation policy of 90 days.
Set-AzKeyVaultKeyRotationPolicy -VaultName AKVPrimary -KeyName SQLKey -ExpiresIn (New-TimeSpan -Days 720) -KeyRotationLifetimeAction `
@{Action = "Rotate";TimeAfterCreate = (New-TimeSpan -Days 90)}<Rob Schneider> You can do it! </ Rob Schneider>
Securables
So, we've done it, we now have our keys, and they are locked away in a vault. We just need something to secure with it now.
Lets create an SQL Azure Server.
New-AzSqlServer -ResourceGroupName SQLResources -Location UKWest -ServerName SQLServerPrimary -EnableActiveDirectoryOnlyAuthentication -ExternalAdminName "Bouncer Rod" -AssignIdentityThe only point to note here is the -AssignIdentity switch. What this does is create and assign a Microsoft Entra identity for the server, which allows it to be identified and permissioned with services, such as Key Vault.
There are two methods for allowing the server access to the Key Vault, i'll only be discussing one of those.
Vault Access Policy
Key Vault: check, Key: check, Server: check.
Now we have our server, we need to grant it access to the Key Vault, so that it can get at the key(s) it will be using to encrypt its databases. If you used the GUI to provision your Vault, then you'd have seen the access policy radio button, Role Based or Access Policy.
Clicking +Create, you select:
The permissions, The minimum required permissions are get, wrapKey and unwrapKey.
Then the Principal of the server, either the server name for an Assigned Entra system identity (or the User Assigned Identity if you use those).
Then Create.
Or... as moving a mouse and clicking is far too strenuous...
$serverprim=Get-AzSqlServer -ServerName SQLServerPrimary
Set-AzKeyVaultAccessPolicy -VaultName AKVPrimary -ObjectId $serverprim.Identity.PrincipalId -PermissionsToKeys get, wrapKey, unwrapKeyThis does the trick. The PrincipleID of the server (created from the -AssignIdentity switch above) now has access to the Key vault and keys. Marvellous.
So now our server has access to the Vault, we can configure it to use our lovingly crafted keys.
Under the Security Blade in the SQL Server - Transparent data Encryption. Choose customer-Managed Key, then select the Key you want, and if you want this key to be the default for TDE DBs, and if to rotate.
or....
$KeyPrim=Get-AzKeyVaultKey -VaultName AKVPrimary -Name SQLKey
Set-AzSqlServerTransparentDataEncryptionProtector -Type AzureKeyVault -ServerName SQLServerPrimary -ResourceGroup SQLResources -KeyId $keyPrim.id -AutoRotationEnabled $true -ForceSetting the key, and configuring the Server to use AutoRotation as well. -Force to save an additional prompt and subsequent mouse click/keyboard bash.
What, rotation again??? Yes, as you have rotating keys, and the server has to be configured for rotation as well. I'm getting dizzy....
Server Key AutoRotation
Ultimately, what this does is instruct the SQL Server to periodically check in with the Key vault to ensure it has the latest version of the key. So if the Key is rotated in the Vault, the server picks up on that and reencrypts keys to use this new version within 24 hours.
Key rotation is an online operation and should only take a few seconds to complete, because this only decrypts and re-encrypts the database's data encryption key, not the entire database.
Note: KEEP your older key versions within the Key Vault. They may be required for older restores of DBs that were using those older key versions. Old backups are not reencrypted with the new key versions.
That's it, you've now successfully set up BYOK for Azure SQL Server. Well done you.
Thats all the shiny, shinies complete, now for a dose of reality....
Pitfalls - Be afraid, be VERY afraid.
Vaults across Subscriptions
You may have the situation where you have Development resources in a different subscription? And you now want to restore a copy of a DB (Managed Instance) to a Development server, and Production is TDE protected by a customer key... doesn't sound too outrageous a demand does it? The truth is that you cannot back up a key from a Vault in one subscription, and restore it to a Vault in another subscription. (That sucks right?)
If you need to do this, you can generate your key outside of Key Vault, and import it into both. I'll detail how to do that in another follow up post.
Lost Vault Access
I'll say this a lot, but this is outrageously important. If a server loses access to a Vault (minor network blips are handled automatically, along with Azure Key Vault Outages, see above), that server can no longer access its keys, so the following will happen.
Within 10 mins of Key Vault inaccessible - TDE enabled DBs will change to Inaccessible.
If Key Vault Access is restored within 30mins - The DBs will autoheal within the next hour.
If Key access restored after 30mins, no autoheal will happen and requires extra steps in the portal to initiate the recovery process. - At the Server Level - Security blade - Transparent Data Encryption - REVALIDATE KEY. You can select an alternate key here also.
Depending on the size of the database(s), this could take hours.
And the really good bit? </sarcasm> THIS MAY LOSE SETTINGS!
ie... server level -- failover group config, tags
db level -- elastic pools, read scale, autopause, point in time history, long term retention settings.
Effectively, after 30min or greater key loss - validate all of your DB / SERVER and KEY settings.
Lets stop and review that for a moment. What would happen to your company if one or more of its critical DBs were not accessible for up to an hour, even after you reacted quickly? Possible Pain and suffering.
Ok, what happens if it was the middle of the night, and it went unnoticed for a while, then the keys needed revalidating, and then the DB was inaccessible for several hours?
<Bill Paxton> Game over man, game over! </ Bill Paxton>
Result? MDK courtesy of Simon Pheonix.
Ouch! The moral of this story, don't let your servers lose access to your vaults...ever. Seriously, I mean it.
But if you do lose that access/connectivity? You need to know when a DB is in trouble ASAP ...which means Monitoring. My favourite. I'll create another follow up post for how a database transitioning to Inaccessible appears, and how we can monitor and alert on it.
Summary
I'm not trying to discourage anyone from using Customer Keys at all. Many times, this decision is out of our grubby little hands anyway, so we have to do it. I just want to highlight some of the issues/restrictions/design implications that have to be taken into account when going down this path, that's all. Do it right, and you have ultimate control over the Encryption on your Azure DBs, but get it wrong, and you can easily back yourself into a painful corner.
I will also follow up with a set up for multiple Key Vaults, and a quick alternative method to provide rotation. Watch this space!.
Thanks for reading.
BSGA63BablDVmWFZnzF7qcNeS7dROmGiceyZHl7gjrc=
Oh sorry, Wrong encryption key...
Rod Edwards
Title Image courtesy of pikisuperstar https://www.freepik.com/author/pikisuperstar