Configuring Power BI-Cloud PostgreSQL conn

Programming
, , , ,
1

Issue: Connection Issues with Power BI Desktop and Digital Ocean Hosted Database

I am having trouble accessing reports I have created using Power BI (PBI) Desktop and its PostgreSQL connector with DirectQuery for auto refreshing through PowerBI.com. I believe this is due to our Digital Ocean (DO) hosted database only allowing trusted sources to access it, as my office is whitelisted as a trusted source, allowing the reports to work locally on PBI Desktop.

Questions

  1. Is there a way to whitelist the IP that the Power BI web service uses?
  2. Is it possible to create an on-premises gateway that will act as an intermediate access point between PowerBI.com and the Digital Ocean DB?

Errors and What I’ve Tried

When I have published to my workspace on PowerBI.com from PBI Desktop and try to view the report I get the following error:

The data source {database} is missing credentials and cannot be accessed

After reviewing the Data Source Credentials settings and making sure the credentials match exactly what I was using in PBI Desktop, I still get the following error:

Failed to update data source credentials: An error happened while reading data from the provider: 'A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond'

This indicates that the connection is not permitted, as it takes the timeout value of 20s to receive that error.

Any help or advice is appreciated.

2

  1. Yes, you can whitelist the IP that the Power BI web service uses. To do this, you need to find the IP address of the Power BI web service and add it to the allowed IP list in your Digital Ocean database configuration.

  2. Yes, you can create an on-premises gateway to act as an intermediate access point between PowerBI.com and the Digital Ocean DB. The on-premises gateway allows you to securely connect to your on-premises data sources from Power BI service without the need to expose them through the internet.

To resolve your connection issues, follow these steps:

  1. Whitelist the IP address of the Power BI web service in your Digital Ocean database configuration.
  2. Install and configure the Power BI On-premises Data Gateway on a machine that has access to both the Digital Ocean database and the Power BI web service. This gateway will act as an intermediary for data transfer.
  3. Connect your Power BI Desktop to the Digital Ocean database using the PostgreSQL connector and DirectQuery mode.
  4. Publish the report to PowerBI.com.
  5. Configure the dataset to use the on-premises gateway for data refresh.

By following these steps, you should be able to access and refresh your reports using Power BI Desktop and the Digital Ocean hosted database through PowerBI.com.