通过Terraform启用Azure SQL的TDE失败，寻求解决方案

背景 #

我的Terraform脚本完成以下操作：

• 创建主SQL Server
• 启用SQL/AD登录
• 创建数据库
• 配置故障转移组（自动创建辅助服务器并复制数据库）
• 为主、辅助SQL Server分配用户托管标识

定义了默认值为false的is_transparent_data_encryption_enabled标志，设为true时生成的执行计划如下：

# 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)
   }

问题现象 #

执行terraform apply失败，报错SameKeyMaterialNotFoundOnRemoteServer。已尝试以下操作但未解决：

• 等待用户托管标识的Key Vault访问策略生效1小时
• 先给辅助服务器启用TDE（参考微软文档）
• 先给主服务器启用TDE

报错信息如下：

│ 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" {

期望 #

实现主、辅助SQL Server的TDE启用，需明确问题原因并提供解决方案建议。

解决方案建议 #

1. 验证主辅服务器的密钥访问一致性 #

• 确认主、辅服务器的用户托管标识均对目标Key Vault拥有get、wrapKey、unwrapKey权限，可通过Azure CLI验证：

  az keyvault show --name <your-key-vault-name> --query "accessPolicies[?objectId=='<msi-object-id>']"
  

• 确保两者使用完全相同的密钥ID（包括密钥版本），不能引用不同密钥或同一密钥的不同版本。

2. 强制Terraform执行顺序 #

Azure要求故障转移组关联的主辅服务器必须同时拥有相同的TDE密钥，Terraform默认并行更新资源会导致时序问题。通过depends_on强制先配置辅服务器的TDE密钥，再更新主服务器：

resource "azurerm_mssql_server" "secondary" {
  # 现有配置...
  transparent_data_encryption_key_vault_key_id = var.tde_key_id

  # 确保MSI权限已生效后再配置TDE
  depends_on = [azurerm_key_vault_access_policy.sql_secondary]
}

resource "azurerm_mssql_server" "primary" {
  # 现有配置...
  transparent_data_encryption_key_vault_key_id = var.tde_key_id

  # 等待辅服务器完成TDE配置后再更新主服务器
  depends_on = [azurerm_mssql_server.secondary]
}

3. 手动预配置辅服务器的密钥引用 #

如果Terraform依赖配置仍无法解决问题，可先通过Azure CLI手动给辅服务器添加密钥引用，确保辅服务器能获取到目标密钥：

az sql server tde-key set --server sqlautopocdemo-secondary-new --resource-group sc-sql-msi-poc --kid "https://suhas-sql-tde-key-53.vault.azure.net/keys/example-key/54cd85cc10d745eb966676f7c5af06a5" --identity-type UserAssigned --user-assigned-identity-id <secondary-msi-id>

完成后再执行terraform apply。

4. 检查故障转移组健康状态 #

确保故障转移组的复制状态正常，避免因复制异常导致TDE配置失败：

az sql failover-group show --resource-group sc-sql-msi-poc --server sqlautopocdemo --name <your-failover-group-name> --query "replicationState"

若状态异常，先修复复制问题再配置TDE。

内容的提问来源于stack exchange，提问作者Suhas Chandrashekar