I have created some reports using Power BI(PBI) Desktop and it's PostgreSQL connector with DirectQuery for auto refreshing. The reports work well. The problem lies in publishing the reports to PowerBI.com and viewing the reports as I do on Desktop.
I am having trouble accessing the reports due to connection issues around credentials, but the credentials are definitely correct.
I have since understood that there is a high likelihood this is due to our Digital Ocean (DO) hosted database only allows trusted sources to access it. My office is whitelisted as a trusted source (hence the reports working locally using PBI Desktop) but it can't be accessed when powerBI.com is attempting the connection.
First question is "is there a way to whitelist the IP that the Power BI web service uses?". I believe this is difficult because the IPs regularly change apparently and there are a plethora per region.
Second is "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?". Forgive me if my understanding is skewed here, but if I setup a Microsoft on-prem gateway, whitelist the IP of that gateway in Digital Ocean and then edit my Data Source settings in PowerBI.com to access the db through the gateway rather than directly to DO.
In theory, the gateway IP would be static so could be listed in DO and the gateway being a Microsoft service would be make communication between a lot easier.
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 this error:
The data source {database} is missing credentials and cannot be accessed
I have reviewed the Data Source Credentials settings and made sure the credentials match exactly what I was using in PBI Desktop and still get an issue:
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'
I know the timeout value for connections is 20s and it takes this long to receive that error so I know that something is not permitted to make this connection.
This is rather new to me so any help or advice is appreciated.