cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nmsivasankar
Frequent Visitor

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

23 REPLIES 23
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?

wshapiro
Frequent Visitor

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

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

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

jpd99
Frequent Visitor

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

Same issue with cloud service. Please help.

Hi,

I also managed to connect in PBI desktop, but once trying to refresh in the service, it gives the same error.

Any idea how to solve this?

Thanks & best regards,

Levien

Im having the same issue.

Hello, Test this..

File -> Data Source Settings -> Edit Permissions worked for me. In the Encrypted Connections Option Uncheck the Check.

Regards!!!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.