Power Bi PostgreSQL data import error - The remote certificate is invalid according to the validation procedure.'"

Question

I am trying to connect PostgreSQL database to Power Bi. Database is hosted on Heroku. I am getting the following error -

An error happened while reading data from the provider: 
'The remote certificate is invalid according to the validation procedure.

I think i am getting this error because it needs "sslmode=require". but I am not sure how to proceed.

Can someone help me with this problem.

Thanks in advance.

Answer 1

These steps should help you connect to Power BI desktop via ODBC. Note, you should probably create a follower database in heroku so you're not using your production database credentials.

1. Download PostgreSQL ODBC driver and install. Note, be sure to check the GAC option during installation (https://www.postgresql.org/ftp/odbc/versions/msi/).
2. Restart computer.
3. Open Power BI and click "Get Data" button in the ribbon.
4. Click "Other" and then select "ODBC".
5. Choose "None" as the data source name.
6. Click the "Advanced options" and enter this connection string: Driver={PostgreSQL Unicode};Server=HEROKU_HOST, where HEROKU_HOST is the "Host" from your heroku database credentials (example: ec2-xx-xxx-xx-xx.xxx.amazon.com).
7. On the next screen, enter the user name (user from heroku db credentials), password (password from heroku db credentials), and for connection string enter the following (with HEROKU_XXXX replaced with your info: PORT=HEROKU_PORT;DATABASE=HEROKU_DATABASE;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;HOST=HEROKU_HOST;COMPATIBLE=2.2.3.0;USER ID=HEROKU_USER;PASSWORD=HEROKU_PASSWORD;SSLMODE=require

Answer 2

I don't use Heroku but had a similar problem with AWS and had to configure the db provider and certificates as described below. I would think that something similar is needed for Heroku.

I did some digging and found 2 possible solutions for Heroku:

• Supply the additional settings sslmode=Require;Trust Server Certificate=true in the database connection. This does not seem to be an option with PowerBI, because even under "Advanced Settings" there is nowhere to provide these.
• Download the certificate for you database as described here and then follow much the same process as I did below to register the certificate with Windows. If Heroku supplies a P7B (PKCS#7 or pfx (PKCS#12) format certificate you can register that with Windows without having to do the conversion I described.

---

My setup of the Npgsql provider and AWS Certificates

PowerBI and Excel support connections to Postgres using the Npgsql .NET Data Provider. This provider is not installed by default on Windows. Here's how to install it:

1. Download the appropriate .msi installer from the Npgsql releases page. The version you need depends on your version of Excel. I am running Office 365, which required version 4.0.x (I installed 4.0.10, download here).
2. Run the installer. During the install there is an option to install to the GAC which is switched off by default - you must select to have the files installed to the GAC.
3. Reboot your computer and you should be able to connect setup a Postgres connection in PowerBI or Excel.

AWS RDS Postgres - additional requirement

If you are connecting to a Postgres cluster running in AWS, when you attempt to connect to Postgres after installing the Npgsql provider you may get an error saying:

"The remote certificate is invalid according to the validation procedure"

Here's how to fix it:

1. Download the public keys for AWS RDS regions: https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
2. Convert the bundle to a Windows certificate in P7B/PKCS#7 format. There are many ways to do this, and easy one is to use https://www.sslshopper.com/ssl-converter.html.
3. In Windows, search for the "Manage User Certificates" utility and open it.
   1. Right-click on "Trusted Root Certificate Authorities", "All Tasks", "Import..."
   2. Select the .p7b file which was the output of converting the AWS keys to P7B format.
   3. Choose to place all certificates in the Trusted Root Certification Authorities store.
   4. You will probably have to confirm for each certificate - there is one per AWS region

You should now be able to connect to your AWS RDS Postgres database from PowerBI or Excel.