Connections disappearing in flow but still in connection list

[rmcleod45]

Hello, I have encountered this error a couple of times now with different connections. 

I have  a flow that was running all night and then started to fail this morning. It is a connection to a On-Prem SQL database through a data gateway.

The flows have failed wtih this error:

Unable to process template language expressions in action 'Get_rows' inputs at line '1' and column '1980': 'The template language expression 'json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sql']['connectionId']' cannot be evaluated because property 'shared_sql' doesn't exist, available properties are 'shared_sharepointonline, shared_onedriveforbusiness'. Please see https://aka.ms/logicexpressions for usage details.'.

My list of connections still shows the SQL connection and the connection is working

I have another test flow with the same connection and it works just fine. 

I am not sure why this is happening but it has happened a few times now, once with an approval workflow. I have recreated a number of flows to get around this but that is extremely frustrating.

Any help would be greatly appreciated.

[Brad_Groux · Accepted solution]

This is almost certainly a communication issues with the on-premise data gateway. Please review the Troubleshooting the On-premises data gateway documentation, and also review the Port requirements and the Firewall and proxy documentation and insure that your proxy or firewall allows access over those ports. 

Please review both hardware and software firewalls, including the Windows Firewall on the server. Finally, the Tools for troubleshooting section covers collecting logs and Fiddler tracing. 

For one reason or another, as the Flow is running, it can't communicate with the connections setup through the gateway. I'd check the sytem logs of the server or PC where you are running the connector, and see if you can correlate a drop in network activity to the times when the Flows failed. 

Also, insure that all of the required ports for the connector are always in use and not blocked or filtered by a proxy or firewall. If you'll notice the data gateway port requirements are far more than port 80 and 443:

*.servicebus.windows.net        5671-5672	  Used for Advanced Message Queuing Protocol (AMQP).	
*.servicebus.windows.net	443, 9350-9354	  Used by listeners on Service Bus Relay over TCP (requires 443 for access control token acquisition).

For most firewalls, port 80 and 443 are open because they are the HTTP/S ports, however the ports listed above would not be open by default.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-yamao-msft]

Hi @rmcleod45 ,

Do you have any functions in the action Get rows?

Please make sure SQL Server is always running.

If possible, please try to resubmit the flow to see if the run will succeed.

Further, please make sure there are no modifications on the table fields.

When testing with action Get rows, it is working properly.

Best regards,

Mabel

[v-yamao-msft]

Hi @rmcleod45 ,

What’s your current situation?

If the issue still exists, please feel free post back.

Best regards,

Mabel

[rmcleod45]

I am still working with our IT team to ensure that our gateway is configured correctly. I haven't had the same issue since, I ended up redesigning the flow anyways. 

Thank you for your resonse and sorry for not replying earlier. Wanted to wait to see what our IT came back with.

I will accept this as a solution.