cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arify Established Member
Established Member

Re: Connect PowerBI Desktop with PostgreSQL

Looks like there are installers on the official website https://www.postgresql.org/ftp/odbc/versions/msi/

 

I recommend taking a look at the license here before installing: https://odbc.postgresql.org/

kschaefers Regular Visitor
Regular Visitor

Re: Connect PowerBI Desktop with PostgreSQL

Hi everyone, we still can't natively establish a connection with a PostgreSQL databyse. Any plans on including a fix in one of the next versions?

 

it works out of the box btw. in tableau 😉

pulkitg123 Visitor
Visitor

Re: Connect PowerBI Desktop with PostgreSQL

IF you are using AWS hosted Postgre then use the public key certificate provided by AWS.

 

Please follow the following instruction after installing Npgsql latest version

 

  1. Download the AWS RDS public certificate (https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem)
  2. use Google library to install openssl(https://code.google.com/archive/p/openssl-for-windows/downloads) and set it to path variable.(http://stackoverflow.com/questions/11896304/openssl-is-not-recognized-as-an-internal-or-external-com...)
  3. To Create a .crt file from the .pem file downloaded. use this in command line (openssl x509 -outform der -in rds-combined-ca-bundle.pem -out rds-combined-ca-bundle.crt)
  4. Install the certificate from the newly created (.crt) file.(http://www.cs.virginia.edu/~gsw2c/GridToolsDir/Documentation/ImportTrustedCertificates.htm)
  5. Connect!
Highlighted
chtshop Frequent Visitor
Frequent Visitor

Re: Connect PowerBI Desktop with PostgreSQL

Guys, it's 2017 and we still can't connect to Postgresql.  This is unbelievable!

 

I'm getting the same "Server certificate was not accepted" error.

michaeldurieux Frequent Visitor
Frequent Visitor

Re: Connect PowerBI Desktop with PostgreSQL

Hi @pulkitg123

Thank you for this post, it solved my point.

I am now able to use PowerBI PostGreSQL connector !

 

For information, my database is hosted on AWS (RDS).

The other solution mentionned before (use ODBC driver) works also but with this ODBC solution, I was not able to use the feature "scheduled refresh" because I had the message "You can't schedule refresh for this dataset because one or more sources currently don't support refresh." on Power BI Service.

 

So now, I use a PostGreSQL on AWS and the connector PostGreSQL on PowerBI. Now, that's sounds great !

 

It works like a charm !

kschaefers Regular Visitor
Regular Visitor

Re: Connect PowerBI Desktop with PostgreSQL

@pulkitg123, thanks for your advice, but tihs is quite a challenge for non-developers. Maybe can you further elaborate on the steps? how exactly do I install openssl from the Google Library?

 

Also Microsoft, why aren't you providing more native working connections? Tableau has all of these working out of the box.

Cheers,

Konrad

michaeldurieux Frequent Visitor
Frequent Visitor

Re: Connect PowerBI Desktop with PostgreSQL

Maybe a more detailed view even if the post of @pulkitg123 seems to be enough.

 

My OS : Windows 2012 Server R2

My Database is stored on AWS. This is a PostGreSQL database using RDS AWS service.

 

 

  1. Download and install Npgsql (latest version from the official github repo : link here).
  2. Download the AWS RDS public certificate (certificate to download) on your desktop for instance.
  3. Download the last version of openssl (link here). Unzip the file on you desktop for instance - just keep in mind the link of openssl.exe which is in "bin" folder extracted. You we'll need it in step 4).
  4. Set OpenSSL to your PATH variable (See Youtube video here).
  5. Create a .crt file from the .pem file downloaded in step 1 : open a console on Windows (Win + R, then enter "cmd" and OK) and past this following line in your console (openssl x509 -outform der -in rds-combined-ca-bundle.pem -out rds-combined-ca-bundle.crt). Do not forget to indicate the PATH of your AWS RDS public certificate downloaded on step 1 (rds-combined-ca-bundle.pem)
  6. Install the certificate from the newly created (.crt) file. (Just follow these steps)
  7. Reboot your computer 🙂
  8. Connect!
kschaefers Regular Visitor
Regular Visitor

Re: Connect PowerBI Desktop with PostgreSQL

@michaeldurieux, thanks a lot for this detailed description. However, it still does not work for me.

I am getting the following error message:

Capture.PNG

 

I am connecting to our AWS database through a tunnel which uses port 10001 on my machine. So in the connection settings I tried 127.0.0.1 (works e.g. in HeidiSQL) or localhost:10001. But neither of those work 😞

kschaefers Regular Visitor
Regular Visitor

Re: Connect PowerBI Desktop with PostgreSQL

Okay, this just got even worse :).

 

I reinstalled the latest npgsql version from here

https://github.com/npgsql/npgsql/releases

 

and now I am getting this message:

Capture.PNG

 

Why is this so difficult in Power BI?

 

I don't understand, why they even list PostgreSQL as an option, when you basically need an engineering degree to get it working :).

 

 

michaeldurieux Frequent Visitor
Frequent Visitor

Re: Connect PowerBI Desktop with PostgreSQL

Could you try to reinstall npgsql with ticking the two options during the setup (and then reboot your PC) ?

 

npgsql.png

Let us know.

Cheers.

Michaël

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 391 members 3,587 guests
Please welcome our newest community members: