PostgreSQL + powerbi desktop connection error

[Gaël]

Hi!

I'm trying to connect my PB desktop to a PostgreSQL DB but the following error is generated:

Detalles: "Se produjo un error al leer datos desde el proveedor: 'TlsClientStream.ClientAlertException: CertificateUnknown: Server certificate was not accepted. Chain status: Se procesó correctamente una cadena de certificados, pero termina en un certificado de raíz no compatible con el proveedor de confianza.
. The specified hostname was not present in the certificate.
en TlsClientStream.TlsClientStream.ParseCertificateMessage(Byte[] buf, Int32& pos)
en TlsClientStream.TlsClientStream.TraverseHandshakeMessages()
en TlsClientStream.TlsClientStream.GetInitialHandshakeMessages(Boolean allowApplicationData)
en TlsClientStream.TlsClientStream.PerformInitialHandshake(String hostName, X509CertificateCollection clientCertificates, RemoteCertificateValidationCallback remoteCertificateValidationCallback, Boolean checkCertificateRevocation)'"

I've made a copy of the certificate of my Postgre server, I've installed it on my windows computer but it is not possible to connect, always the same error.

What can I do, check or change to make this connection works?

Many tahnks for your help.

[asirota]

I am trying to connect to Postgres on Heroku -- on that environment you don't get access to the server public certificate. Instead you must use the following connection parameters:

From this reference

sslmode=Require;Trust Server Certificate=true

The question is where can these parameters be inserted into the PowerBI connection string options? I am getting the following when trying to connect and cannot follow the instructions to add a local key and instead must use the connect string parameters above:

Details: "An error happened while reading data from the provider: 'Npgsql.Tls.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 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.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.Tls.TlsClientStream.<PerformInitialHandshake>d__72.MoveNext()'"

[Anonymous]

Any luck getting Power BI working with Postgres on Heroku?  I am getting the same certificate error

[itamarg]

I'm also using Heroku and trying to solve the same issue.

Did you mange to find a solution?

[peltolai]

For me, the problem wasn't that the certificate wasn't installed.

For me, the problem was that I was using an alias to connect to the database and the certificate didn't include that alias.

We're hosting our database in Amazon RDS.

Switching to use the 'real' hostname for the server fixed this issue.

[v-haibl-msft]

@Gaël

From the error details, server certificate should be the root cause. Please check that if you get the right certificate and trust it on your Power BI Desktop machine with the correct steps.

Where did you get the certificate of your Postgre server? I found the certificate location as below mentioned in other threads.

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 Power BI and import using Run | mmc.

Administrators is the minimum group membership required to complete this procedure.

To add certificates to the Trusted Root Certification Authorities store for a local computer

1. Click Start, click Start Search, type mmc, and then press ENTER.
2. On the File menu, click Add/Remove Snap-in.
3. Under Available snap-ins, click Certificates, and then click Add.
4. Under This snap-in will always manage certificates for, click Computer account, and then click Next.
5. Click Local computer, and click Finish.
6. If you have no more snap-ins to add to the console, click OK.
7. In the console tree, double-click Certificates.
8. Right-click the Trusted Root Certification Authorities store.
9. Click Import to import the certificates and follow the steps in the Certificate Import Wizard.

Best Regards,

Herbert

[Gaël]

Hi Herbert,

Thanks for your help. The certificate I was importing was not the right, after check the posgresql.conf I have had the good one... Or not: Now I have a new error message

"Se produjo un error al leer datos desde el proveedor: 'TlsClientStream.ClientAlertException: CertificateUnknown: Server certificate was not accepted. The specified hostname was not present in the certificate.

I'm trying to connect with an IP direction (internal or external) or with the domain name too and nothing... I'm losing my last hairs!!!

[v-haibl-msft · Accepted solution]

@Gaël

It seems that there is still problem about the certificate. Maybe you can also try to trust the CA cert on both machines. If issue still persists, I think you can look for help in PostgreSQL community, to find that what certificates need to be installed/trusted on client machine (Power BI Desktop).

I just found a document about PostgreSQL Service and Client certificate. Hope it helps.

Best Regards,

Herbert

[Gaël]

Hi @v-haibl-msft,

You're right again. The problem was the certificate in the server. We have installed the good ones and now Pbi works perfectly.

Thanks again.

Best regards!

[shop]

hi @Gaël

I got stuck with that error, and don't know what to do next, Could you share how you fixed it?

Really appreciate your time.

[shop]

Really appreciate your help. 

Would you mind shareing the steps on how to do it? I have managed to add certificate file in my local PC. Then I saw the error message that you specified and got it solved.

regards,

[Sajeevsahadev]

Can you pls explain how do you installed the certificates

[v-haibl-msft]

@Gaël

Could you please list the right certificates which need to be install on the Power BI Desktop machine if convenient?

I’ve saw several users have the same certificate issue when using this function. If you can list the right certificates here, it will be very helpful to them. Thanks in advance.

Best Regards,

Herbert

[Gaël]

@v-haibl-msft

The certificates I've installed in the PostgreSQL server and the Pbi desktop ( creating a .PEM) are:

AddTrustExternalCARoot.crt

DV_NetworkSolutionsDVServerCA2.crt

DV_USERTrustRSACertificationAuthority.crt

my_domain_name.com.crt

I hope it will be useful.