Power BI Service and Scheduled refresh for Postgre SQL

[rahulanandt]

I have connected Postgre SQL with Power BI Services DataFlow but whenever I am refreshing the data I am getting the following error.

Error: Data Source Error : DataSource.Error: An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.' DataSourceKind = PostgreSQL DataSourcePath = *****.*****.us-east-2.rds.amazonaws.com;*****. RootActivityId = 8c4464fd-b10d-44c3-a09f-e851954b3b5d.Param1 = DataSource.Error: An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.' DataSourceKind = PostgreSQL DataSourcePath = *****.*****.us-east-2.rds.amazonaws.com;***** Request ID: 8f34e130-2e62-bc64-d136-6d7a205f8a8d.

If I use a gateway this error doesn't happen but I don't want to use a gateway for this connetion.

[rahulanandt]

Hi Liu Yang,

Thank you for your comment.
My problem is not with power bi desktop it is with power bi online verison.
I am able to connected directly to postgre in power bi desktop verision. Also in the online version I am able to connect to postgre using a enterprise gateway.
If I am not using the gateway I am able to connect(after unchecking the enecrypted connection option) but, refresh is not working. If I click on the refresh I am getting the above erorr. I wanted to schedule refresh the database at every morning 2 am without keeping my laptop on.

[v-yangliu-msft]

Hi  @rahulanandt ,

 The PostgresSQL connector doesn't work out of the box, you need to do quite a few things first

1. Install Npgsql which allows .NET access to PostgresSQL.  I installed it using the Pakagae Manager Console in Visual Studio; detailed instructions are here: https://www.nuget.org/packages/Npgsql/

You need to connect over SSL to AWS which isn't setup by default

2. Download the AWS public key from here https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

3. Convert that to a certificate using https://www.sslshopper.com/ssl-converter.html (convert to PKCS#7/P7B)

4. Import that certificate to the Trusted Room Certificate described here http://www.cs.virginia.edu/~gsw2c/GridToolsDir/Documentation/ImportTrustedCertificates.htm

5. Test access from Power BI desktop using the PostgresSQL connector and it should work

OR:

You can connect PostgreSQL using ODBC Connection.

https://stackoverflow.com/questions/53921336/an-error-happened-while-reading-data-from-the-provider-...

This is the related document, you can view this content:

https://stackoverflow.com/questions/60102636/power-bi-postgresql-data-import-error-the-remote-certif...

https://www.adoclib.com/blog/an-error-happened-while-reading-data-from-the-provider-the-remote-certi...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.