cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nmsivasankar
Advocate I
Advocate I

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

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.'"

1 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

 

View solution in original post

View solution in original post

28 REPLIES 28
TPG
New Member

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

Anonymous
Not applicable

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.

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
Frequent Visitor

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

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

Hossam
Regular Visitor

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?

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

wshapiro
Frequent Visitor

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?

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

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

@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
Microsoft
Microsoft

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

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

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

 

View solution in original post

Hi - the link in step (4) no longer works and so I wondered whether anyone has the instructions please? http://www.cs.virginia.edu/~gsw2c/GridToolsDir/Documentation/ImportTrustedCertificates.htm Thanks

@jpd99see my response earlier in this thread for the guides I used to connect Postgres to Power BI Desktop and Power BI cloud

@wshapiro thank you!  I actually had a go using the instructions in your earlier post but wasn't able to view the whole page at sslsupportdesk.  I also tried the video at medium.com for the import but it wouldn't work.  That all said, I'll give it another go from scratchg.  Fingers crossed.  Thanks again

Hi @nmsivasankar ,

 

Thank for your guide. It succeced to connect aws rds postgres on Power BI Desktop. But when I publish to cloud. It has error on Power BI Cloud.

 

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

 

2019-12-31 19_51_38-TestPostgreSQL - Power BI.png

Could you please take a look?

 

Thank and Regard,

Hello All,

 

I am also facing same issue. 

@quangdatnth @ledeh @akoeller

Did you got any solution. If you have can you please help me on this.

 

Thank You.

We are facing the same issue. Was anyone able to resolve this issue on Power BI Service?

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.