部署带系统托管标识的Azure SQL Server遇AzureKeyVaultNoServerIdentity错误求助

错误信息 #

Resource Group Name: "rg-a-b-dev-db"
│ Server Name: "sql-a-b-dev"
│ Database Name: "db-a-b-dev"): polling after CreateOrUpdate: polling failed: the Azure API returned the following error:
│
│ Status: "AzureKeyVaultNoServerIdentity"
│ Code: ""
│ Message: "The server identity is not correctly configured on server 'Server: sql-a-b-dev, DB: db-a-b-dev'."
│ Activity Id: ""

问题分析 #

这个错误核心是：SQL Server的系统托管标识尚未完成Azure AD注册，或者未获得访问Key Vault中TDE密钥的必要权限，就尝试为数据库配置透明数据加密（TDE）。你的配置存在两个关键问题：

1. 冗余权限配置：同时使用Key Vault访问策略和Key Vault Crypto User角色分配，两者功能重叠，且角色分配的生效时机可能滞后于数据库创建。
2. 资源依赖不完整：SQL数据库的创建未等待Key Vault访问策略完全生效，导致SQL Server标识还未获得密钥访问权限。

修复方案 #

1. 移除冗余的角色分配 #

Key Vault的访问策略已足够授予SQL Server标识访问密钥的权限，无需额外的azurerm_role_assignment资源，直接删除该资源即可。

2. 调整依赖关系 #

确保SQL数据库在Key Vault访问策略完全配置完成后再创建，明确依赖Key Vault资源的就绪状态。

修改后的完整配置 #

# Resource Group
resource "azurerm_resource_group" "database" {
  name     = "rg-${var.product}-${var.environment}-db"
  location = var.location
  tags     = module.mazure_tags.tags
}

# SQL Server with SystemAssigned Identity
resource "azurerm_mssql_server" "mssql_server" {
  name                         = "sql-${var.product}-${var.environment}"
  resource_group_name          = azurerm_resource_group.database.name
  location                     = var.location
  version                      = "12.0"
  administrator_login          = "user"
  administrator_login_password = "password"
  tags                         = module.mazure_tags.tags

  identity {
    type = "SystemAssigned"
  }
}

output "sql_server_principal_id" {
  value = azurerm_mssql_server.mssql_server.identity[0].principal_id
}

# Azure Key Vault
resource "azurerm_key_vault" "database" {
  name                        = "kv-database-${var.environment}-${random_id.kv_id.hex}"
  location                    = azurerm_resource_group.database.location
  resource_group_name         = azurerm_resource_group.database.name
  enabled_for_disk_encryption = true
  tenant_id                   = data.azurerm_client_config.current.tenant_id
  soft_delete_retention_days  = 7
  purge_protection_enabled    = true
  sku_name                    = "standard"

  # Admin access
  access_policy {
    tenant_id = data.azurerm_client_config.current.tenant_id
    object_id = data.azurerm_client_config.current.object_id

    key_permissions = [
      "Get", "List", "Create", "Delete", "Update",
      "Recover", "Purge", "GetRotationPolicy"
    ]
  }

  # SQL Server identity access - 此权限已满足TDE需求
  access_policy {
    tenant_id = azurerm_mssql_server.mssql_server.identity[0].tenant_id
    object_id = azurerm_mssql_server.mssql_server.identity[0].principal_id

    key_permissions = ["Get", "WrapKey", "UnwrapKey"]
  }

  depends_on = [
    azurerm_mssql_server.mssql_server
  ]
}

# Key Vault Key
resource "azurerm_key_vault_key" "database" {
  name         = "key-${var.product}-${var.environment}"
  key_vault_id = azurerm_key_vault.database.id
  key_type     = "RSA"
  key_size     = 2048
  key_opts     = ["unwrapKey", "wrapKey"]

  depends_on = [azurerm_key_vault.database]
}

# SQL Database using TDE with Key Vault Key
resource "azurerm_mssql_database" "mssql_database" {
  name           = "db-${var.product}-${var.environment}"
  server_id      = azurerm_mssql_server.mssql_server.id
  collation      = "SQL_Latin1_General_CP1_CI_AS"
  license_type   = "LicenseIncluded"
  max_size_gb    = 10
  read_scale     = false
  sku_name       = "S0"
  zone_redundant = false
  enclave_type   = "VBS"
  tags           = module.mazure_tags.tags

  transparent_data_encryption_key_vault_key_id = azurerm_key_vault_key.database.id

  lifecycle {
    prevent_destroy = true
  }

  # 确保等待Key Vault和密钥完全就绪，且访问策略生效
  depends_on = [
    azurerm_mssql_server.mssql_server,
    azurerm_key_vault.database,
    azurerm_key_vault_key.database
  ]
}

resource "azurerm_mssql_server_extended_auditing_policy" "audit_policy" {
  server_id                               = azurerm_mssql_server.mssql_server.id
  storage_endpoint                        = azurerm_storage_account.main.primary_blob_endpoint
  storage_account_access_key              = azurerm_storage_account.main.primary_access_key
  storage_account_access_key_is_secondary = false
  retention_in_days                       = 90
}

额外说明 #

• 系统托管标识的注册是异步过程，Azure需要数秒到数分钟完成标识在AD中的创建，depends_on仅能保证资源创建顺序，无法等待AD同步。如果仍遇到问题，可以在Key Vault资源后添加time_sleep资源，延迟1-2分钟后再创建数据库。
• 确保Key Vault的tenant_id与SQL Server标识的tenant_id一致，避免跨租户权限问题。

内容的提问来源于stack exchange，提问作者Sang Kim