PostgreSQL heroku database with PowerBI

[Insightzclub]

Below are the steps that are taken by us for connecting our production PostgreSQL with PowerBI

Steps to be followed according to the documentation:-
1. Install Npgsql which allows .NET access to PostgresSQL. I installed it using the Package Manager Console in Visual Studio; detailed instructions are here: https://www.nuget.org/packages/Npgsql/

You need to connect over SSL to AWS which isn't setup by default (on my Windows 10 machine anyway)

2. Download the AWS public key from here https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem

3. Convert that to a certificate using https://www.sslshopper.com/ssl-converter.html (convert to PKCS#7/P7B)

4. Import that certificate to the Trusted Root Certificate described here http://www.cs.virginia.edu/~gsw2c/GridToolsDir/Documentation/ImportTrustedCertificates.htm

5. Test access from Power BI desktop using the PostgreSQL connector and it should work

Problems encountered by us:

1) The latest version of Npgsql doesn't work as intended and nothing seemingly happens when you install it. After installing it, there is no change in machine config and powerBI remains in the same state. The only working version that worked for us is (3.1.8) version.

2) AWS Postgres URL is of format:  postgres://userID:password@serverName:Port/dbName

After installing npgsql(3.1.8) for the powerBI desktop we were prompted for server and dbname. But as soon we try to connect we get the error as attached in the mail.

3) This is apparently a certificate issue but after following all the instructions found in powerBI community we were unable to make it connect to Postgres.

URLs we refered:

1) https://community.powerbi.com/t5/Integrations-with-Files-and/Connecting-to-PostgresSQL-hosted-on-AWS...

2) http://community.powerbi.com/t5/Integrations-with-Files-and/Connect-PowerBI-Desktop-with-PostgreSQL/...

3) http://community.powerbi.com/t5/Integrations-with-Files-and/Connect-PowerBI-Desktop-with-PostgreSQL/...

[v-jiascu-msft]

Hi @Insightzclub,

According to my search, PostgreSQL Heroku is an independent service that is different from AWS. Please vote up this idea.  This site could be helpful.

Best Regards,

Dale

[Anonymous]

Hi @Insightzclub ,

This is possible for free for both Import and DirectQuery methods via an ODBC custom connector I've been developing. As far as I'm aware - that's the only way to get around Heroku's inability to add your own certificates.
Blog post here:
https://medium.com/just-readr-the-instructions/heroku-postgres-and-powerbi-ab6b0a18c40f

Project homepage here:
https://github.com/sgoley/DirectQuery-for-ODBC-in-PowerBI

Scott

[tomandersen]

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

This worked for me. I had tried 8 months ago and it seemed way more complicated:

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

[martin_Grafil]

Hi @tomandersen 

This worked for me. Is this a secure connection?

[mikkelboje]

My learnings:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Support-Heroku-Postgres-databases-with-the-Post...