Unable to connect due to remote certificate is invalid according to the validation procedure.'

[attszabo]

Hey Community,

I really need your help, at this moment I'm quite disappointed. I know this issue has been raised before but in my case the suituation is slightly different.

RDBMS: PostgreSQL ver 12

Vendor: DigitalOcean droplet

My issue is that with certbot I can't create a certificate without domain (doesn't accept the data server's IP address and there's no domain name). I tried several sources to find a solution, but most of the either related to AWS or recommending to use ODBC or untick Encrypt connection.

Ideally I would like to fix this problem so I can setup scheduled refreshes for the report.

I have full access to the data server via Putty.

Your help is much appreciated. Please let me know if some important detail(s) are missing.

Thank you

Reviewed several threads and websites already:
https://community.fabric.microsoft.com/t5/Service/Add-an-SSL-Certificate-to-PostgreSQL-Database-Conn...

https://community.fabric.microsoft.com/t5/Service/PostgreSQL-data-source-using-encrypted-connection-...

Reading through even these blogs: https://dataintoresults.com/post/powerbi-and-postgresql-ssl-lets-encrypt-and-gateway/

https://pavelevstigneev.medium.com/postgresql-ssl-with-letsencrypt-b53051eacc22

[Anonymous]

Hi @attszabo ,

1. If you’re using DigitalOcean Managed Databases, each managed database comes with an SSL certificate.
How to Connect to PostgreSQL Database Clusters :: DigitalOcean Documentation

2. You can use OpenSSL to generate a self-signed certificate. This would allow you to use SSL without a domain name. 
PostgreSQL: Documentation: 16: 19.9. Secure TCP/IP Connections with SSL

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[attszabo]

@Anonymous Thank you  for the suggestions.

I am using a droplet not a managed database, but for the purpose of testing I created a new managed database.

Option A

1. Downloaded the certificate: ca-certificate.crt

2. Opened the certificate and installed it for the Current User and let it automatically select the certificate store based on the type of certificate.

3. Setup postgresql connection in PowerBI and I got the same 'The remote certificate is invalid according to the validation procedure.'  error message.

Option B

Tried your 2nd solution with OpenSSL following the instructions and I installed the root.crt certificate for the Current users. Then the following steps were the same as above.

In both cases I still received The remote certificate is invalid according to the validation procedure. error. This is getting pretty annoying and in my case ODBC would be a temporary solution. Unticking Encrypt connections would only solve refresh within PowerBI desktop, but once published on server and setup a scheduled refresh the same error would appear.

Any recommendations on how to proceed would be much appreciated. 

Thank you.

@Anonymous