unable to set azure sql dataase connection in logic app trhough ARM

Question

i have created a API connection through ARM template and in Second task i'm calling that API connection to set up sql connection. but it is not working.

API connection is successfully created , in the logic app it not showing up

API connetion ARM template

   resources": [
       {
         "type": "Microsoft.Web/connections",
         "apiVersion": "2016-06-01",
         "location": "centralus",
         "name": "DataBase",
         "properties": {
           "api": {
             "id": "[concat(subscription().Id,'/providers/Microsoft.Web/locations/','centralus','/managedApis/sql')]"
           },
           "displayName": "SQLDataBase",
           "parameterValues": {
             "server": "[parameters('SqlServerr')]",
             "database": "[parameters('database')]",
             "username": "[parameters('username')]",
             "password": "[parameters('password')]"
           }
         }
       }
     ],  "variables": {}

Connetcion in logic app**

"parameters": { "$connections": { "value": { "DataBase": { "connectionId": "[concat(subscription().id,'/resourceGroups/',resourceGroup().id,'/providers/Microsoft.Web/connections/DataBase')]", "connectionName": "DataBase", "id": "[concat(subscription().id,'/providers/Microsoft.Web/locations/centralus/managedApis/sql')]" } } } }

Answer 1

@Josh Thanks for reaching out. Your resource type Microsoft.Web/connections is missing authType value for the SQL connection.

  resources": [
       {
         "type": "Microsoft.Web/connections",
         "apiVersion": "2016-06-01",
         "location": "centralus",
         "name": "DataBase",
         "properties": {
           "api": {
             "id": "[concat(subscription().Id,'/providers/Microsoft.Web/locations/','centralus','/managedApis/sql')]"
           },
           "displayName": "SQLDataBase",
           "parameterValues": {
             "server": "[parameters('SqlServerr')]",
             "database": "[parameters('database')]",
             "username": "[parameters('username')]",
             "password": "[parameters('password')]",
             "authType":"basic"
           }
         }
       }
     ]

Your $connection looks good as below:

{
   "parameters":{
      "$connections":{
         "value":{
            "DataBase":{
               "connectionId":"[concat(subscription().id,'/resourceGroups/',resourceGroup().id,'/providers/Microsoft.Web/connections/DataBase')]",
               "connectionName":"DataBase",
               "id":"[concat(subscription().id,'/providers/Microsoft.Web/locations/centralus/managedApis/sql')]"
            }
         }
      }
   }
}

For your reference sharing the ARM template that creates the SQL connector and logic app with an SQL action. I have tested the same at my end and the connection and logic app was deployed successful and tested the same by executing my workflow.

{
   "$schema":"https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
   "contentVersion":"1.0.0.0",
   "parameters":{
      "connections_sql_name":{
         "defaultValue":"sql",
         "type":"String"
      },
      "workflows_logicappmaktest_name":{
         "defaultValue":"logicappmaktest",
         "type":"String"
      }
   },
   "resources":[
      {
         "type":"Microsoft.Web/connections",
         "apiVersion":"2016-06-01",
         "name":"[parameters('connections_sql_name')]",
         "location":"[resourceGroup().location]",
         "properties":{
            "api":{
               "id":"[concat(subscription().id, '/providers/Microsoft.Web/locations/', [resourceGroup().location], '/managedApis/', 'sql')]"
            },
            "displayName":"[parameters('connections_sql_name')]",
            "customParameterValues":{
               
            },
            "parameterValues":{
               "server":"servername",
               "database":"databasename",
               "username":"username",
               "password":"password",
               "authType":"basic"
            }
         }
      },
      {
         "type":"Microsoft.Logic/workflows",
         "apiVersion":"2017-07-01",
         "name":"[parameters('workflows_logicappmaktest_name')]",
         "location":"eastus",
         "dependsOn":[
            "[resourceId('Microsoft.Web/connections', parameters('connections_sql_name'))]"
         ],
         "properties":{
            "state":"Enabled",
            "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":{
                  "manual":{
                     "type":"Request",
                     "kind":"Http",
                     "inputs":{
                        "schema":{
                           
                        }
                     }
                  }
               },
               "actions":{
                  "Get_rows_(V2)":{
                     "runAfter":{
                        
                     },
                     "type":"ApiConnection",
                     "inputs":{
                        "host":{
                           "connection":{
                              "name":"@parameters('$connections')['sql']['connectionId']"
                           }
                        },
                        "method":"get",
                        "path":"/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/tables/@{encodeURIComponent(encodeURIComponent('[SalesLT].[Address]'))}/items"
                     }
                  }
               },
               "outputs":{
                  
               }
            },
            "parameters":{
               "$connections":{
                  "value":{
                     "sql":{
                        "connectionId":"[resourceId('Microsoft.Web/connections', parameters('connections_sql_name'))]",
                        "connectionName":"sql",
                        "id":"/subscriptions/yoursubscriptionID/providers/Microsoft.Web/locations/eastus/managedApis/sql"
                     }
                  }
               }
            }
         }
      }
   ]
}

Let me know if you need any assistance.

Please accept as "Yes" if the answer is helpful so that it can help others in the community. If you need any help/clarification/concerns/follow up questions, then please click on "Add Comment" on my answer and provide more details.