Forum Discussion
Connecting to PostgresSQL hosted on AWS RDS?
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
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
SpoilerAktivitä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
