如何用Terraform安全连接Azure Analysis Services与SQL数据库、Data Factory

一、完整Terraform配置示例 #

以下是包含Azure Analysis Services Server（AASS）、Azure SQL Database、Azure Data Factory（ADF）及安全连接配置的可复用代码：

# 基础资源组
resource "azurerm_resource_group" "example" {
  name     = "rg-data-analytics"
  location = "East Asia"
}

# Azure SQL Server与数据库（配置防火墙允许AASS访问）
resource "azurerm_sql_server" "sql_server" {
  name                         = "sql-server-${random_string.suffix.result}"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
  version                      = "12.0"
  administrator_login          = "sqladminuser"
  administrator_login_password = "ComplexPass_2024!"
}

resource "azurerm_sql_database" "analytics_db" {
  name           = "business-analytics-db"
  resource_group_name = azurerm_resource_group.example.name
  server_name    = azurerm_sql_server.sql_server.name
  sku_name       = "GP_Gen5_2"
}

# 自动允许AASS出站IP访问SQL数据库的防火墙规则
resource "azurerm_sql_firewall_rule" "aass_to_sql" {
  name                = "AllowAASSAccess"
  resource_group_name = azurerm_resource_group.example.name
  server_name         = azurerm_sql_server.sql_server.name
  start_ip_address    = azurerm_analysis_services_server.server.outbound_ip_addresses[0]
  end_ip_address      = azurerm_analysis_services_server.server.outbound_ip_addresses[length(azurerm_analysis_services_server.server.outbound_ip_addresses)-1]
}

# Azure Analysis Services Server（含基础防火墙+可选专用端点）
resource "azurerm_analysis_services_server" "server" {
  name                    = "analytics-services-server"
  location                = azurerm_resource_group.example.location
  resource_group_name     = azurerm_resource_group.example.name
  sku                     = "S0"
  admin_users             = ["myadmin@domain.tld"]
  enable_power_bi_service = true

  # 固定IP段防火墙规则（适配办公网络或测试环境）
  ipv4_firewall_rule {
    name        = "AllowOnPremAccess"
    range_start = "210.117.252.0"
    range_end   = "210.117.252.255"
  }

  # 可选：生产环境专用端点配置（VNet内私有访问）
  private_endpoint_connection {
    name = "aass-private-endpoint-connection"
    private_endpoint_id = azurerm_private_endpoint.aass_pe.id
    private_service_connection_state {
      status          = "Approved"
      description     = "Auto-approved for VNet access"
      actions_required = ""
    }
  }

  tags = {
    Environment = "Production"
    Workload    = "Analytics"
  }
}

# 专用端点依赖的虚拟网络与子网
resource "azurerm_virtual_network" "data_vnet" {
  name                = "data-analytics-vnet"
  address_space       = ["10.10.0.0/16"]
  location            = azurerm_resource_group.example.location
  resource_group_name = azurerm_resource_group.example.name
}

resource "azurerm_subnet" "private_endpoint_subnet" {
  name                 = "private-endpoint-subnet"
  resource_group_name  = azurerm_resource_group.example.name
  virtual_network_name = azurerm_virtual_network.data_vnet.name
  address_prefixes     = ["10.10.1.0/24"]

  private_endpoint_network_policies_enabled = true
}

resource "azurerm_private_endpoint" "aass_pe" {
  name                = "aass-private-endpoint"
  location            = azurerm_resource_group.example.location
  resource_group_name = azurerm_resource_group.example.name
  subnet_id           = azurerm_subnet.private_endpoint_subnet.id

  private_service_connection {
    name                           = "aass-private-service-link"
    private_connection_resource_id = azurerm_analysis_services_server.server.id
    subresource_names              = ["asazure"]
    is_manual_connection           = false
  }
}

# Azure Data Factory及链接服务配置
resource "azurerm_data_factory" "analytics_adf" {
  name                = "analytics-data-factory"
  location            = azurerm_resource_group.example.location
  resource_group_name = azurerm_resource_group.example.name
}

# ADF链接到AASS（服务主体身份验证）
resource "azurerm_data_factory_linked_service_analysis_services" "adf_to_aass" {
  name               = "LinkedService-AASS"
  data_factory_id    = azurerm_data_factory.analytics_adf.id
  server_endpoint    = azurerm_analysis_services_server.server.server_full_name
  
  service_principal {
    id          = azurerm_service_principal.adf_sp.id
    key         = azurerm_service_principal_password.adf_sp_pwd.value
    tenant_id   = data.azurerm_client_config.current.tenant_id
  }
}

# ADF链接到SQL Database
resource "azurerm_data_factory_linked_service_sql_server" "adf_to_sql" {
  name                 = "LinkedService-SQLDB"
  data_factory_id      = azurerm_data_factory.analytics_adf.id
  connection_string    = "Server=tcp:${azurerm_sql_server.sql_server.fully_qualified_domain_name},1433;Initial Catalog=${azurerm_sql_database.analytics_db.name};Persist Security Info=False;User ID=${azurerm_sql_server.sql_server.administrator_login};Password=${azurerm_sql_server.sql_server.administrator_login_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
}

# 辅助资源：随机字符串（避免资源命名冲突）
resource "random_string" "suffix" {
  length  = 5
  special = false
  upper   = false
}

# 辅助资源：ADF访问AASS的服务主体
resource "azurerm_azuread_application" "adf_app" {
  name = "app-adf-analytics-access"
}

resource "azurerm_service_principal" "adf_sp" {
  name                = "sp-adf-analytics-access"
  application_id      = azurerm_azuread_application.adf_app.application_id
}

resource "azurerm_service_principal_password" "adf_sp_pwd" {
  service_principal_id = azurerm_service_principal.adf_sp.id
  value                = "StrongPass_2024!"
  end_date             = "2030-01-01T00:00:00Z"
}

二、安全网络配置说明 #

1. 防火墙规则 vs 专用端点选择 #

• 防火墙规则：适合测试环境或需要允许特定公网IP访问的场景，配置简单快速。示例中同时配置了固定IP段规则，以及动态引用AASS出站IP的SQL防火墙规则，确保AASS能正常访问SQL数据源。
• 专用端点：生产环境首选，所有流量通过VNet私有IP传输，完全避免公网暴露。需注意：
  • 专用端点必须部署在启用private_endpoint_network_policies_enabled的子网中
  • AASS的private_endpoint_connection需设置为Approved状态才能生效

2. 核心安全最佳实践 #

• 禁止硬编码敏感凭证，建议使用Azure Key Vault存储密码、服务主体密钥
• 最小化防火墙IP范围，仅允许必要的访问来源
• 为专用端点所在子网配置网络安全组（NSG），限制VNet内的流量访问权限

三、各服务连接关键要点 #

AASS与SQL Database连接 #

• 必须通过SQL防火墙规则允许AASS的出站IP段访问，示例中通过Terraform属性动态引用outbound_ip_addresses，无需手动查询
• 若使用专用端点，AASS模型中配置SQL数据源时需使用SQL的私有端点FQDN

ADF与AASS连接 #

• 使用服务主体（SPN）进行身份验证，需提前将该SPN添加到AASS的管理员或数据访问角色中
• 链接服务配置中直接引用AASS的server_full_name属性，自动获取完整端点地址

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