Implementing TDE in Azure SQL with Custom Managed Keys from Azure Key Vault

In today’s data-driven landscape, safeguarding sensitive information is paramount, especially when leveraging cloud technologies. Transparent Data Encryption (TDE) offers a robust solution by encrypting SQL Server data at rest, thus enhancing security by preventing unauthorized access. This blog post delves into implementing TDE on Azure SQL Database using custom managed keys stored within Azure Key Vault.

TDE is a technology that performs real-time encryption and decryption of the data and log files in SQL Server, Azure SQL Database, and Azure SQL Managed Instance. It ensures that data files are encrypted on the disk, enhancing security by adding a layer of protection that does not require changes to existing applications. TDE works by encrypting the storage of an entire database using a symmetric key called the Database Encryption Key (DEK). This key is then protected by a certificate stored in the server, or, in more secure environments, by an asymmetric key protected by an external key management system like Azure Key Vault.

The primary advantage of TDE is its transparency to applications accessing the database. Applications can run queries and process data as usual, with no need to modify existing queries or application code. This seamless approach not only simplifies the implementation of encryption but also minimizes the impact on performance. By securing data at rest without altering how data is accessed, TDE provides a straightforward, effective method for meeting regulatory and compliance requirements, making it an essential component of a comprehensive data protection strategy in the cloud.

In this blog post, we’ll explore how to enhance the security features of TDE by integrating it with custom managed keys from Azure Key Vault, offering more granular control over encryption keys and compliance with stringent data protection policies.

In the code below I am referencing the Azure Verified Modules project which is written by Microsoft and worth checking out.

Step 1 – Create an Azure KeyVault Key which we can use for TDE

// KeyVault settings
var keyVaultName = 'kv-${customerName}-${environmentName}-${locationShortCode}'

//MARK: createKeyVaultKeyForAzureSQLTDE
@description('create an RSA key within KeyVault')
module createKeyVaultKeyForAzureSQLTDE 'modules/key-vault/vault/key/main.bicep' = {
  scope: resourceGroup(spokeRg.name)
  name: 'keyVaultKey'
  params: {
    keyVaultName: keyVaultName
    name: keyName
    keySize: 2048
    kty: 'RSA'
    keyOps: [
      'encrypt'
      'decrypt'
      'sign'
      'verify'
      'wrapKey'
      'unwrapKey'
    ]
    tags: tags
    attributesEnabled: true
  }
  dependsOn: [
    spokeRg
    createKeyVault
  ]
}

The code above will create a new Key which we can use for TDE within an existimg KeyVault.

Once we have the key created we now need to figure out to to add this to Azrue SQL which proved to be harder than it should have (might be a bug in the modules, not sure yet). I couodnt figure out how to add the uri in the format it asks for – everything I tried didnt work so gave up and used the code below.

To get this working i used a custom module which is below:-

// sqlserver-keyvault-encryption.bicep
param sqlServerName string
param keyVaultName string
param keyName string
param keyVersion string
param keyUri string
param autoRotationEnabled bool

resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' existing = {
  name: sqlServerName
}

// Create sql server key from key vault
resource sqlServerKey 'Microsoft.Sql/servers/keys@2022-05-01-preview' = {
  name: '${keyVaultName}_${keyName}_${keyVersion}'
  parent: sqlServer
  properties: {
    serverKeyType: 'AzureKeyVault'
    uri: keyUri
  }
}

// Create the encryption protector
resource propector 'Microsoft.Sql/servers/encryptionProtector@2022-05-01-preview' = {
  name: 'current'
  parent: sqlServer
  properties: {
    serverKeyType: 'AzureKeyVault'
    serverKeyName: sqlServerKey.name
    autoRotationEnabled: autoRotationEnabled
  }
}

I then call this custom modue and pass in the necessary parameters like so: –

//MARK: encryption
module encryption 'modules/custom/sqlserver-keyvault-encryption.bicep' = {
  scope: resourceGroup(spokeResourceNetworkGroupName)
  name: 'sqlserver-keyvault-encryption'
  params: {
    sqlServerName: sqlServerName
    keyVaultName: keyVaultName
    keyName: keyName
    keyVersion: last(split(keyVaultKey.properties.keyUriWithVersion, '/'))
    keyUri: keyVaultKey.properties.keyUriWithVersion
    autoRotationEnabled: autoRotationEnabled
  }
  dependsOn: [
    spokeRg
    createsqlServer
  ]
}

This will then add TDE Encryption using custom managed keys and look like this:-

The screenshot above shows you when you have configured SQL TDE at the server level as database level is still in preview.

Summary

I hope this helps someone at somepoint as I spent some time trying to get the url for the version of the secret out of keyvault but it just wasnt happening.