PostgreSQL Connector - The remote certificate is invalid according to the validation procedure

[ReneMoawad]

Hello everyone,

I want to connect Power BI to a PostgreSQL located in AWS RDS, everything is fine on Power BI desktop.

But when I try to refresh the data in Power BI service, I receive this error "The remote certificate is invalid according to the validation procedure".

Please note that I don't have a server to download the gateway on so I don't want to use ODBC.

[v-xiandat-msft · Accepted solution]

Hi @ReneMoawad ,

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

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/

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

Below is the post will help you:
Solved: The remote certificate is invalid according to the... - Microsoft Fabric Community

Best Regards,

Xianda Tang

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

[ReneMoawad]

Update from Microsoft Support:
For PostgreSQL DB hosted on AWS RDS, it necessitates an on-premises data gateway for refreshing from PBI Service, as the AWS public key needs to be installed on the gateway machine.

[v-xiandat-msft · Accepted solution]

Hi @ReneMoawad ,

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

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/

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

Below is the post will help you:
Solved: The remote certificate is invalid according to the... - Microsoft Fabric Community

Best Regards,

Xianda Tang

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

[ReneMoawad · Accepted solution]

Hi @v-xiandat-msft,

The above solution worked on Power BI Desktop but not on Power BI Service

Still showing the error "The remote certificate is invalid according to the validation procedure"

Thank you,