SQL Server: support for Transparent Data Encryption

hashibot commented 7 years ago

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 commented 6 years ago

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 commented 6 years ago

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

RustyF commented 5 years ago

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 commented 5 years ago

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

jjgrayston commented 5 years ago

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

andreas-schilling commented 5 years ago

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 commented 5 years ago

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

rojekabc commented 4 years ago

I think the author of this issue doesn't think about settings for _azurerm_sqldatabase, but rather for _azurerm_sqlserver. 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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

Any update on this one?

cloudtidings commented 4 years ago

Why this issue was closed if there is no resolution disclosed

jorgearteiro commented 4 years ago

Why this issue was closed if there is no resolution disclosed

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

roshanp85 commented 4 years ago

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

Anna-Katona commented 4 years ago

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

paddywood commented 3 years ago

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 commented 3 years ago

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

frugecn commented 3 years ago

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

jayanthrajanna commented 3 years ago

Any update on this? I too have requirement to enable customer managed key on sql server for TDE

GrooveGod commented 3 years ago

Is there a schedule for this or any updates?

lazarillo commented 3 years ago

Why are the "would love to see this get implemented" and "any update" comments being downvoted, or worse, hidden as "off topic"?

This shows that there is still interest in the feature. I understand there is complexity -- perhaps it is even impossible -- but isn't it helpful to still be notified that this is of interest to customers?

fl-max commented 3 years ago

Why are the "would love to see this get implemented" and "any update" comments being downvoted, or worse, hidden as "off topic"?

@lazarillo because it is off-topic; comments are reserved for meaningful contributions. Downvotes are given out as it notifies everyone subscribed to the thread, only to come to find a "any update on this?" comment. You show your support by upvoting the OP's comment.

MoAhuja commented 3 years ago

Thanks @pearcec for the detailed information around the behavior of the keys. I have a working implementation of this, but wanted to update the group here to see if there is anything else that should be considered.

The structure of the resource is as follows:

resource "azurerm_mssql_server_transparent_data_encryption" "byok" {
    server_name         = azurerm_mssql_server.example.name
    resource_group_name = azurerm_resource_group.example.name
    key_vault_uri = azurerm_key_vault_key.generated.id
}

Implementation Details

The implementation follows pretty closely to what Azure Portal does when you toggle between Service Managed / Customer Managed options.

Once TDE (encryption protector) is enabled, it can't be deleted. I've followed the Azure portal design and opted not to delete the server keys either.
If key_vault_uri is omitted it will use Service Managed keys
You can switch from Service Managed <--> Customer Managed
There are no inherent safeguards to prevent you from deleting a customer managed key that is being used by TDE because they are separate resources. The documentation will call out for users to read the lifecycle management recommendations and best practices in the Microsoft documentation which includes the notion of backing up keys in AKV and binding TDE to a new key before deleting the old one.
Azure Portal has a checkbox to enable/disable setting the server key as the default encryption protector. When disabled, it will create a server key, but will not update encryption protector. As this resource is focused on setting up TDE, I've assumed anyone using it wants to setup encryption protector, so it's not a configurable option. This also means that if you want to add server keys without enabling TDE, it'll need to be done elsewhere.

ghost commented 3 years ago

This has been released in version 2.57.0 of the provider. Please see the Terraform documentation on provider versioning or reach out if you need any assistance upgrading. As an example:

provider "azurerm" {
    version = "~> 2.57.0"
}
# ... other configuration ...

lazarillo commented 3 years ago

And for those stumbling across this, here is the documentation.

github-actions[bot] commented 3 years ago

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues. If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.

hashicorp / terraform-provider-azurerm