The remote certificate is invalid according to the validation procedure - Connecting Postgres

[nmsivasankar]

Experiencing error while trying to connect the PostgresSQL DB.  I am trying to connect my Power BI Destktop to Postgres RDS hosted on the AWS, below error occuring when I try to setup my connection.

Details: "An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'"

[stino123]

Alternatively you can change in data source settings.  Turn of encrypt conections.

[kondado]

Thaaaaank you so much stino123 for mentioning this. The ODBC Driver's performance only kept getting worse as of late. Now I can finally use the integrated Power BI Connector for PostgreSQL instead.

[Patrick85]

Could it help to create a custom domain which SSL Certificate is signed by a different registrar than Amazon?

[Sukhdev]

it dose not work like that you postgres server needs and ssl certificate not you domain at least in this case so what happenes it the certificat that is generated is not generated via global trusted root ca so it fails.

apart form it i still doubt why microsoft havent provides a input to provide custom root ca for power bi service connection.

[Anonymous]

Same issue, but I connecting docker citus, Is it supported?

[Anonymous]

😄 it working: https://community.powerbi.com/t5/Desktop/AWS-Postgres-The-remote-certificate-is-invalid/m-p/930810

[Anonymous]

For what it's worth, I tried the above solution and it didn't work. However, this simple fix did:

https://community.powerbi.com/t5/Desktop/AWS-Postgres-The-remote-certificate-is-invalid/m-p/930810

Note: I am connecting to an AWS DB over an ssh tunnel.

[dpeacockTHH]

Quick question from someone who is STRUGGLING with this PostgreSQL connector.  For the server name, I have "1.amazonaws.com:5432" at the end.  Do I include the ":5432" in the server name or just stop at amazonaws.com?   So glad you're able to get into yours.  I'm still trying.

[PeterBlatchley]

The link in Step 4 of the solution is broken/restricted. Does anyone have a new link?

[wshapiro]

Yes, I was able to get the connection to work on desktop and online - see my post on 10-26-2020

[Hossam]

I get the Same Message but online whenever I try to edit the credentials so It can refresh without a gateway. the refresh works on the PBI desktop when I refresh it but doesn't work online,

What's the Solution?

[wshapiro]

I needed the gateway for data to be refreshed and rendered on powerBI online

[wshapiro]

Same issue here: Power BI Desktop connection worked after installing cert, but now I am receiving the error for PowerBI cloud. Does anyone have the solution for PowerBI Cloud?

[wshapiro]

Here are the steps I took to getting Postgresql to work on PowerBI Service:

1. Guide to setting up PowerBI connection to AWS RDS on PowerBI desktop: https://medium.com/@vikasthamke/connection-of-power-bi-with-rds-postgresql-in-aws-from-scratch-4712d...

2. Guide to setting up Gateway to enable Postgres data to be accessed on PowerBI Service (web version): https://docs.microsoft.com/en-us/archive/blogs/chmitch/complete-guide-to-setting-up-power-bi-connect...

Install Npgsql 3.1.8 via the MSI

Follow Part 4

Enable gateway on PowerBI Service

[MoorsTech]

@wshapiro @v-jiascu-msft according to this page in the documentation, PostgreSQL is supported by PowerBI without the use of a gateway. Not having to use a gateway has a number of potential advantages (like being able to share an app outside your tenant). I don't get how microsoft documents this as a supported feature but then does not seem to at all document how to use this feature.

[wshapiro]

@MoorsTechI don't understand why it is so hard to connect Postgres either. Also, I don't understand why a gateway is needed.

Maybe @v-jiascu-msft can provide more detail or an official guide as they appear to be an employee at Microsoft

[v-jiascu-msft]

Hi @nmsivasankar,

1. Did you allow the connection in the AWS?

2. Did you use the proper certification?

3. Can you provide more details of the error message, please? Especially the IDs.

A reference: Certificate-issue-when-connecting-a-PostgreSQL-database-to-an-On.

Best Regards,
Dale

[nmsivasankar · Accepted solution]

This was resolved after following the steps below ( Original link on - https://community.powerbi.com/t5/Integrations-with-Files-and/Connecting-to-PostgresSQL-hosted-on-AWS... )

@v-yuezhe-msft 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 (on my Windows 10 machine anway)

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

In conclusion it's possible to connect to PostgresSQL on AWS RDS databases but it isn't easy

[broberti]

The procedure works on desktop app, but what about the web portal app.powerbi.com?

It gives me the same error when publishing the report

 Failed to update data source credentials: An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'Show details

[darling_midas]

We are also running into this same issue.