Forum Discussion
AWS RDS Postgres refresh from PowerBI service - the remote certificate is invalid
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.
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.
