Add an SSL Certificate to PostgreSQL Database Connection

[NathanGTyrenet]

Hi,

I have been using ODBC connection to my PostgreSQL Database because I was unable to get the PostgreSQL connector to work for me due to this error:
"An error happened while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'"

I have created some reports with the ODBC connector and now want to have the reports refresh automatically and I understand this is done via the DirectQuery connection. Problem is that I have seen that ODBC is not supported for DirectQuery and so I would have to use PostgreSQL connector again. So I'm back trying to get this to work.

We ARE using SSL and I think I just need to add the certificate I already have (which works in other tools i.e. DBeaver) to the connection. How do I do this?

Thanks,

Nathan

[Dzsokkoci]

Can  you tell me, from where did you get the certificate for the PostGreSQL database?

[NathanGTyrenet · Accepted solution]

UPDATE: I managed to resolve the issue I was having by following these steps which add your SSL certificate to a trusted Windows store:

1. Windows Key + R
2. Search 'mmc' and hit enter
3. In the Microsoft Management Console go to File > Add/Remove Snap-in... 
4. In the left panel, click Certificates and Add button between left and right panels then OK
5. Click the newly added Certificates - Current User in the right panel 
6. Double-click Trusted Root Certification Authorities
7. Select and right-click Certificates > All Tasks > Import
8. Follow the Certificate Import Wizard making sure you add your dedicated certificate to the Trusted Root Certification Authorities certificate store
9. Finish the wizard import
10. Try your Get Data connection again in Power BI
    1. This didn't work immediately because I had previously stored credentials from an old attempt so make sure in Power BI you go to File > Options and settings > Data source settings and clear permissions for your other connection attempts before trying again

I hope this helps someone, this was an extreme pain to get a Direct Query connection but hopefully now I can finally detect new changes to the database/tables and update my reports as often as I need.