Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
edwardrmiles
Helper III
Helper III

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

@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

34 REPLIES 34
Anonymous
Not applicable

Sorry, I can't add pictures to steps 4 and 5.

So, in step 4 right-click and select install certificate

aletorres_BI_0-1599755225323.png

Select where you want to put all certificates, I select Trusted Root Certification Authorities

aletorres_BI_1-1599755337760.png

5. Move the certificate from Current user - Trusted Root Certification Authorities to Local Computer - Trusted Root Certification Authorities using MMC command.

 
Anonymous
Not applicable

2020-09-10T13_35_24.png

Hello everyone,

thanks @Anonymous this is great help. It is working on my local computer with my local gateway - just perfect. However after installing everything to my remote server (also windows 10 frontend) with the same steps involved. I will always get an error code when I want to enter my credentials for this dataset. It is not working. 

 

I am using the latest Gateway version

 

Do you have a  clou why?

 

2020-10-30 09_43_51-ts03.avance.intern - Remotedesktopverbindung.png2020-10-30 09_43_59-ts03.avance.intern - Remotedesktopverbindung.png

2020-10-30 09_47_40-ts03.avance.intern - Remotedesktopverbindung.png2020-10-30 09_44_56-ts03.avance.intern - Remotedesktopverbindung.png

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. @SarithaVM Did you got any solution?

 

@aqavi10 Did you have access the link in the 4th step?

@SarithaVM  &  @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

@edwardrmiles 

This solution totally worked for me.Thanks a ton! 

Hi!

 

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

Sem título.jpgSem título2.jpg

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

 

 

@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

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors