Search code examples
azure-sql-databaseazure-biceptde

Resolve azure policy sql server should use customer managed keys to encrypt for bicep script

The following bicep script should create a SQL Server. However, due to Azure policy violation it fails, and requires that I use customer-managed keys to encrypt data. See Error:

enter image description here

Here's the code block to encrypt protection for SQL Server. Still getting the error above (not sure why it won't work):

resource sqlServer 'Microsoft.Sql/servers@2021-11-01' = {
  location: location
  name: name
  properties: {
    administratorLogin: adminUsername
    administratorLoginPassword: adminPassword
    publicNetworkAccess: publicNetworkAccess
  }
}

resource encryptProtectSQLServer 'Microsoft.Sql/servers/encryptionProtector@2021-11-01' =  {
  name: 'current'
  parent: sqlServer
  properties: {
    autoRotationEnabled: true
    serverKeyName: 'key-adf-cdis-devtest-va'
    serverKeyType: 'AzureKeyVault'
  }
}

When I add keyId:

resource sqlServer 'Microsoft.Sql/servers@2021-11-01' = {
  location: location
  name: name
  properties: {
    administratorLogin: adminUsername
    administratorLoginPassword: adminPassword
    publicNetworkAccess: publicNetworkAccess
    keyId: 'https://kvinfratestva.vault.usgovcloudapi.net/keys/key-pcr-sqlserver/654aa7c96d51450cassaassa15fce3ff26'
  }
}

I get the following error:

enter image description here

Solution

  • The first issue here is that you need to give the SQL Server RBAC permission to the KeyVault via a RoleAssignment. I personally prefer a User Assigned Managed Identity as it can be independently lifecycled (created in advance of your SQL Server by someone with permissions if your organisation has a tight security policy).

    By default, TDE in Azure SQL uses the primary user-assigned managed identity set on the server for key vault access. If no user-assigned identities have been assigned to the server, then the system-assigned managed identity of the server is used for key vault access. When using a user-assigned managed identity for TDE with CMK, assign the identity to the server and set it as the primary identity for the server

    The primary user-assigned managed identity requires continuous key vault access (get, wrapKey, unwrapKey permissions). If the identity's access to key vault is revoked or sufficient permissions aren't provided, the database will move to Inaccessible state

    ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-byok-identity?view=azuresql#considerations-while-using-umi-for-customer-managed-tde

    The second issue you could have is if the KeyVault is firewalled to only accept connections from connected Virtual Networks. I notice that you're using the US Gov Cloud (usgovcloudapi.net). So you could be might well have fun here given this restriction.

    The Allow Trusted Microsoft Services to bypass this firewall option is currently not available in National clouds.

    Good luck! 🤓