cancel
Showing results for 
Search instead for 
Did you mean: 

Support "Execute a SQL query" action through an on-premises data gateway

The “Execute a SQL query” action is not currently supported in Microsoft Flow using an on-premises gateway connection. 

 

The "Get rows" action allows users to pull records from a single table, but there is currently no way to pull records from multiple on-prem SQL tables. To avoid executing a complex query from Flow, some users have combined "Execute a stored procedure" to build a single table with "Get rows" to query it, but this workflow is also not supported with an on-premises data gateway.

 


Adding this functionality would create a great deal of opportunities for automation of testing and reporting on complex data structures.

Executing a Flow with this connector yields the following error:
{
  "status"400,
  "message""Gateway ExecutePassThroughNativeQueryAsync\r\nclientRequestId: 0a3e8a93-45a6-4f29-ad28-d4375b6963a7",
  "source""sqlconnectionprovider-westus.tip0-westus.p.azurewebsites.net"
}

 

 Flow Error.PNG

 

Status: New
Comments
ucfhall
New Member

Only problem with this approach is that the query is fixed and Power Automate is not set up to pass parameter values. This is a showstopper for us unfortunately. Good find...but Microsoft needs to stop bullying businesses to use Azure SQL by refusing to properly support on-premises SQL servers.

trjackson77
Helper II

Why does this idea have the 'New' label?  It is years old...

Boyan
Frequent Visitor

@ucfhall That's a good point. It is static. Duly noted. Of course the optimal outcome here is for Microsoft to listen and "fix" this terrible architecture decision but in 30 years in this biz I've always failed to compel Microsoft in that simple skill - listen to the customers 🙂 

jpshank2
Regular Visitor

Multiple docs online say this is possible. Could we match the functionality to the docs? That would save a huge amount of frustration

CromeSchool
New Member

This isn't just a problem for querying on-premise databases.  For security reasons, I'm needing to query my azure sql databases via an on-prem gateway (which has express route access to my databases).

 

My understanding is that in order to query azure sql from the power platform you need to whitelist the power platform ip ranges OR simply allow access to all azure services, neither of which is ideal.  (Very happy to hear if my understanding is wrong).  So this is the reason why I'm wanting to use an on-prem gateway.

 

Very keen to see this idea get developed.