Excel2016: Cannot query PostgresSQL database: Server certificate not accepted

Question

I want to import some data to Excel2016 from a postgresSQL table. I have tried it by clicking "new query" and selecting From Database -> From PostgresSQL Database:

But then I receive the following error:

Details: "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. . The specified hostname was not present in the certificate. 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)"

Any suggestions on how to solve this? Thank you so much in advance!

Answer 1

This error is indicative of a connection being made to the PostgreSQL db where the server's certificate cannot be validated by the client making a connection. This error only happens when the "Trust Server Certificate" is set to FALSE in the library Excel uses to connect to PostgreSQL (npgsql).

There are several ways that may work to address this, in the order I'd suggest trying them:

1. If there's an option hidden in Excel (perhaps under advanced options or similar) to set the 'Trust Server Certificate' parameter to True, then your connection will start working. If it allows you to specify an entire connection string, then this can be done in the connection string as well.

2. The database should have a public key in an SSL cert listed in the postgresql.conf file for the db. If you (or your db administrator) can get that public key and add it to your machine (instructions will vary depending on your operating system).