How to query on-premises SQL Server database using power automate varible?

Question

I am trying to query an on-premises SQL Server database using a power automate variable.

Is there any suggestions or is this even possible for the time being?

I have attempted the following:

1. Using “Execute a SQL query (V2)” with an on-prem gateway connection. Not supported!
2. Using “Execute stored procedure (V2)” This won't return a value or allow variables.
3. Using “Power Query” This has similar issues to 2, that it won't allow power automate variables.
4. Consider Using Azure Managed Instances and linking the on-premises db to this instance, but can't see a obvious way for Azure to communicate with the on-prem SQL db.

Query Example: SELECT * FROM Customers WHERE Country={Power-Automate-Varible};

Answer 1

This can be achieved by utilising Power Automate and Desktop Flows together. The limitations prevent on-prem SQL to be queried directly using a cloud flow, but querying it locally and passing the variables between Cloud and Desktop as Input and Output variables defined in the Desktop Flow.

Once defined the input variables in Desktop Flow, you can setup a run desktop flow in the cloud flow to execute the command on a local machine either attended or unattended and pass the query results back using output variables and this would automatically be stored in the cloud flow once the desktop flow has completed.

Here is further detail regarding input/output variables: https://learn.microsoft.com/en-us/power-automate/desktop-flows/manage-variables