Logic App Execute Query fails to connect

Question

Hi,
After trawling the forums and Docs I cannot find a reason for this failure, but I have a couple of ideas but need guidance.

My stored procedure executes fine from SQL Server Management Studio.
My Logic App runs fine apart from when it runs Execute a SQL query (V2) segment.
I have tried selecting the Azure SQL connection details from the drop downs and get a 404 failure.
I have created an Azure API Connection to the same Azure SQL Instance but this fails from within the Logic App when executing with a 404 error too.

The below Execute a SQL query (V2) uses the API Connection
EXEC [dbo].[ImportXML]'<?xml version="1.0" encoding="utf-8"?>
<xport version="1.0">
<shipments>
<shipment id="999999">
<keycode>XXX-999999999</keycode>
<sealno>H 566707</sealno>
<departure>2020-09-03T10:06:15Z</departure>
<licenceplate-truck>9999</licenceplate-truck>
<licenceplate-trailer/>
<max-weight>25000</max-weight>
<dropoff-trailer>false</dropoff-trailer>
<empty-weight>0</empty-weight>
<haulier>
<code>999999999</code>
<description>XXXX (Europe)</description>
</haulier>
<shipto>
<destination>
<code>XXXXX</code>
<description>XXXX</description>
<address>XXXX</address>
<address-additional/>
<zip-code>XXXX</zip-code>
<city>XXXXXXX</city>
<country>XX</country>
</destination>
</shipto>
<freights>
<freight>
<cxn>XX999999999</cxn>
<items>
<item>
<code>XX99</code>
<description>XXXXXX</description>
<eancode>99999999</eancode>
<eanvariant>00</eanvariant>
<casewidth>999</casewidth>
<casedepth>999</casedepth>
<caseheight>999</caseheight>
<caseweight>99.9000000</caseweight>
<tdunettweight>0.0000000</tdunettweight>
<tdudeclaredweight>0.0000000</tdudeclaredweight>
<casesonpallet>1</casesonpallet>
<stackheight>1</stackheight>
<numberoflayers>1</numberoflayers>
<pallettype>XXX</pallettype>
<pallets>1</pallets>
<positions>1.00</positions>
<weight>999.9000000</weight>
<cases>1</cases>
</item>
</items>
</freight>
</freights>
</shipment>
</shipments>
</xport>
{"Connection":"keep-alive","Accept":"/","Host":"prod-20.uksouth.logic.azure.com:443","User-Agent":"AHC/1.0","x-correlation-id":"a15089f0-edcd-11ea-8c96-129df4861397","Content-Length":"5757","Content-Type":"application/xml; charset=UTF-8"}'

and returns
{
"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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)\r\nclientRequestId: 246f8275-7887-4b5e-b6e6-ff7d66123af1",
"error": {
"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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)"
},
"source": "sql-uks.logic-ase-uksouth.p.azurewebsites.net"
}

So is the problem due to some IP's not being whitelisted in nsg? If so what are they?
Why is the connection info after the Execute command and included as a parameter?The Stored procedure is expecting just the XML.

Any direction would be wonderful
Thanks

AG

Answer 1

@Andrew Gilbert Thank you for sharing the details offline. In this case, we are connecting a SQL Managed instance. Upon further investigation , MS team has figured that there was some issue with the NSG configuration.
If you have NSG rules enabled for the SQL MI subnet, please allow inbound connections to port 3342.
Check this

Note : Since logic app is in multi tenant environment, to connect with SQL managed instance, we will have to enable public end point.
Please refer to this.

Please Accept it as an answer and "Up-Vote" the answer or message(s) that helped you so that it can help others in the community looking for help on similar topics