9

I'm receiving the following error when connecting to an AWS Postgres database that requires SSL. I recently upgraded from npgsql 2.3.2 (which was buggy) to 3.0.3 which won't connect. Any suggestions would be appreciated.

DataSource.Error: TlsClientStream.ClientAlertException: CertificateUnknown: Server certificate was not accepted. Chain status: A certificate chain could not be built to a trusted root authority. . 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) Details: DataSourceKind=PostgreSQL

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
Jon Capezzuto
  • 93
  • 1
  • 4

5 Answers5

9

I was able to fix the issue by installing the Amazon RDS public certificate on my machine. Once I did this, I was able to connect.

Steps I followed:

  1. Download the AWS RDS public certificate 1
  2. Create a .crt file from the .pem file downloaded. Sample instructions here 2
  3. Install the certificate (.crt file) on the machine. 3
  4. Connect!
Community
  • 1
  • 1
VinnyP
  • 106
  • 1
  • 2
  • 1
    I have performed all these 3 steps, but still it does not connect. The error persist. What can be the possible cause? – Luv33preet Nov 09 '17 at 16:56
5

The docs from npgsql give the solution as changing the default trust server certificate of 'false' to 'true' in the connection string. Unfortunately, neither Excel (AFAIK) nor Power BI will allow you to edit the connection string. So if you are unable to get the SSL certificate from the DB admin (as suggested in another answer), or the SSL cert has a different server name to the name you connect to (in my case an IP address), there is not much that can be done.

I can see two ways of fixing this. Either Shay & co from npgsql (who are doing an excellent job btw) provide some way for users to change the default settings for the connection string parameters. Or Microsoft allows users to send keywords in the connection dialog of Power BI (and Excel).

Community
  • 1
  • 1
a-burge
  • 1,535
  • 1
  • 13
  • 25
  • 2
    Thanks for the comment Larry, appreciated and acknowledged. Still, since I have now spent more than three whole days searching for a solution to this problem in vain, I felt it was valuable to provide an answer which states that there is currently no solution to this problem. It might save subsequent users from going through the same as I have. – a-burge Sep 13 '16 at 17:46
3

Npgsql 2.x didn’t perform validation on the server’s certificate by default, so self-signed certificate were accepted. The new default is to perform validation, which is probably why your connection is failing. Specify the Trust Server Certificate connection string parameter to get back previous behavior.

You can read more on the Npgsql security doc page, note also that this change is mentioned in our migration notes.

Shay Rojansky
  • 15,357
  • 2
  • 40
  • 69
  • That is very helpful. Unfortunately Power BI Desktop doesn't allow direct editing of the connection strings. I'll report this issue to Microsoft for resolution. – Jon Capezzuto Oct 22 '15 at 11:34
  • Again, I know nothing about Power BI but note that the new "Trust Server Certificate" parameter is a "standard" one, supported in SqlClient. There should be a way for you to access the connection string somehow... – Shay Rojansky Oct 22 '15 at 13:21
  • Jon Capezzuto, did you get any response from Microsoft? – user541905 Mar 10 '16 at 12:42
3

I had the same issue connecting PowerBI to a locally hosted PostgreSQL server and it turned out to be easy to solve if you can get the right information. Recent Npgsql versions will only connect over SSL if it trusts the certificate of the server. As a Windows application PowerBI uses the windows certificate store to decide what to trust. If you can get the SSL cert for the PostgreSQL server (or the CA cert used to sign that one) then tell Windows to trust that certificate, PowerBI will trust it too.

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 cert. Note NOT the key file which contains the private key, only the cert file which contains the public key. copy it or its content to the machine running PowerBI and import using Run | mmc | Add Plugin... Certificates (Google it)

Look at the server name once you imported the cert and connect from PowerBI using the same server name (so the cert matches the connection). That solved the problem for me. If PostgreSQL is configured to insist on a SSL connection you might have to do the same for a ODBC connection too.

Ruan Malan
  • 41
  • 4
  • how do I import the postgresql cert in excel? – Luv33preet Nov 09 '17 at 06:21
  • Start > Run > mmc.exe. File > Add/Remove Snap-in; Standalone tab > Add; Certificates snap-in > click Add; Wizard > Computer Account> Local Computer > Finish. Close the dialog. Navigate to Certificates (Local Computer). If you have the certificate for the server itself, choose Other People. Right-click the store and choose All Tasks --> Import. Follow the wizard and provide the certificate file you have; – Ruan Malan Nov 10 '17 at 09:21
  • actually I have done that import but I keep getting the same error with description that server name is not contained in the certificate – Luv33preet Nov 10 '17 at 09:42
1

Its not best way but worked for me since if u dont need encryption for security reason.

Go to Postgres config file on your DB server and go from

ssl = true

to

ssl = false

Then open your power bi desktop File-> Options and settings -> Data source settings -> then in global you will have saved your connection press Edit Permissions and uncheck "ENCRYPT CONNECTIONS"

Then it will work

WARNING: THIS IS NOT RECOMMENDED IF YOUR DB IS OPEN TO PUBLIC.

Regards,

Davlik

Davlik
  • 39
  • 3