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

[americanmussel]

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"
}

 

[ucfhall]

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]

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

[Boyan]

@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]

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

[CromeSchool]

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.

[Timy2604]

May I know when MS plans to carry out this update? It's ....almost 4 years already. 

[kengkreingkrai]

Still no update for this

[kcb4mk]

Please let me know if there is any update. I am in March, 2022 but still see the same errors.

[AnnaChu]

Closing out this idea dated back to 2017 with a solution posted by @Pstork1 Solved: Re: How to get past on-premise gateway limit, Exec... - Power Platform Community (microsoft.....

[DavePotts_BritE]

@AnnaChu my opinion is that you can't simply close this out with the work around as "implement a stored procedure in the database". What if you have access to a DB to run a SQL SELECT but no permissions to create a stored procedure - this is a *very* common scenario in my experience.

Plus it is a huge impediment to developing if for every new SELECT statement you have to implement a stored procedure. That's simply not how relational databases are designed to work.