cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Accepted Solutions

Re: Connecting to PostgresSQL hosted on AWS RDS?

@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

12 REPLIES 12
Moderator v-yuezhe-msft
Moderator

Re: Connecting to PostgresSQL hosted on AWS RDS?

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.

Re: Connecting to PostgresSQL hosted on AWS RDS?

@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

abnerrolim Occasional Visitor
Occasional Visitor

Re: Connecting to PostgresSQL hosted on AWS RDS?

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

Re: Connecting to PostgresSQL hosted on AWS RDS?

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

Re: Connecting to PostgresSQL hosted on AWS RDS?

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

Re: Connecting to PostgresSQL hosted on AWS RDS?

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?

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

datavigilante Regular Visitor
Regular Visitor

Re: Connecting to PostgresSQL hosted on AWS RDS?

@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 Frequent Visitor
Frequent Visitor

Re: Connecting to PostgresSQL hosted on AWS RDS?

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)