PostgreSQL connection via On Premise Gateway fails due to untrusted SSL certificate

[petar_gyurov]

Hi,

I have successfully connected to my PostgreSQL database with Power BI Desktop - I initially had the same error there, but after making the SSL certificate trusted on my machine, the issue was resolved.

However, I am now trying to connect to the same PostgreSQL database on Power BI cloud, using a On-Premise Gateway. I run into the same error as before, only this time I have no way of making the certificate trusted.

The error in question:

Unable to connect: We encountered an error while trying to connect to powerbi.ctzb8ork1zix.eu-west-1.rds.amazonaws.com. Details: "Cannot connect to the mashup data source. See error details for more information."Hide details
Activity ID:	9c104039-50ff-4d6f-92b0-2961270d5370
Request ID:	8d8d3b7a-0763-466f-9764-a5b72aebcd58
Cluster URI:	https://wabi-north-europe-redirect.analysis.windows.net
Status code:	400
Error Code:	DM_GWPipeline_Gateway_MashupDataAccessError
Time:	Fri Sep 01 2017 16:11:34 GMT+0100 (GMT Summer Time)
Version:	13.0.2278.219
Underlying error code:	-2147467259
Underlying error message:	An error happened while reading data from the provider: 'TlsClientStream.ClientAlertException: CertificateUnknown: Server certificate was not accepted. Chain status: A certificate chain processed, but terminated in a root certificate which is not trusted by the trust provider.
 .
 at TlsClientStream.TlsClientStream.SendAlertFatal(AlertDescription description, String message)
 at TlsClientStream.TlsClientStream.ParseCertificateMessage(Byte[] buf, Int32& pos)
 at TlsClientStream.TlsClientStream.TraverseHandshakeMessages()
 at TlsClientStream.TlsClientStream.GetInitialHandshakeMessages(Boolean allowApplicationData)
 at TlsClientStream.TlsClientStream.PerformInitialHandshake(String hostName, X509CertificateCollection clientCertificates, RemoteCertificateValidationCallback remoteCertificateValidationCallback, Boolean checkCertificateRevocation)'
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	PostgreSQL
Microsoft.Data.Mashup.ValueError.DataSourcePath:	powerbi.ctzb8ork1zix.eu-west-1.rds.amazonaws.com;postgres
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error

How can I fix this?

[v-yuezhe-msft]

@petar_gyurov,

Firstly, copy SSL certificate for the PostgreSQL server to the machine that installing Power BI gateway and import it to Windows certificate store.

Secondly, in the configuration folder for the PostgreSQL server there is a postgresql.conf file, search it for SSL settings, there is one with the location of the SSL certificate. Take a look at the server name when you import the SSL certificate and connect from Power BI using the same server name.

Here is a similar thread for your reference.
http://community.powerbi.com/t5/Integrations-with-Files-and/Unable-to-connect-to-PostgreSQL-with-SSL...

Regards,
Lydia

[petar_gyurov]

Hi @v-yuezhe-msft,

Thanks for the reply but Imay require a bit more help.

Firstly, I cannot find the path to the SSL certificate when looking at the postgresql.conf file. This is the line in the file:

#ssl_cert_file = 'server.crt'		# (change requires restart)

I cannot find this file anywhere in the Postgres directory.

I had this issue with Power BI Desktop and to solve it I downloaded the SSL certificate from Amazon. I added this certificate to the Windows Certificate Store and the issue was resolved. So I figured that if the gateway is setup on the same machine, the issue would not persist with the Power BI cloud, but it does.

Am I missing something?

[v-yuezhe-msft]

@petar_gyurov,

You can try to install Power BI gateway on the same machine that installs Power BI Desktop and check if the above error goes away.

And I would recommend you post the SSL certificate issue in PostgreSQL community to get better support.

Regards,
Lydia

[petar_gyurov]

The gateway is already set up on the same machine, which is why I thought it should work. Any ideas?

[v-yuezhe-msft]

@petar_gyurov,

Please post the SSL certificate issue in PostgreSQL community to get dedicated support.

Regards,
Lydia

[petar_gyurov]

I don't think this is PostgreSQL issue. I have successfully connected to my PostgreSQL database with PowerBI Desktop, using the certificate provided by my hosting provider, i.e: Amazon. 

I believe the issue lies with the PowerBI service itself. Here are the replication steps:

1. Go to Manage Gatways

2. Add new source

3. Select PostgreSQL

4. Enter server, database and credentials

5. Press Add

6. Error message in initial post shows up

As I said before, I have the SSL certificate authenticated on the machine used as the gateway.