Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

PostgreSQL heroku database with PowerBI

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:
 
Status: New
Comments
v-jiascu-msft
Microsoft Employee

Hi @Insightzclub,

 

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

 

Best Regards,

Dale

Anonymous
Not applicable

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
New Member

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
New Member

Hi @tomandersen 

 

This worked for me. Is this a secure connection?