Terraform部署Azure SQL Server：用户分配身份授权超时求助

我用Terraform搭建包含.NET Linux服务应用与Azure SQL Server数据库的基础设施，希望通过用户分配身份（User Assigned Identity）实现API对数据库的访问。尝试为该身份添加数据库权限时出现超时问题——Terraform部署在Azure DevOps中执行，且处于私有网络环境，无法直接访问数据库。

我的Terraform配置代码如下：

resource "azurerm_user_assigned_identity" "sql_server_managed_identity" {
  name                = "sql-server-${local.domain}-managed-identity"
  location            = azurerm_resource_group.app_resource_group.location
  resource_group_name = azurerm_resource_group.app_resource_group.name
}

resource "azurerm_mssql_server" "sql_server" {
  name                         = "sql-${local.domain}-business"
  resource_group_name          = azurerm_resource_group.app_resource_group.name
  location                     = azurerm_resource_group.app_resource_group.location
  version                      = "12.0"
  minimum_tls_version          = "1.2"

  azuread_administrator {
    azuread_authentication_only = false
    login_username              = azurerm_user_assigned_identity.sql_server_managed_identity.name
    object_id                   = azurerm_user_assigned_identity.sql_server_managed_identity.principal_id
  }

  identity {
    type         = "UserAssigned"
    identity_ids = [azurerm_user_assigned_identity.sql_server_managed_identity.id]
   }

   primary_user_assigned_identity_id = azurerm_user_assigned_identity.sql_server_managed_identity.id
}

resource "azurerm_mssql_database" "sql_database_test" {
  depends_on     = [azurerm_mssql_server.sql_server]
  name           = "sqldb-${local.domain}-test"
  server_id      = azurerm_mssql_server.sql_server.id
  collation      = "SQL_Latin1_General_CP1_CI_AI"
  read_scale     = false
  ledger_enabled = false
  sku_name       = "S3"
  zone_redundant = false
  tags           = local.app_tags
}

resource "mssql_user" "web" {
  server {
    host = azurerm_mssql_server.sql_server.fully_qualified_domain_name
    azuread_managed_identity_auth {
      user_id = azurerm_user_assigned_identity.sql_server_managed_identity.id
    }
  }
  database  = azurerm_mssql_database.sql_database_test.name
  username  = azurerm_user_assigned_identity.sql_server_managed_identity.name
  object_id = azurerm_user_assigned_identity.sql_server_managed_identity.client_id
  roles     = ["db_datareader", "db_datawriter"]

  timeouts {
    default = "2m"
  }
}

问题根源 #

私有网络环境下，Azure DevOps的托管Terraform runner无法直接连接到SQL Server的公网端点，导致mssql_user资源创建时因网络不通超时。

可行方案 #

1. 用SQL Server AD管理员执行权限配置（生产环境推荐） #

既然已将用户分配身份设为SQL Server的AD管理员，可通过Azure资源脚本执行器运行SQL命令，无需直接连接数据库端口：

替换原有的mssql_user资源，使用azurerm_resource_script_execution：

resource "azurerm_resource_script_execution" "sql_user_permissions" {
  name                = "sql-user-permissions-${local.domain}"
  resource_group_name = azurerm_resource_group.app_resource_group.name
  location            = azurerm_resource_group.app_resource_group.location
  script_content      = <<EOF
USE ${azurerm_mssql_database.sql_database_test.name};
CREATE USER [${azurerm_user_assigned_identity.sql_server_managed_identity.name}] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [${azurerm_user_assigned_identity.sql_server_managed_identity.name}];
ALTER ROLE db_datawriter ADD MEMBER [${azurerm_user_assigned_identity.sql_server_managed_identity.name}];
EOF

  identity {
    type         = "UserAssigned"
    identity_ids = [azurerm_user_assigned_identity.sql_server_managed_identity.id]
  }

  timeout = "PT5M"

  depends_on = [azurerm_mssql_database.sql_database_test]
}

该方案通过Azure内部API执行SQL脚本，利用已配置的用户分配身份作为AD管理员权限，无需暴露数据库公网端点。

2. 配置私有网络访问 #

• 在SQL Server所在私有网络中部署Azure DevOps自托管代理，让runner直接在同网络内访问数据库
• 为SQL Server创建Azure Private Link，允许Azure DevOps托管代理通过私有端点访问数据库

3. 临时开启公网访问（仅测试用） #

测试阶段可临时添加Azure DevOps托管代理的公网IP到SQL Server的防火墙规则中，部署完成后移除。生产环境禁止使用此方法。

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