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

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

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... Smiley Happy

 

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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 378 members 3,860 guests
Please welcome our newest community members: