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.

[Anonymous · 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.

[Hemadri]

Power BI Desktop

--Uncheck Encrypt connections in Transform Data-->Data source settings-->Edit permissions --> Uncheck Encrypt connections

Power BI Service Schdule Refresh

-- Install On premises Data Gateway standard mode and login with account logged in power bi service

-- Download and install with Npgsql GAC Installation checked

 https://github.com/npgsql/npgsql/releases/download/v4.0.17/Npgsql-4.0.17.msi
--Restart the on-premises gateway and machine

-- Open power BI Service

-- Gateway connections--> On premises Gateway settings --> Check all settings --> Apply

-- Open semantic model--> settings--> Gateway connections--> turn on onpremises--> Add Gateway --> keep Encrytion method as Not Encryted and Save.

[fheijmans]

Hi there,

At some point, we set up a connection between Power BI and AWS RDS PostgreSQL, both from a Windows virtual machine (AWS EC2) that runs the native Power BI app and from an on-premise gateway server that allows the connection from the Power BI cloud / website.

Recently, AWS RDS notified us that our current certificate (rds-ca-2019) is about to expire in August of this year (2024) and we should start using the new certificates.

Hence, I followed the steps in the solution again to set up the new certificate. Similar to you, this worked for me in connecting the server that runs the native Power BI app, but it did not work in the gateway server. I get the same "The remote certificate is invalid according to the validation procedure" error in Power BI when I try to refresh my data there, but through the app it works perfectly fine.

Any idea what could be going wrong there?

[DoubleBN]

I'm having the same issue. Did you find a way to resolve this?

[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.

[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.

[Anonymous · 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.

[KalpeshClearDu]

i have tried that all things that you mentioned here . but still i got the error "The remote certificate is invalid according to the validation procedure". What has missed there?I cant understand.Please Help 

[ReneMoawad · Accepted solution]

Hi @Anonymous,

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,