Connecting to PostgresSQL hosted on AWS RDS?

[edwardrmiles]

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)

[v-yuezhe-msft]

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

[edwardrmiles · 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

[abnerrolim]

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

[PowerBILover92]

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.

[PowerBILover92]

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.

[datavigilante]

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

[edwardrmiles]

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

[datavigilante]

@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

[ppalacin]

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

Spoiler

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