How to Connect Power BI to Heroku PostgresSQL?

[Anonymous]

Hi, 

I am struggling to follow the instructions here:

https://stackoverflow.com/questions/60102636/power-bi-postgresql-data-import-error-the-remote-certif... 

What I am doing:

First here are my credentials

Host: ec2x
Database: d4x
User: box
Port: 5432
Password: 08x
Heroku username: user
Heroku password: password​

1. Download PostgreSQL ODBC driver and install => I have succesfully installed the driver.

2. I follow step 2 succesfully here: 

 3. For the next step, I put the user name as my data base user, and the password as my database password. Using the credentials listed above.

For the connection string: 

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

I replace the following: HEROKU_PORT, HEROKU_DATABASE, HEROKU_HOST, USER ID, PASSWORD.

My string therefore becomes (using my example credentials:

PORT=5432;DATABASE=d4x;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;HOST=ec2x;COMPATIBLE=2.2.3.0;USER ID=user;PASSWORD=password;SSLMODE=require

So the box filled in is:

But I keep getting this issue:

Can someone please help me out here? I seem to following the instructions step by step and keep hitting this issue.

Thanks!

[darling_midas]

<SOLVED> And It's super easy! Check out my stackoverflow answer (https://stackoverflow.com/a/77574948/14340684) for an easy-to-follow and detailed guide.

Please consider liking this answer and mark it as the solution if the fruits of my labor helped you!

[amitchandak]

@Anonymous , Try the posgresql type connection can work. Do not miss any step in the first solution

https://community.powerbi.com/t5/Power-Query/Connecting-to-PostgresSQL-hosted-on-AWS-RDS/td-p/135666
https://dataintoresults.com/post/powerbi-and-postgresql-ssl-lets-encrypt-and-gateway/

[Anonymous]

Thanks, the link is for AWS. Would it work for Heroku? I am not sure how to get the SSL certificates and so forth. Have you managed to get it working? (Apologies I am a total noob when it comes to Power BI)

I am also not sure why my steps didn't work. - been super confused with how to make it work.

[v-yingjl]

Hi @Anonymous ,

For SSL certifications, you can refer from the official PostgresSQL document:

https://www.postgresql.org/docs/current/libpq-ssl.html 

In addition, besides using ODBC to connect, power bi has PostgresSQL DB connector in-build that you can use it to connect:

Here is an article which introduces it in details not only ODBC but also the database connector: PostgreSQL To Power BI: 3 Best Ways To Connect 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Yinjie

Thanks, but I am unable to connect via ODBC. Can you point out where I am going wrong? I googled and researched the issue but cannot find anything that helps. If you could help me specifically with the issue I am having that would be appreciated!