My team needed to connect to a Postgre SQL database using Power BI. However, we were getting an invalid certificate error and everything we tried did not work. "The remote certificate is invalid according to the validation procedure". We tried following the steps listed in this post, but still, no luck. https://community.powerbi.com/t5/Desktop/The-remote-certificate-is-invalid-according-to-the-validation/td-p/531475
However, there is hope! An alternative is to use the Postgre ODBC driver!
I was finally able to connect to a Postgre DB by using the Postgre ODBC driver instead of the native Power BI Postgre connector. This method ignores any need for certificates, etc. Further, I tested the refresh on PowerBI. com to confirm that a gateway is indeed compatible for setting up refresh schedules.
These are the steps I did:
1) Download the Postgre ODBC (zip file) at: https://www.postgresql.org/ftp/odbc/versions/msi/
(scroll down to the bottom to get the latest version). I chose x64 since I'm running on a 64 bit machine.
2) Extract zip file contents.
3) Run the msi file (this will install the driver). Accept all the defaults.
4) Open Control Panel -> System and Security -> Administrative Tools -> Double Click on "ODBC Data Sources (64bit)"
5) Click "Add"
6) Choose PostgreSQL Unicode (x64)
7) Enter DB Credentials here and click "Save". Take note of the Data Source name (DSN). Mine shows "PostgreSQL35W" and you will choose this in the next step with Power BI.
8) Now use Power BI to connect to an ODBC as a data source. Select the recently installed Postgre ODBC (mine is "PostgreSQL35W" as you can see in the above image in step 7). You'll need to enter your credentials one more time.
Hopefully this will save some people a headache or two! Cheers!
