Problem Setting Scheduled Refresh utilizing Azure Database for Postgresql

[lorossi]

Hello, I have read numerous sources online and on this forum but I cannot seem to find the source of my particular problem. Whenever I use Power BI Desktop the database connection works and all of the visuals are refreshed (Via Import, not DirectQuery, however DirectQuery also does not work). When I publish the dashboard everything looks great; but when I try to set scheduled refresh, I get an error saying that my credentials are not correct. That confuses me because my the crendentials are the same as the ones I used inside Power BI Desktop:

I found many resources like this from 2018, maybe telling me that I need some sort of gate way to make this work, but I'm confused because of this image on the scheduling page:

I also tried to adding a huge range of IP addresses on the Firewall rule name contained in this link into the Networking tab on my Azure Database for Postgresql portal.

I also explored the possibility of this type of connection being the problem, but I am not sure:

I set up a separate Azure Postgresql database and the problems persisted.

I tried multiple other usernames and passwords for the credentials and nothing change, whenever I try to insert the correct credentials I get this error message:

I'm sure there is something that I am missing or not understanding properly how it works. 

Any help is deeply appreciated. Thank you!

[aj1973]

Hi @lorossi 

Try this 

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/connect-with-power-bi-desktop

and try to create a dataflow to see if you are able to use Power Query online without the Gateway. If so then you will be able to set the refresh schedule for your dataset

[lorossi]

Unfortunately I am unable to create the dataflow. It gives me the same errors stated above.

[aj1973]

Technically you are not supposed to enter your credentials if you don't need a Gateway and Postgre SQL is in the cloud like you said

How did you receive the same message?

[lorossi]

Okay, I added a firewall rule to all ip addresses temporarily = 0.0.0.0 - 255.255.255.255. The power bi refresh worked perfectly. But I do not want to allow all ip addresses to have access to my database. Now that we have identified the issue, do you know how to make it so that I don't have to allow all IP addresses? I have seen posts about an IP address list that Microsoft aAzure updates every week. I do not want to be updating a huge list of IP Addresses range, is there another way? Maybe I need to start another forum post about this.

[aj1973]

Sorry can't help you with that, I know that IP addresses are dynamic and hard to set it up. 

However you can create Power BI account service and only it can connect to the source and refresh data. Also use AD security groups to add only members to edit a dataset, and another security group for end users to consume the reports in App environment

I think this way you can better governance your data