cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

27 REPLIES 27
New Member

Hello @aqavi10 , @edwardrmiles , @aletorres_BI ,

 

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

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.

 

 

Microsoft
Microsoft

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

View solution in original post

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

 

 

@alvaroborquez : 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.

attached photos.

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

 

@alvaroborquez , I've sent a private message. Please check and connect soon.

 

 Thanks

Hi @aqavi10 , I've got the same problem than @alvaroborquez, can you help me? please

Hi @aletorres_BI ,

 

  Please check your inbox. I'll try doing whatever I can.

 

Cheers!

Hi everybody, I solve my issue following these steps:

 

1.- Install Npgsql using Visual Studio.

First, create a new project by default options.

aletorres_BI_1-1599754158343.png

Second, in References select Manage NuGet packages...

aletorres_BI_2-1599754259095.png

Third, Browse Npgsql and install it.

aletorres_BI_3-1599754333400.png

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)

4. Install certificate using right-click in the mouse.

5. Move certificate from Current user to Local computer using MMC command.

Sorry, I can't add pictures to steps 4 and 5.

So, in step 4 right-click and select install certificate

aletorres_BI_0-1599755225323.png

Select where you want to put all certificates, I select Trusted Root Certification Authorities

aletorres_BI_1-1599755337760.png

5. Move the certificate from Current user - Trusted Root Certification Authorities to Local Computer - Trusted Root Certification Authorities using MMC command.

 

2020-09-10T13_35_24.png

Hello everyone,

thanks @aletorres_BI this is great help. It is working on my local computer with my local gateway - just perfect. However after installing everything to my remote server (also windows 10 frontend) with the same steps involved. I will always get an error code when I want to enter my credentials for this dataset. It is not working. 

 

I am using the latest Gateway version

 

Do you have a  clou why?

 

2020-10-30 09_43_51-ts03.avance.intern - Remotedesktopverbindung.png2020-10-30 09_43_59-ts03.avance.intern - Remotedesktopverbindung.png

2020-10-30 09_47_40-ts03.avance.intern - Remotedesktopverbindung.png2020-10-30 09_44_56-ts03.avance.intern - Remotedesktopverbindung.png

Hi,

The link in the 4th step is not accessible. I get a message "

You don't have permission to access /~gsw2c/GridToolsDir/Documentation/ImportTrustedCertificates.htm on this server.

Additionally, a 403 Forbidden error was encountered while trying to use an ErrorDocument to handle the request."

The link in the 4th step is not accessible. I get a message "

You don't have permission to access /~gsw2c/GridToolsDir/Documentation/ImportTrustedCertificates.htm on this server.

Additionally, a 403 Forbidden error was encountered while trying to use an ErrorDocument to handle the request."

 

 

I am also facing same isuue. @SarithaVM Did you got any solution?

 

@aqavi10 Did you have access the link in the 4th step?

@SarithaVM  &  @schinchole 
Hi,
 Yes, the link is forbidden now however, it was previously available. 

In order to install the certificate , please follow the below link. 
https://docs.microsoft.com/en-us/skype-sdk/sdn/articles/installing-the-trusted-root-certificate

 

If anyone is facing difficulty in downloading and converting the certficate , then please download the PKCS converted file from the below gdrive link .
https://drive.google.com/file/d/19OxyegTGGnyx3cYzN8huEFWRPo48fKRc/view?usp=sharing

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors