topic Re: Connecting to PostgresSQL hosted on AWS RDS? in Power Query

Connecting to PostgresSQL hosted on AWS RDS?

edwardrmiles — Thu, 02 Mar 2017 14:25:56 GMT

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)

Re: Connecting to PostgresSQL hosted on AWS RDS?

Anonymous — Fri, 03 Mar 2017 02:26:29 GMT

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

Re: Connecting to PostgresSQL hosted on AWS RDS?

edwardrmiles — Fri, 03 Mar 2017 10:45:56 GMT

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

Re: Connecting to PostgresSQL hosted on AWS RDS?

abnerrolim — Wed, 29 Mar 2017 14:42:25 GMT

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."

Re: Connecting to PostgresSQL hosted on AWS RDS?

PowerBILover92 — Tue, 10 Oct 2017 10:49:20 GMT

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.

Re: Connecting to PostgresSQL hosted on AWS RDS?

PowerBILover92 — Tue, 10 Oct 2017 10:53:50 GMT

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.

Re: Connecting to PostgresSQL hosted on AWS RDS?

datavigilante — Tue, 19 Dec 2017 20:09:38 GMT

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:

screenshot from MMC

thanks in advance for any help!

David

Re: Connecting to PostgresSQL hosted on AWS RDS?

edwardrmiles — Wed, 20 Dec 2017 12:01:25 GMT

datavigilante I'm not too sure. I don't have acess to that machine to check now either. Sorry!

Re: Connecting to PostgresSQL hosted on AWS RDS?

datavigilante — Thu, 21 Dec 2017 17:46:44 GMT

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

Re: Connecting to PostgresSQL hosted on AWS RDS?

ppalacin — Sun, 04 Feb 2018 23:02:43 GMT

I am running into the same issue.

I have a PostreSQL running in IBM Cloud (Compose) that provides me with a pregenerated SSL-Cert

I have copied that cert into my local certificate storage but npgsql throws me now the hostname error mentioned before.

Is there any chance to disable the hostname check/ ssl vaildator?

I need this to work to configure my enterprise data gateway.

BR,

Patrick

Aktivitäts-ID:	1e4eeb70-41bd-45a3-926f-5ba1af8879c6
Anforderungs-ID:	51aba6b6-dde7-0d67-db75-e11637a509d3
Cluster-URI:	https://wabi-north-europe-redirect.analysis.windows.net
Statuscode:	400
Fehlercode:	DMTS_PublishDatasourceToClusterErrorCode
Zeit:	Sun Feb 04 2018 23:37:52 GMT+0100 (W. Europe Standard Time)
Version:	13.0.4164.178

TC-GW-MUNICH-OFFICE:	Mit der Mashupdatenquelle kann keine Verbindung hergestellt werden. Weitere Informationen finden Sie in den Fehlerdetails.
Zugrunde liegender Fehlercode:	-2147467259
Zugrunde liegende Fehlermeldung:	An error happened while reading data from the provider: 'Npgsql.Tls.ClientAlertException: CertificateUnknown: Server certificate was not accepted. The specified hostname was not present in the certificate. at Npgsql.Tls.TlsClientStream.SendAlertFatal(AlertDescription description, String message) at Npgsql.Tls.TlsClientStream.ParseCertificateMessage(Byte[] buf, Int32& pos) at Npgsql.Tls.TlsClientStream.TraverseHandshakeMessages() at Npgsql.Tls.TlsClientStream.<GetInitialHandshakeMessages>d__43.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Npgsql.Tls.TlsClientStream.<PerformInitialHandshake>d__72.MoveNext()'
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	PostgreSQL
Microsoft.Data.Mashup.ValueError.DataSourcePath:	sl-eu-fra-2-portal.3.dblayer.com:15xxx;mydb
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error

Re: Connecting to PostgresSQL hosted on AWS RDS?

paulob_involves — Wed, 28 Mar 2018 21:37:57 GMT

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?

Re: Connecting to PostgresSQL hosted on AWS RDS?

hugoventurini — Fri, 18 May 2018 11:30:46 GMT

Hi!

I tried this solution step by step, but it did not work. I received this error message in response:

Re: Connecting to PostgresSQL hosted on AWS RDS?

aqavi10 — Thu, 11 Jul 2019 00:49:13 GMT

edwardrmiles

This solution totally worked for me.Thanks a ton!

Re: Connecting to PostgresSQL hosted on AWS RDS?

Anonymous — Tue, 02 Jun 2020 11:03:47 GMT

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."

Re: Connecting to PostgresSQL hosted on AWS RDS?

schinchole — Wed, 03 Jun 2020 11:51:15 GMT

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?

Re: Connecting to PostgresSQL hosted on AWS RDS?

aqavi10 — Fri, 24 Jul 2020 01:04:04 GMT

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

Re: Connecting to PostgresSQL hosted on AWS RDS?

Anonymous — Thu, 23 Jul 2020 21:08:59 GMT

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

Re: Connecting to PostgresSQL hosted on AWS RDS?

aqavi10 — Fri, 24 Jul 2020 01:10:33 GMT

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.

Re: Connecting to PostgresSQL hosted on AWS RDS?

Anonymous — Fri, 24 Jul 2020 02:52:37 GMT

Hi aqavi10

I followed all the steps indicated in the comment, the error still persists.
Download the certificate, install it in the MMC in the option certified user and equipment.aqavi10,

https://modella-my.sharepoint.com/:f:/g/personal/alvaro_borquez_modellagroup_com/Eo1c759wjsFDk7x5S-VSuAEBzHqQl9QsoX3m5PD4pEZqgQ?e=zhf1hh

Re: Connecting to PostgresSQL hosted on AWS RDS?

Anonymous — Fri, 24 Jul 2020 03:01:30 GMT

attached photos.