Forum Discussion
Add an SSL Certificate to PostgreSQL Database Connection
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
UPDATE: I managed to resolve the issue I was having by following these steps which add your SSL certificate to a trusted Windows store:
- Windows Key + R
- Search 'mmc' and hit enter
- In the Microsoft Management Console go to File > Add/Remove Snap-in...
- In the left panel, click Certificates and Add button between left and right panels then OK
- Click the newly added Certificates - Current User in the right panel
- Double-click Trusted Root Certification Authorities
- Select and right-click Certificates > All Tasks > Import
- Follow the Certificate Import Wizard making sure you add your dedicated certificate to the Trusted Root Certification Authorities certificate store
- Finish the wizard import
- Try your Get Data connection again in Power BI
- 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.
2 Replies
UPDATE: I managed to resolve the issue I was having by following these steps which add your SSL certificate to a trusted Windows store:
- Windows Key + R
- Search 'mmc' and hit enter
- In the Microsoft Management Console go to File > Add/Remove Snap-in...
- In the left panel, click Certificates and Add button between left and right panels then OK
- Click the newly added Certificates - Current User in the right panel
- Double-click Trusted Root Certification Authorities
- Select and right-click Certificates > All Tasks > Import
- Follow the Certificate Import Wizard making sure you add your dedicated certificate to the Trusted Root Certification Authorities certificate store
- Finish the wizard import
- Try your Get Data connection again in Power BI
- 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.
Can you tell me, from where did you get the certificate for the PostGreSQL database?
