cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andyclap
Helper I
Helper I

AWS RDS Postgres refresh from PowerBI service - the remote certificate is invalid

Hi - I have been trying to refresh a PowerBI report based on postgres DB datasource hosted on AWS RDS directly in the PowerBi service, not via a gateway.

 

The report works locally (after installing the AWS root certificates of course).

However on the service when trying to set datasource credentials I get:

 

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

Activity ID:8ee6d48d-fe4a-4b45-a6f2-2be4a5a6d83b
Request ID:5d917a53-5f08-fb88-c355-f7d05286b6ca
Status code:400
Time:Sun Jan 10 2021 02:53:04 GMT+0000 (Greenwich Mean Time)
Service version:13.0.15022.68
Client version:2012.2.04142-train
Cluster URI:https://wabi-uk-south-redirect.analysis.windows.net/

 

 

It looks like you don't have the AWS RDS root certificates installed for the PowerBI servers.

I could allocate a machine or set up a machine in the cloud as a gateway, however this adds a single point of failure (and set-up/support/maintenance cost), and is not a sensible architecture for us.

 

The documentation clearly states that Postgres hosted in the cloud is available for refresh from the PowerBI service.

 

Are you able to trust the AWS root certificates, or do you only plan to support Postgres hosted in the Microsoft Azure cloud ... and we should move over from AWS RDS?

 

1 ACCEPTED SOLUTION
GilbertQ
Super User II
Super User II

Hi @andyclap 

 

As far as I can see you would need to install a Gateway Server so that you could get the right certificate installed.

 

I have found putting the Gateway servers in a cluster allows for there to not be a single point of failure.

 

I would put the Gateway Server in AWS using an EC2 instance so that it is as close as possible to the RDS source.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

5 REPLIES 5
andyclap
Helper I
Helper I

Anyway, we have moved to Azure postgres, which does allow BI connections without a gateway.

 

The whole gateway architecture is a concern for me: while it's completely understandable for access to on-prem data sources, it doesn't feel the right architecture for a BI cloud service.

I'd need an "on-demand" gateway managed cloud service in Azure that is pay by usage (or naturally preferably free for PowerBI Pro) before considering it.

Eyelyn9
Community Support
Community Support

Hi @andyclap ,

 

It's possible to connect to PostgresSQL on AWS RDS databases but it isn't easy.

 

This was resolved after following the steps below ( The original link is here)

 

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

 

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

GilbertQ
Super User II
Super User II

Hi @andyclap 

 

As far as I can see you would need to install a Gateway Server so that you could get the right certificate installed.

 

I have found putting the Gateway servers in a cluster allows for there to not be a single point of failure.

 

I would put the Gateway Server in AWS using an EC2 instance so that it is as close as possible to the RDS source.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

Accepting this as the answer - while it's not the answer I would like, it is correct for the current PowerBI service offering.

 

We're finding Postgres in Azure quite good 🙂

Oh, I was under the impression PostgreSQL could be refreshed from the service without a gateway.

 

Based on this document it is not necessary to install a gateway in order to connect to to PostgreSQL (column Gateway (required) shows No).

 

I would love to be able to afford a cluster of windows servers, but we're not a huge enterprise and so to create and maintain our own "pet" servers just to mediate a cloud service connecting to another cloud service isn't a path we really want to go down.

 

Is there a SaaS offering in Azure for gateway servers that we could just configure and let Azure look after?

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.