求Terraform部署含内置SQL连接器的Azure标准逻辑应用示例教程

1. Terraform提供者配置 #

terraform {
  required_providers {
    azurerm = {
      source  = "hashicorp/azurerm"
      version = "~> 3.0"
    }
    null = {
      source  = "hashicorp/null"
      version = "~> 3.2"
    }
  }
}

provider "azurerm" {
  features {}
}

2. 创建用户分配托管身份 #

resource "azurerm_user_assigned_identity" "sql_reader" {
  name                = "la-sql-reader-msi"
  resource_group_name = azurerm_resource_group.example.name
  location            = azurerm_resource_group.example.location
}

3. 配置Azure SQL数据库：创建MSI登录并赋予权限 #

# （可选）若未提前创建SQL服务器和数据库，执行以下代码
resource "azurerm_mssql_server" "example" {
  name                         = "example-sql-server"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
  version                      = "12.0"
  administrator_login          = "sqladmin"
  administrator_login_password = "ComplexPassw0rd!"
}

resource "azurerm_mssql_database" "example" {
  name           = "example-sql-db"
  server_id      = azurerm_mssql_server.example.id
  sku_name       = "GP_Gen5_2"
}

# 执行SQL脚本，为MSI创建登录账户并分配db_datareader权限
resource "null_resource" "configure_sql_permissions" {
  depends_on = [azurerm_mssql_database.example, azurerm_user_assigned_identity.sql_reader]

  provisioner "local-exec" {
    command = <<EOT
az sql db execute-query --resource-group ${azurerm_resource_group.example.name} --server ${azurerm_mssql_server.example.name} --database ${azurerm_mssql_database.example.name} --query-text "CREATE LOGIN [${azurerm_user_assigned_identity.sql_reader.name}] FROM EXTERNAL PROVIDER; CREATE USER [${azurerm_user_assigned_identity.sql_reader.name}] FOR LOGIN [${azurerm_user_assigned_identity.sql_reader.name}]; ALTER ROLE db_datareader ADD MEMBER [${azurerm_user_assigned_identity.sql_reader.name}];"
EOT
  }
}

4. 创建VNet及逻辑应用专用子网 #

resource "azurerm_virtual_network" "example" {
  name                = "la-vnet"
  address_space       = ["10.0.0.0/16"]
  location            = azurerm_resource_group.example.location
  resource_group_name = azurerm_resource_group.example.name
}

resource "azurerm_subnet" "la_integration" {
  name                 = "la-integration-subnet"
  resource_group_name  = azurerm_resource_group.example.name
  virtual_network_name = azurerm_virtual_network.example.name
  address_prefixes     = ["10.0.0.0/24"]

  # 逻辑应用VNet集成必需的子网配置
  enforce_private_link_endpoint_network_policies = true
  enforce_private_link_service_network_policies  = true
}

5. 创建标准逻辑应用集成环境（ISE） #

resource "azurerm_logic_app_integration_service_environment" "example" {
  name                = "la-ise"
  resource_group_name = azurerm_resource_group.example.name
  location            = azurerm_resource_group.example.location

  sku {
    name     = "Developer" # 生产环境建议使用Premium SKU
    capacity = 1
  }

  virtual_network_configuration {
    subnet_id = azurerm_subnet.la_integration.id
  }
}

6. 创建逻辑应用工作流（含重复触发器+SQL查询） #

resource "azurerm_logic_app_workflow" "example" {
  name                = "sql-query-workflow"
  resource_group_name = azurerm_resource_group.example.name
  location            = azurerm_resource_group.example.location
  integration_service_environment_id = azurerm_logic_app_integration_service_environment.example.id

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

  # 工作流定义：重复触发器 + SQL查询动作
  definition = <<DEFINITION
{
  "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "$connections": {
      "defaultValue": {},
      "type": "Object"
    }
  },
  "triggers": {
    "Recurrence": {
      "recurrence": {
        "frequency": "Hour",
        "interval": 1
      },
      "type": "Recurrence"
    }
  },
  "actions": {
    "Execute_SQL_Query": {
      "type": "ApiConnection",
      "inputs": {
        "host": {
          "connection": {
            "name": "@parameters('$connections')['sql']['connectionId']"
          }
        },
        "method": "post",
        "path": "/datasets/default/query/sql",
        "body": {
          "query": "SELECT TOP 10 * FROM YourTargetTableName"
        }
      },
      "runAfter": {}
    }
  },
  "outputs": {}
}
DEFINITION

  # 配置SQL连接器参数
  parameters = {
    "$connections" = jsonencode({
      "sql" = {
        "connectionId"      = azurerm_api_connection.sql.id
        "connectionName"    = "sql"
        "id"                = "/subscriptions/${var.subscription_id}/providers/Microsoft.Web/locations/${azurerm_resource_group.example.location}/managedApis/sql"
      }
    })
  }
}

# 创建VNet集成的SQL连接器
resource "azurerm_api_connection" "sql" {
  name                = "sql-internal-connection"
  resource_group_name = azurerm_resource_group.example.name
  location            = azurerm_resource_group.example.location
  managed_api_id      = "/subscriptions/${var.subscription_id}/providers/Microsoft.Web/locations/${azurerm_resource_group.example.location}/managedApis/sql"

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

  parameter_values = {
    "server"   = azurerm_mssql_server.example.fully_qualified_domain_name
    "database" = azurerm_mssql_database.example.name
  }

  integration_service_environment_id = azurerm_logic_app_integration_service_environment.example.id
}

关键补充 #

• 替换YourTargetTableName为实际SQL表名
• 生产环境需将ISE的SKU改为Premium，并调整容量
• 需为SQL服务器添加VNet规则，允许逻辑应用子网访问（可通过azurerm_mssql_server_vnet_rule资源配置）
• 提前定义var.subscription_id变量，或直接替换为实际订阅ID

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