Forum Discussion
Connecting to PostgresSQL hosted on AWS RDS?
Anonymous 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
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
Anonymous 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
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'.
This solution totally worked for me.Thanks a ton!
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. Anonymous Did you got any solution?
@aqavi10 Did you have access the link in the 4th step?
Anonymous & 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-certificateIf 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
Hi edwardrmiles,
In order for this to work does the host name have to end in amazon.com or amazonaws.com? The host name given to me from my client looks like this:
prod-dbpgsql-aaxxx.rds.us-east-1a.aws.systems.compnamedata.tools
I'm having the issue where it throws the error "The specified hostname was not present in the certificate" even though I have installed the certificate:
thanks in advance for any help!
David
datavigilante I'm not too sure. I don't have acess to that machine to check now either. Sorry!
edwardrmiles, no problem.
I just got mine to work by using nslookup to see what the DNS record mapped to and then connected Power BI to the fully qualified db location. And yes, it does end with amazonaws.com... :)
thanks,
David
Thanks, I did it and it worked in Power BI Desktop, but web refresh still won't work. Is there a way to make web refresh work?
Hi!
I tried this solution step by step, but it did not work. I received this error message in response:
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.
Hi
Thank you. This is beyond my skills. I will elevate this to our IT group.
The link on step 4 does not work. Do you have a alternative source for instructions ?
That link is dead, but you can still find it on Internet Archive:
https://web.archive.org/web/20160707140032/http://www.cs.virginia.edu/~gsw2c/GridToolsDir/Documentation/ImportTrustedCertificates.htm
It's old though - this might be a better guide:https://woshub.com/updating-trusted-root-certificates-in-windows-10/
