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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
edwardrmiles
Helper III
Helper III

Connecting to PostgresSQL hosted on AWS RDS?

Has anyone managed to do connect to PostgresSQL hosted on AWS RDS? if so how did you do it?

 

It appears from a few forum and ideas posts that it wasn't possible when PBI was released but I was wondering if the situation had changed or perhpas someone had come up with a work around?

 

(ps I know there is a PostgresSQL on-prem connector and AWS Redshift connector)

1 ACCEPTED SOLUTION

@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

38 REPLIES 38
jbm1101
Regular Visitor

Recently Amazon changed the link to download the certificate. To download the latest certificate go to: Using SSL/TLS to encrypt a connection to a DB instance or cluster - Amazon Relational Database Servi...

Download the global-bundle.p7b or the bundle that corresponds to the region of the RDS. Then add certificate to Trusted Root Certificate.

I've added the global bundle cert from the AWS to Trusted Root Certificate, but I still get the error on Power BI - "The remote certificate is invalid according to the validation procedure". Have you managed to connect to Postgresql database with encryption?

pan_levko
New Member

I am struggling with the same issue connecting power bi to couldSql database on GCP.

It doesn't work if i select a direct PostgreSQL connection. Error is 'The remote certificate is invalid according to the validation procedure'.

But if I use ODBC and set 'sslMode=required' then the connection works. (I must to use direct PostgreSQL connection for my solution).

Anyone faced similar issue? it's very urgent for me unfortunately, thanks in advance!

dustout
Advocate I
Advocate I

I ran into the same error, and disabling encryption will also make that error go away. 

 

To disable encryption

  1. File --> Options and settings --> Data source settings
  2. Click Edit Permissions on your connection
  3. Uncheck Encrypt Connections

This totally worked for me.
Thank you!

vlad_drigin
Frequent Visitor

I have noticed that some links in the solution do not work.
Here is what I did:

1. Install Npgsql 
2. Download the AWS public key and convert it to a certificate 

3. Apply the Certificate

4. Download and Install On-premises data gateway

The detailed instructions can be found here: https://www.tradefinanceglobal.com/posts/connecting-powerbi-web-to-postgressql-hosted-on-aws-relatio...

loic_cadiot
New Member

Hello @aqavi10 , @edwardrmiles , @Anonymous ,

 

I have successfully followed all the instructions but still getting the "The remote certificate is invalid according to the validation procedure" error.

 

However, I'm missing the last step when moving certificates to "Local Computer". Indeed, I've no such folder on my machine :

certificates.png

 

Could it be the reason why I still get the error? What should I do to make it work?

pbi certificate error.png

Thanks a lot !

Loïc

abnerrolim
New Member

I tried these instructions above, but i need to figure out another detail: you need to specify the host full name (e.g: mydb-production-read.sdjalsajdlskja3j.sa-east.rds.amazon.com) not the friendly name behind your dns.
I tried connect with the internal name of our vpc, like mydatabase.prod.mycompany.com and fail because this host isn't especified on amazon certificate.

""Npgsql.Tls.ClientAbortException: Certificate Unknown: Server certificate was not accepted. The specified hostname was not present in the certificate."

Hello,

 

Have you figured out this? I am facing the same issue after doing the exact instructions above.

 

Given that I am providing the IP Address not the full Host name.

 

Hello,

 

Have you figured this out? I am facing the same problem and done the above instructions, given that I am giving a IP address for the server name.

 

 

v-yuezhe-msft
Microsoft Employee
Microsoft Employee

Hi @edwardrmiles

Have you tried to use the on-pre PostgreSQL connector in Power BI Desktop to connect to PostgreSQL hosted on AWS RDS? What problem do you get?


Thanks,
Lydia Zhang

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

@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

The link on step 4 does not work. Do you have a alternative source for instructions ?

 

AWS makes their certificate bundles available in the correct format so you don't need to convert.

You can download the bundle for specific regions from: Certificate bundles for specific AWS Regions 

Thank you!!!! @colivier I spent all day trying to resolve this issue and your the first one that posted a link with the updated AWS certs by region already converted and that did the trick!  

Can you help me with the steps on how you installed the certs please? I tried installing the AWS certs, both the global one and the regional one, but I still get the error on Power BI - 'The remote certificate is invalid according to the validation procedure'. 

Thank you. This is beyond my skills. I will elevate this to our IT group.

Anonymous
Not applicable

Hi. I am trying to connect AWS sql postgres with power bi and I have got several errors, you have an idea what may be the problem (problem is An error occurred while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.' ") I found this forum but this solution does not work for me either, l

 

 

@Anonymous : I have updated the gdrive link in my previous reply which should help you download the remote certificate. However, please follow the instructions available in the Microsoft url attached previously in order to convert the certificate and install it. Try restarting power BI or your PC once you are done installing the certificate and give it a go. Also, please post screenshots of the errors and the steps you have tried prior to seeking help.

Anonymous
Not applicable

attached photos.

Error1.jpgcertificat1.jpgcertificat1.jpgcertificate2.jpgcertificate3.jpgcertificate4.jpgError1.jpgphoto1.jpg

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.