Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andyclap
Helper II
Helper II

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
Super User

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

7 REPLIES 7
andyclap
Helper II
Helper II

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.

v-eqin-msft
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.

Is there already a working solution available? I've tried above proposed solution, including using a qualified aws domain and physical ip address. The standard Postgres adapter in Power BI is still not working. There are no issues when connecting via database management tools, like PGAdmin or DBeaver, or other BI tools, like Qlik Sense.

 

Regards Frank van Zuilen

Unfortunately not.

 

The Aws root certificates are not trusted for some reason (anticompetitive?)

 

You can put something inbetween, either your own PBI gateway or some other form of proxy, but this adds complexity.

 

As mentioned we went for postgres on Azure, and it's working ok for us.

GilbertQ
Super User
Super User

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors