Enabling the TDE on the Azure SQL using the resource -azurerm_mssql_server throws error (Critical)

[suhas-chandrashekar-maersk]

Is there an existing issue for this?

• [X] I have searched the existing issues

Community Note

• Please vote on this issue by adding a :thumbsup: 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 and review the contribution guide to help.

Terraform Version

Terraform v1.4.2

AzureRM Provider Version

azurerm v3.101.0

Affected Resource(s)/Data Source(s)

azurerm_mssql_server

Terraform Configuration Files

resource "azurerm_mssql_server" "primary" {
  name                          = var.sql_server_name
  resource_group_name           = local.resource_group_name
  location                      = var.location
  version                       = "12.0"
  administrator_login           = var.is_sql_authentication ? var.sql_administrator_login : null
  administrator_login_password  = var.is_sql_authentication ? random_password.primary.result : null
  public_network_access_enabled = true
  azuread_administrator {

    login_username              = var.ad_user_name
    object_id                   = var.ad_user_object_id
    azuread_authentication_only = var.azuread_authentication_only

  }

  transparent_data_encryption_key_vault_key_id = var.is_transparent_data_encryption_enabled ? azurerm_key_vault_key.example[0].id : null

  dynamic "identity" {
    for_each = length(local.user_identity_ids) > 0 ? [1] : []
    content {
      type         = "UserAssigned"
      identity_ids = local.user_identity_ids
    }
  }
  primary_user_assigned_identity_id = local.primary_identity != null ? local.primary_identity.id : null

  lifecycle {
    ignore_changes = [
      tags
    ]
  }
}

Debug Output/Panic Output

### Debug Output

│ Error: updating Server (Subscription: "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
│ Resource Group Name: "sc-sql-msi-poc"
│ Server Name: "sqlautopocdemo"): polling after CreateOrUpdate: polling failed: the Azure API returned the following error:
│
│ Status: "SameKeyMaterialNotFoundOnRemoteServer"
│ Code: ""
│ Message: "All servers linked by Geo replication should have the same key material as the encryption protector of the primary server. Please add the key 'https://suhas-sql-tde-key-53.vault.azure.net/keys/example-key/54cd85cc10d745eb966676f7c5af06a5' with the same key material to the secondary server 'sqlautopocdemo-secondary-new'."
│ Activity Id: ""
│
│ ---
│
│ API Response:
│
│ ----[start]----
│ {"name":"79d84913-ea6d-43c4-ba5c-20ccb225003f","status":"Failed","startTime":"2024-05-04T12:09:03.687Z","error":{"code":"SameKeyMaterialNotFoundOnRemoteServer","message":"All servers linked by Geo replication should have the same key material as the encryption protector of the primary server. Please add the key 'https://suhas-sql-tde-key-53.vault.azure.net/keys/example-key/54cd85cc10d745eb966676f7c5af06a5' with the same key material to the secondary server 'sqlautopocdemo-secondary-new'."}}
│ -----[end]-----
│
│
│   with azurerm_mssql_server.primary,
│   on azure_sql.tf line 48, in resource "azurerm_mssql_server" "primary":
│   48: resource "azurerm_mssql_server" "primary" {

Expected Behaviour

1. Create Primary SQL Server and Secondary SQL Servers.
2. Create Database on primary server.
3. Enable the Failover groups.
4. Enable TDE with CMK and it should work with azurerm_mssql_server resource.

TDE with CMK should be enabled using azurerm_mssql_server resource as mentioned here- https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_server#identity

Ref - https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_server_transparent_data_encryption

Actual Behaviour

It it throwing an error when I run terraform apply when am enabling the TDE with CMK when failover groups are enabled.

Steps to Reproduce

Am Running a Terraform Script that creates the following:

1. Creates primary server.
2. Enables sql/ad login.
3. Creates database/s.
4. enables the failover groups.(During this step it also creates a secondary Server and replicates the databaes)
5. Assigns the user Managed identity to both primary and secondary SQL Servers. Now, I have a flag defined as is_transparent_data_encryption_enabled which will be false by default. When, I enable it as true, It generates the plan as follows :

azurerm_mssql_server.primary will be updated in-place ~ resource "azurerm_mssql_server" "primary" { id = "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/sc-sql-msi-poc/providers/Microsoft.Sql/servers/sqlautopocdemo" name = "sqlautopocdemo"

• transparent_data_encryption_key_vault_key_id = (known after apply) (12 unchanged attributes hidden) (2 unchanged blocks hidden) }`

  azurerm_mssql_server.secondary[0] will be updated in-place ~ resource "azurerm_mssql_server" "secondary" { id = "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/sc-sql-msi-poc/providers/Microsoft.Sql/servers/sqlautopocdemo-secondary-new" name = "sqlautopocdemo-secondary-new"

• transparent_data_encryption_key_vault_key_id = (known after apply) (12 unchanged attributes hidden)

  (2 unchanged blocks hidden)

  }

Am Running a Terraform Script that creates the following:

Creates primary server. Enables sql/ad login. Creates database/s. enables the failover groups.(During this step it also creates a secondary Server and replicates the databaes) Assigns the user Managed identity to both primary and secondary SQL Servers. Now, I have a flag defined as is_transparent_data_encryption_enabled which will be false by default. When, I enable it as true, It generates the plan as follows :

azurerm_mssql_server.primary will be updated in-place ~ resource "azurerm_mssql_server" "primary" { id = "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/sc-sql-msi-poc/providers/Microsoft.Sql/servers/sqlautopocdemo" name = "sqlautopocdemo"

• transparent_data_encryption_key_vault_key_id = (known after apply) (12 unchanged attributes hidden)

   (2 unchanged blocks hidden)

  }`

  azurerm_mssql_server.secondary[0] will be updated in-place ~ resource "azurerm_mssql_server" "secondary" { id = "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/sc-sql-msi-poc/providers/Microsoft.Sql/servers/sqlautopocdemo-secondary-new" name = "sqlautopocdemo-secondary-new"

• transparent_data_encryption_key_vault_key_id = (known after apply) (12 unchanged attributes hidden)

   (2 unchanged blocks hidden)

  } But, when i run terraform apply, It fails with this message - `

I tried running after an hour too considering it might take some time for the user managed identity to get the access policies reflected, but still , it does not work.

Also, tried enabling it on Secondary first (as mentioned on the Microsoft document) and also tried enabling on the primary first too.. Both fails.

would be helpful with suggestions/inputs as soon as possible please?

Error: │ Error: updating Server (Subscription: "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" │ Resource Group Name: "sc-sql-msi-poc" │ Server Name: "sqlautopocdemo"): polling after CreateOrUpdate: polling failed: the Azure API returned the following error: │ │ Status: "SameKeyMaterialNotFoundOnRemoteServer" │ Code: "" │ Message: "All servers linked by Geo replication should have the same key material as the encryption protector of the primary server. Please add the key 'https://suhas-sql-tde-key-53.vault.azure.net/keys/example-key/54cd85cc10d745eb966676f7c5af06a5' with the same key material to the secondary server 'sqlautopocdemo-secondary-new'." │ Activity Id: "" │ │ --- │ │ API Response: │ │ ----[start]---- │ {"name":"79d84913-ea6d-43c4-ba5c-20ccb225003f","status":"Failed","startTime":"2024-05-04T12:09:03.687Z","error":{"code":"SameKeyMaterialNotFoundOnRemoteServer","message":"All servers linked by Geo replication should have the same key material as the encryption protector of the primary server. Please add the key 'https://suhas-sql-tde-key-53.vault.azure.net/keys/example-key/54cd85cc10d745eb966676f7c5af06a5' with the same key material to the secondary server 'sqlautopocdemo-secondary-new'."}} │ -----[end]----- │ │ │ with azurerm_mssql_server.primary, │ on azure_sql.tf line 48, in resource "azurerm_mssql_server" "primary": │ 48: resource "azurerm_mssql_server" "primary" {

Important Factoids

No response

References

https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_server#identity

Ref - https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_server_transparent_data_encryption

[suhas-chandrashekar-maersk]

@Everyone, Can someone confirm, if we are good using this - azurerm_mssql_server_transparent_dataencryption? Only Misleading is this -"This resource should only be used with pre-existing MS SQL Instances that are over 2 years old. For new MS SQL Instances that will be created through the use of the azurerm_mssql_server resource, please enable Transparent Data Encryption through azurerm_mssql_server resource itself by configuring an identity block"_

Ref -https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/mssql_server_transparent_data_encryption

Using azurerm_mssql_server to enable TDE with Customer Managed key throws error as mentioned above.