9

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.

CR7SMS
  • 2,520
  • 1
  • 5
  • 13
shubham.a
  • 91
  • 1
  • 1
  • 3

3 Answers3

13

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
tquill
  • 279
  • 2
  • 12
  • 1
    This worked for me. I had tried other methods - which were complicated. I made readonly Heroku credentials, used as above. Follower database is not needed if you have a small database, for larger databases a follower would be nice to make sure the powerbi queries dont slow down your production server. Or depending on the security you need, etc. – Tom Andersen Jul 28 '20 at 15:46
  • here we can find other options for connection strings, https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/ – A. Munir Dec 29 '20 at 16:39
  • ODBC Connection Type has limitations in regards to the connection type, it does not currently support Direct-query. This might not be the way to go if you want to use live connections – Glitch Aug 31 '21 at 06:26
  • As a Power BI User, I am very thankful for this answer. I had a problem with a PostgreSQL DB connection although this was on-premise and not in any Cloud. Either way, using the described steps, replacing the IP in Server=HEROKU_HOST and using only the "PORT=...; DATABASE=...;" I could finally connect. The integrated (non-ODBC) connector within Power BI Desktop didn't connect. – Kondado Mar 22 '23 at 16:09
11

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.

Peter
  • 5,455
  • 7
  • 46
  • 68
  • [this bit](https://devcenter.heroku.com/articles/heroku-postgres-via-mtls#setting-client-side-certificates) that you linked to only works for Private Spaces on Heroku. If you have a regular Heroku account, you don't get Private Spaces, and thus you can't download the certificate for your database by following those instructions. – J86 Mar 15 '21 at 20:39
  • @J86 - I can view that link and I don't have a Heroku account at all. – Peter May 03 '21 at 06:36
  • For future readers, following only the steps under "AWS RDS Postgres - additional requirement" (downloading and importing RDS certificates) worked well for me. My setup: PostgreSQL on AWS RDS, Power BI on a Windows EC2 (AWS), no Heroku account. – m01010011 Feb 14 '22 at 04:14
  • This worked for us, although its a bit of a dissapointment to use this in our infrastructure as the Npgsql version required is 3 whole versions behind at time of writing... – Jake May 12 '23 at 18:20
0

You need to check if the host based authentication in your postgres database is allowing this from connection from POWERBI. [https://www.postgresql.org/docs/current/auth-pg-hba-conf.html][1]

The file is usually available in the postgres data folder and the structure is like below. Make sure the client host, username have the right permissions to access the database.

local         database  user  auth-method [auth-options]

Note: If you are using a cluster manager like patroni, the pg_hba.conf file will be overwritten by Patroni every time you edit it. you will have to edit this configuration from the patronictl command.

Sumukh
  • 660
  • 7
  • 11