Power Platform Community Forum Thread Details

Hi everyone! It's good to be on here! 😊

I would like to know what is the best way for MS Power Automate to connect to an on-premise DB that is currently in a datacenter.

The database is currently accessible via SSMS on Citrix Desktop, but our vendor has opened access for us to access this DB using the IP address which I obtained using the method found in this video: https://www.youtube.com/watch?v=jr_9fiSzhs8.

The vendor has opened access by providing us with an "alias" IP address that is mapped to the actual IP address of the DB server. I am using the default credentials to connect.

When I tried connecting to the DB using the SQL Server connector on Power Automate, I kept getting a timeout error. I have installed the on-premise data gateway to allow connections on my machine, but I am not sure if that is the right thing to do.

I would love to hear from those who have had experience doing this. Thanks.

ps: Nice meeting everyone on here!

Categories:

General topics

Hi,

To connect to an on-premises database using Power Automate, you can use the On-premises data gateway. The on-premises data gateway acts as a bridge to provide quick and secure data transfer between on-premises data (data that isn’t in the cloud) and several Microsoft cloud services, including Power Automate 1.

To use the on-premises data gateway, you need to download and install it on a local computer. After installation, you can configure the gateway based on your firewall and other network requirements. You can then add gateway admins who can also manage and administer other network requirements. Finally, you can use the gateway to connect to your on-premises data sources from Power Automate 1.

Here are the steps to connect to your on-premises database using the SQL Server connector on Power Automate:

In Power Automate, create a new flow.
Add the SQL Server - Execute a SQL query action to your flow.
In the Server name field, enter the IP address of the database server.
In the Database name field, enter the name of the database you want to connect to.
In the User name and Password fields, enter your credentials.
In the Gateway field, select the on-premises data gateway you installed.
Enter your SQL query in the SQL query field.
Save and test your flow.

If you’re still experiencing issues with connecting to your database, you can try the following:

Ensure that the on-premises data gateway is installed and running on your local computer.
Ensure that your firewall settings allow traffic to and from the on-premises data gateway.
Ensure that your credentials are correct and have the necessary permissions to access the database.
Ensure that the IP address you’re using to connect to the database is correct.

Gill