Terraform the Creation on a Azure Standard Logic App and "SQL Connector"

Question

I would like to automate the deployment of a standard Azure logic app that does the following…

• Starts with a reoccurrence trigger (this i know how to do)

• Using a “sql connector” (think I should use a built-in connector as the logic app is using vnet integration) connect to an Azure sql database and execute a simple “sql select” query.

• The query will be executed using the ‘user assigned managed identity’ of the logic app which will be created as login in the database with “db_datareader” perms.

I am looking for a example or tutorial on how to Terraform the above. I have looked but found examples of using smtp and storage accounts...

Answer 1

Terraform the Creation on a Azure Standard Logic App and "SQL Connector"

I tired the configuration you're looking for creating logic app and SQL connector by referring to MSDoc1 and MSdoc2 this configuration you're looking for cannot be achieved from terraform along we need automation tools null resource and CLI commands to achieve the requirement.

Configuration:

resource "azurerm_mssql_server" "example" {
  name                         = "vksb-sql-server"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = azurerm_resource_group.example.location
  version                      = "12.0"
  administrator_login          = "adminuser"
  administrator_login_password = "H@Sh1CoR3!"
}


resource "azurerm_mssql_database" "example" {
  name                = "vksb-sql-db"
  server_id           = azurerm_mssql_server.example.id
  collation           = "SQL_Latin1_General_CP1_CI_AS"
  license_type        = "LicenseIncluded"
  max_size_gb         = 250     
  read_scale          = false   
  sku_name            = "S0"    
  zone_redundant      = false
}


resource "azurerm_logic_app_workflow" "example" {
  name                = "vksb-logic-app"
  location            = azurerm_resource_group.example.location
  resource_group_name = azurerm_resource_group.example.name

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

resource "azurerm_resource_group_template_deployment" "sql_api_connection" {
  name                = "vksb-api-connection-deployment"
  resource_group_name = azurerm_resource_group.example.name
  deployment_mode     = "Incremental"

  template_content = file("${path.module}/api-connection-template.json")

  parameters_content = jsonencode({
    connectionName = {
      value = "sql-connection"
    }
    serverName = {
      value = "vinaysb-sql-server.database.windows.net"
    }
    databaseName = {
      value = "vinay-sql-db"
    }
  })

  depends_on = [
    azurerm_user_assigned_identity.example,
    azurerm_mssql_server.example,
    azurerm_mssql_database.example,
    azurerm_logic_app_workflow.example
  ]
}

# Null Resource to execute PowerShell script for Logic App workflow update
resource "null_resource" "configure_logic_app_workflow" {
  provisioner "local-exec" {
    interpreter = ["pwsh", "-Command"]
    command = "${path.module}/configure-logic-app-and-sql-user.ps1"
    environment = {
      CLIENT_ID        = azurerm_user_assigned_identity.example.client_id
      TENANT_ID        = data.azurerm_client_config.current.tenant_id
      SERVER_NAME      = azurerm_mssql_server.example.name
      DATABASE_NAME    = azurerm_mssql_database.example.name
      LOGIC_APP_NAME   = azurerm_logic_app_workflow.example.name
      RESOURCE_GROUP   = azurerm_resource_group.example.name
      SUBSCRIPTION_ID  = data.azurerm_client_config.current.subscription_id
      USER_NAME        = "yourmailID"
      USER_ROLE        = "db_datareader"
    }
  }

  depends_on = [
    azurerm_resource_group_template_deployment.sql_api_connection,
    azurerm_logic_app_workflow.example
  ]
}

configure-logic-app-and-sql-user.ps1

sqlcmd -S tcp:$serverName.database.windows.net -d $databaseName -G -U $clientId@$tenantId -Q "$sqlCommand"


$workflow = @{
    '$schema' = "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#"
    contentVersion = "1.0.0.0"
    parameters = @{
        sqlServer = @{
            type = "string"
            defaultValue = $serverName
        }
        sqlDatabase = @{
            type = "string"
            defaultValue = $databaseName
        }
    }
    triggers = @{
        Recurrence = @{
            type = "Recurrence"
            recurrence = @{
                frequency = "Day"
                interval = 1
            }
        }
    }
    actions = @{
        Execute_SQL_Query = @{
            type = "ApiConnection"
            inputs = @{
                host = @{
                    connection = @{
                        name = "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Web/connections/sql-connection"
                    }
                }
                method = "post"
                path = "/query"
                body = @{
                    query = "SELECT * FROM your_table"
                }
            }
        }
    }
} | ConvertTo-Json -Depth 10

az resource update `
    --resource-group $resourceGroup `
    --name $logicAppName `
    --resource-type "Microsoft.Logic/workflows" `
    --set properties.definition="$workflow"

api-connection.json:

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Web/connections",
      "apiVersion": "2016-06-01",
      "location": "[resourceGroup().location]",
      "name": "[parameters('connectionName')]",
      "properties": {
        "displayName": "SQL Connection",
        "api": {
          "id": "[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', resourceGroup().location, '/managedApis/sql')]"
        },
        "parameterValues": {
          "server": "[parameters('serverName')]",
          "database": "[parameters('databaseName')]"
        }
      }
    }
  ],
  "parameters": {
    "connectionName": {
      "type": "string"
    },
    "serverName": {
      "type": "string"
    },
    "databaseName": {
      "type": "string"
    }
  }
}

Deployment:

Refer:

Provisioning Azure Logic Apps API Connections with Terraform | by Sharon Hart | Microsoft Azure | Medium by Sharon Hart

azurerm_mysql_server | Resources | hashicorp/azurerm | Terraform | Terraform Registry

azurerm_api_connection | Resources | hashicorp/azurerm | Terraform | Terraform Registry

mssql_user | Resources | betr-io/mssql | Terraform | Terraform Registry