Terraform-provider-azurerm: SQL Server: support for Transparent Data Encryption

[hashibot]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

_This issue was originally opened by @anniehedgpeth as hashicorp/terraform#14595. It was migrated here as part of the provider split. The original body of the issue is below._

---

Terraform Version

0.9.5

Affected Resource(s)

• azurerm_sql_database

The azurerm_sql_database is missing the settings for Transparent Data Encryption.

[achandmsft]

As we haven't heard any requests for this enhancement yet, I will archive this issue. We can reconsider if there is any customer interest around the same.

[keithhopkins-gpc]

How to get this re-opened? We have a requirement for TDE in SQL Database (PaaS).

[RustyF]

I too have this requirement. As an aside, newbie question, is there a workaround that allows one to script TDE inside terraform or would I have to manually add TDE afterwards?

[simonlegeek]

I agree this would be very useful otherwise we would have to rely on a script running as a provisioner.

[jjgrayston]

+1. Amazed it hasn't been implemented yet.

[andreas-schilling]

TDE is enabled by default these days. So this feature would only make sense to enable it for existing setups where it is missing so far.

[gusnuf]

This feature is also necessary to integrate TDE with KeyVault, so a custom/user-managed key can be used.

[rojekabc]

I think the author of this issue doesn't think about settings for _azurerm_sql_database_, but rather for _azurerm_sql_server_. Look on those az client commands available on the reference:

• az sql server tde-key | Manage a server's encryption protector.
• az sql server tde-key set | Sets the server's encryption protector.
• az sql server tde-key show | Gets a server encryption protector.
  They allow to manage TDE (transparent database encryption) protector to swith to KeyValult and use specified customer key.
  The global mean of this process is described here: https://docs.microsoft.com/en-us/azure/sql-database/transparent-data-encryption-byok-azure-sql
  The goal is to allow by terraform script set up server tde to key vault, which will hold customer key. For database - yes - the TDE is enabled by default. But for server you have to enable it and you may choose the way of encrypt. Look on the second image with Sample Server on the page https://docs.microsoft.com/en-us/azure/sql-database/transparent-data-encryption-azure-sql
  So - question is still open. When this will be supported by Terraform? And will be or not?

[pearcec]

Reviewing this there are a few things to consider which will shape the implementation. First there are two types of server keys. ServiceManaged and AzureKeyVault. ServiceManaged is the default. With both types an Azure SQL serverKey is exists as documented in the REST API. The AzureKeyVault has one additional property which needs collected, the uri. It also has a naming convention formatted as YourVaultName_YourKeyName_01234567890123456789012345678901, which appears to be managed by the REST call.

The second half of this configuration is the encryptionProtector. Which can be set to any of the serverKeys. In the portal this is simply set along with the key, but it also has this notion of "Make the selected key the default TDE protector.", which if left unchecked, creates a key but doesn't set it as the current encryptionProtector. Which ends hidden from any view in the portal. I am not sure the purpose. Lastly encryptionProtector has multiple items per server but only ever seems to set a "current".

This could go two ways. We can bundle all the activity under a single configuration managing both the key creating and deletion along with setting the encryptionProtector or we could create a new resource managing the server keys. I am inclined to bundle it similar to how the portal simplifies this. The only part I am unsure of is will DELETEing the keys have a downstream effect on the SQL encrypted databases. The portal leaves the keys dangling in the backend and doesn't remove them when you push in a new key. I experimented with removing them and there seems to be no negative effect. As far as I read here the operation is safe and fast. But if someone understands otherwise. There might be some async issues I will need to manage during the update.

If anyone has immediate advice or recommendation I am willing to listen. Otherwise I am going to deliver a bundled optional block managing the keys with extensive testing and see how far I get. I am favoring this approach because there is no need for a key resource if the server does not exist, and that is usually discouraged.

transparent_data encryption {
  type = "ServiceManaged"
}

-or

transparent_data encryption {
  type = "AzureKeyVault"
  key_vault_url = "https://YourVaultName.vault.azure.net/keys/YourKeyName/01234567890123456789012345678901"
}

After that we can turn our attention to turning on and off encryption on the database.

[pearcec]

I hit a couple wrinkles:

• The azure-sdk-for-go is forcing a keyName which I apparently missed during my evaluation of the REST API not a big deal -- will attempt to validate convention
• Thinking through some dependencies the mssql_server managed identity is needed in order to access the vault. But you can't specify the policy until you have the identity.

The creates a circular dependency. This forces a third resource into the provider. It again raises the question do we bundle the encryptionProtector and the serverKey or break the resources out separately.

Naming this is going to be confusing for people as well. When they go to the portal they see "Transparent Data Encryption". This is why I am inclined to simplify it.

mssql_server_transparent_data_encryption {
  resource_group_name
  server_name
  server_key_type = "ServiceManaged"
}

or

mssql_server_transparent_data_encryption {
  resource_group_name
  server_name
  server_key_type = "AzureKeyVault"
  key_vault_url = ""
}

The above gets awkward. If you want to add a AzureKeyVault that is fine. If you want to move to ServiceManaged that is fine. But if you delete the resource what happens (encryptionProtector has no delete)? Do you put back ServiceManaged? If that is the case I think the simplest approach would be to only have

mssql_server_transparent_data_encryption {
  resource_group_name
  server_name
  key_vault_url = "" (Updates allowed)
}

If you want to move back to ServiceManaged simply drop the resource. We can moved it back to ServiceManaged and revalidate before returning.

[pearcec]

Another thing to consider is we need to make sure the key is in place with the encryptionProtector before removing the old one or changing the key URI on an existing name, otherwise it could lead to data loss. See here. Perhaps this is why the portal lets the keys dangle.

[yuri-tieto]

Any update on this one?

[cloudtidings]

Why this issue was closed if there is no resolution disclosed

[jorgearteiro]

Why this issue was closed if there is no resolution disclosed

It's open. @pearcec can we help? any worked started?

[roshanp85]

Any update for this, would we know any ETA over this implementation ?

[Anna-Katona]

Is it possible to add an opportunity to set Custom-managed key for Azure SQL server using terraform?

[paddywood]

Hi there. same as @Anna-Katona, I also have a requirement to use terraform to configure my azure sql server's Transparent data encryption for a customer managed key. Is this feature being considered and if so which release will it be included in? If not imminent, then could someone please let this forum know so that I can find another solution? Thank you in advance...

[mikemowgli]

@yupwei68 , this is a relevant feature and a long-opened ticket. Would you kindly consider it for the next release? Thank you

[frugecn]

Would definitely love to see the Customer Provided/Key Vault implementation.