How to enable SSL for Cloud PostgreSQL connection from PowerBI service

[Anonymous]

Hi,

I have a PostgreSQL database running on a server accessible from the internet. I've managed to connect to this database from PowerBI Desktop, built a report using the data from the imported data and everything is running fine.
I have published this to the PowerBI Service seemingly successfully. I see the resulting report and dataset in the service.
I've then updated the credentials to connect to the database in the Datasets's settings page to then attempt to refresh the data from the database. When clicking on "Sign in", it fails as the service tries to connect without using SSL.

The connection from PowerBI Desktop however used SSL. The database only accepts SSL connections and PostgreSQL logs confirm all the above.

2020-03-04 09:35:37.592 UTC [15133] XXX@XXX FATAL: no pg_hba.conf entry for host "xx.xx.xx.xx", user "xxx", database "xxx", SSL off

Are there additional steps to perform to force PowerBI Service to use a SSL connection or to setup the certificates in the service ?
I thought PowerBI Service always used secure connections.

As it is a cloud database, although not an Azure one, it is my understanding a Gateway is not required.

[hampusrude]

@Anonymous Did you solve this problem?

[Anonymous]

I'm not working on the issue myself anymore, but I heard that it is working natively now, without gateway.

[v-easonf-msft]

Hi , @Anonymous 

According to my search, it still need to configure a gateway.

Refer to these articles and post:

https://community.powerbi.com/t5/Service/PostgreSQL-to-Power-BI-Service-without-on-premise-gateway/td-p/600487

https://stackoverflow.com/questions/52945285/connect-power-bi-service-to-postgresql-database-on-azure

https://stackoverflow.com/questions/39002314/connecting-powerbi-cloud-to-postgresql/45826055#45826055

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, @v-easonf-msft 

Thanks for the links you shared. It is similar to other contents I found before posting.

Unfortunately, they do not provide a satisfactory answer:

• a lot of content is deprecated, referring to npgsql being required even for PostgreSQL for PowerBI Desktop, while it is working now perfectly without requiring any additional component.
• if the gateway is mandatory, why then is the service actually trying to connect to the specified SQL server database, albeit without any secure connection? The simple fact that there are settings to configure the connection in PowerBI Service suggests that this should be supported. How should I proceed to submit this as a bug report ?
• at least one of them mentions using the gateway to access Azure hosted databases. This seems wrong, as it would mean having to go from PowerBI Service hosted by Microsoft to an Azure database hosted by Microsoft through an on-premise middleware ?! How would that work with a full Azure setup with absolutely no on-premise infrastructure ? Would it require having to pay for an additional Azure Windows VM just to host the gateway ?
• is there an official list of currently supported data source for the different modes (PowerBI Desktop, PowerBI Service) ?
• is Microsoft using the wording "cloud" datasources only for Azure datasources ? Cloud is usually used to refer to services that aren't on-premises, that is to say hosted on a private infrastructure not available from outside that private network.

Furthermore, I'd be really interested in how third party cloud services connector work, such as the one for Salesforce, which are hosting their date on their Salesforce cloud.

Best regards,

[v-easonf-msft]

Hi, @Anonymous 

We suggest you to open a ticket here for better answer if you are a pro user:
https://powerbi.microsoft.com/en-us/support/ 

Best Regards,
Community Support Team _ Eason