Create Azure AD integrated SQL Server connection in an ARM template

Question

It looks like we are now able to authenticate with Azure AD with a SQL Server connector in a Logic App, which is great!

Here is a screenshot of the new connector dropdown.

My problem is when I go to change the name of this connector via an ARM template, I no longer have the Azure AD Integrated option when I select 'Edit Api Connection' for that connection in Azure Api connection blade, it looks like the SQL Server authentication connection.

Api Connection with Azure AD Integrated when creating the connection from a Logic App

Api Connection when creating it from an ARM template

From what I can see and have tried, when I export the templates from Azure they look identical. Here are the examples.

Template Export from Azure AD Integrated Connection:

"resources": [
        {
            "type": "Microsoft.Web/connections",
            "apiVersion": "2016-06-01",
            "name": "[parameters('azure_ad_authenticated_connection')]",
            "location": "<valid_location>",
            "properties": {
                "displayName": "{<db_name>} {<db_server_name>}",
                "customParameterValues": {},
                "api": {
                    "id": "[concat('/subscriptions/<subscription_id>/providers/Microsoft.Web/locations/<location>/managedApis/', parameters('connections_sql_name'))]"
                }
            }
        }
    ]

Template Export from a SQL Server authenticated connection:

"resources": [
        {
            "type": "Microsoft.Web/connections",
            "apiVersion": "2016-06-01",
            "name": "[parameters('sql_server_auth_connection')]",
            "location": "<valid_location>",
            "properties": {
                "displayName": "<display_name>",
                "customParameterValues": {},
                "api": {
                    "id": "[concat('/subscriptions/<subscription_id>/providers/Microsoft.Web/locations/<valid_location>/managedApis/sql')]"
                }
            }
        }
    ]

Has anyone been able to successfully create an Azure AD Integrated connection from an ARM template?

There's related azure feedback item on the uservoice: https://feedback.azure.com/forums/287593-logic-apps/suggestions/40651912-allow-azure-ad-integrated-authentication-for-sql-a — Ivan Samygin, Mar 23 '21 at 08:37

score 1 · Accepted Answer · edited Nov 04 '22 at 17:17

Indeed, it's to become crazy!

When Azure exports an ARM template it never includes the parameters that have to be kept secure. Therefore you end with an ARM template that is incomplete. In your case you have to add

"parameterValueSet": {
  "name": "oauth",
  "values": {}
}

The complete template is:

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "sql_Connection_Name": {
      "defaultValue": "sqlConnectionWithOAuth",
      "type": "String"
    },
    "sql_Connection_DisplayName": {
      "defaultValue": "sql Connection with OAuth",
      "type": "String"
    },
    "logicAppLocation": {
      "defaultValue": "westeurope",
      "type": "String"
    }
  },
  "variables": {},
  "resources": [
    {
      "type": "Microsoft.Web/connections",
      "apiVersion": "2016-06-01",
      "name": "[parameters('sql_Connection_Name')]",
      "location": "[parameters('logicAppLocation')]",
      "properties": {
        "displayName": "[parameters('sql_Connection_DisplayName')]",
        "customParameterValues": {},
        "api": {
          "id": "[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', resourceGroup().location, '/managedApis/sql')]",
          "type": "Microsoft.Web/locations/managedApis"
        },
        "parameterValueSet": {
          "name": "oauth",
          "values": {}
        }
      }
    }
  ]
}

And for further needs, you can use ARMclient to figure out the missing parameters. For sql api connection:

armclient.exe get https://management.azure.com/subscriptions/{SubscriptionId}/Microsoft.Web/locations/{LogicAppLocation}/managedApis/sql?api-version=2016-06-01

you'll get a long json description that includes the 'oauth' parameter:

"name": "oauth",
"uiDefinition": {
  "displayName": "Azure AD Integrated",
  "description": "Use Azure Active Directory to access your SQL database."
},

Create Azure AD integrated SQL Server connection in an ARM template

1 Answers1