Forum Discussion
PostgreSQL + powerbi desktop connection error
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.
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
13 Replies
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
- Click Start, click Start Search, type mmc, and then press ENTER.
- On the File menu, click Add/Remove Snap-in.
- Under Available snap-ins, click Certificates, and then click Add.
- Under This snap-in will always manage certificates for, click Computer account, and then click Next.
- Click Local computer, and click Finish.
- If you have no more snap-ins to add to the console, click OK.
- In the console tree, double-click Certificates.
- Right-click the Trusted Root Certification Authorities store.
- Click Import to import the certificates and follow the steps in the Certificate Import Wizard.
Best Regards,
Herbert
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!!!
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
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.
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:
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()'"I'm also using Heroku and trying to solve the same issue.
Did you mange to find a solution?
