none
SQL DB connection failed with status 404 and error-40 when make calls from Logic Apps RRS feed

  • Question

  • HI,

    I have automated the Azure resource creation using ARM templates, the following resources are created using the ARM templates

     - SQL Server, SQL database, Logic Apps with SQL API connection

    When i execute my Logic apps, i am getting the below error. 

    {
      "status": 404,
      "message": "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)\r\nclientRequestId: 8fb827fb-497b-4337-b9e9-b423ba130378",
      "source": "sql-eus.azconn-eus.p.azurewebsites.net"
    }

    But when i recreate the SQL DB connection from Logic Apps again, it is working fine.

    thanks, Senthil

     

    Monday, October 21, 2019 1:53 PM

All replies

  • Hello Senthil,

    I tried with the below ARM Template and it worked fine for me.

    {
      "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
      "contentVersion": "1.0.0.0",
      "parameters": {
        "logicAppName": {
          "type": "string",
          "metadata": {
            "description": "The name of the logic app."
          }
        },
        "sqlConnectionName": {
          "type": "string",
          "metadata": {
            "description": "The name of the SQL connection being created."
          }
        },
        "sqlServer": {
          "type": "string",
          "metadata": {
            "description": "The URI of the SQL Server"
          }
        },
        "sqlDatabase": {
          "type": "string",
          "metadata": {
            "description": "The name of the SQL database."
          }
        },
        "sqlUser": {
          "type": "string",
          "metadata": {
            "description": "The username for the SQL server."
          }
        },
        "sqlPassword": {
          "type": "securestring",
          "metadata": {
            "description": "The password for the SQL server."
          }
        },
        "sqlProcedure": {
          "type": "string",
          "metadata": {
            "description": "The procedure to run."
          }
        },
        "location": {
          "type": "string",
          "defaultValue": "[resourceGroup().location]",
          "metadata": {
            "description": "Location for all resources."
          }
        }
      },
      "variables": {
        "singleQuote": "'"
      },
      "resources": [
        {
          "type": "Microsoft.Web/connections",
          "apiVersion": "2016-06-01",
          "location": "[parameters('location')]",
          "name": "[parameters('sqlConnectionName')]",
          "properties": {
            "api": {
              "id": "[concat(subscription().id,'/providers/Microsoft.Web/locations/', parameters('location'), '/managedApis/sql')]"
            },
            "displayName": "sql_connection",
            "parameterValues": {
              "server": "[parameters('sqlServer')]",
              "database": "[parameters('sqlDatabase')]",
              "authType": "windows",
              "username": "[parameters('sqlUser')]",
              "password": "[parameters('sqlPassword')]"
            }
          }
        },
        {
          "type": "Microsoft.Logic/workflows",
          "apiVersion": "2016-06-01",
          "name": "[parameters('logicAppName')]",
          "location": "[parameters('location')]",
          "dependsOn": [
            "[resourceId('Microsoft.Web/connections', parameters('sqlConnectionName'))]"
          ],
          "properties": {
            "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_stored_procedure": {
                  "runAfter": {},
                  "type": "ApiConnection",
                  "inputs": {
                    "body": {},
                    "host": {
                      "api": {
                        "runtimeUrl": "[concat('https://logic-apis-', parameters('location'), '.azure-apim.net/apim/sql')]"
                      },
                      "connection": {
                        "name": "@parameters('$connections')['sql']['connectionId']"
                      }
                    },
                    "method": "post",
                    "path": "[concat('/datasets/default/procedures/@{encodeURIComponent(encodeURIComponent(', variables('singleQuote'), parameters('sqlProcedure'), variables('singleQuote'), '))}')]"
                  }
                }
              },
              "outputs": {}
            },
            "parameters": {
              "$connections": {
                "value": {
                  "sql": {
                    "connectionId": "[resourceId('Microsoft.Web/connections', parameters('sqlConnectionName'))]",
                    "connectionName": "[parameters('sqlConnectionName')]",
                    "id": "[concat(subscription().id,'/providers/Microsoft.Web/locations/', parameters('location'), '/managedApis/sql')]"
                  }
                }
              }
            }
          },
          "resources": []
        }
      ],
      "outputs": {}
    }
    Just make sure the name of the stored proc in parameter is like [dbo].[<StoredProcName>]

    Tuesday, October 22, 2019 8:55 AM
    Moderator