Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Gaël
Regular Visitor

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.

1 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

View solution in original post

13 REPLIES 13
asirota
New Member

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
Not applicable

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

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

Did you mange to find a solution?

peltolai
Regular Visitor

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
Microsoft Employee
Microsoft Employee

@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

 

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!!!

 

@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

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
Regular Visitor

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
Regular Visitor

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,

 

Can you pls explain how do you installed the certificates

@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

@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.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.