Connect PowerBI Desktop with PostgreSQL

[ecate]

I'm tring to connect power BI to a Posgres 9.4 database installed into the same machine (my windows 10 laptop).

I've overcome the error about Npgsql intalling Npgsql.dll with GACutil and modifying the file machine.config.

However now I've the following error:

"Ssl connection requested. No Ssl enabled connection from this host is configured."

Why a ssl connection is required?

Can I force Power BI not to use ssl?

Or is that a problem with Postgres and I have to configure it differenly?

Does Anyone fixed it?

Thanks!

[Martin0342]

Hi,

I've read all posts in this thread and I think I need to post an update:

It's 2019 and we still can't connect Power BI to PostgreSQL 😉

I've been trying to do it most of the day today and I am stuck. I've read through all materials I could find on the internet and installed all certificates/addons/plugins/drivers/you name it. And it still doesn't work!

I get this message when I try to connect:

Details: "An error happened while reading data from the provider: 'Could not load file or assembly 'System.Memory, Version=4.0.1.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified.'"

Does anyone know what else I could do?

Thanks

[schwinnen]

I think someone else at our organization had a similar issue.  The resolution was to download an earlier version of NPGSQL.  He had downloaded the latest version.  Everyone who has connected successfully is using version 3.27.  I would try that.

[Martin0342]

VFXPro - it looks interesting, will look into it in the future, thanks

schwinnen - I was close to throwing my laptop through a closed window but your solution saved  the day! Thanks a lot!

[VFXPro]

Just a thought...

Azure Data Factory can connect to PostgreSQL natively.  You might want to use that and convert your data into Azure SQL for read only.

[Anonymous]

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.

[schwinnen]

Just to update you, it's the 2nd half of 2018 and it's still very difficult to connect to PostgreSQL.

[pulkitg123]

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!

[sweta]

My Postgres database is hosted on and EC2 instance on AWS. Can you provide public certificate for AWS EC2 instance. I followed these steps but din't work.

[nmsivasankar]

Hi

Please select the Npgsql GAS installation option while you installing your driver.  This will allow you to connect to the Posgres but you still need the certificate to be configured.

[michaeldurieux]

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]

@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]

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]

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

I am getting the following error message:

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]

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:

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]

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

Let us know.

Cheers.

Michaël

[kschaefers]

@michaeldurieux, I tried it twice. still get the same message.

I downloaded the driver from here: https://github.com/npgsql/Npgsql/releases

[VFXPro]

For each driver install, you have to reboot your computer each time.

[Anonymous]

I followed every step - set the path for openssl also and finally got the error in command prompt for the command

openssl x509 -outform der -in rds-combined-ca-bundle.pem -out rds-combined-ca-bundle.crt

Error:

Error opening Certificate rds-combined-ca-bundle.pem
14856:error:02001002:system library:fopen:No such file or directory:.\crypto\bio\bss_file.c:356:fopen('rds-combined-ca-bundle.pem','rb')
14856:error:20074002:BIO routines:FILE_CTRL:system lib:.\crypto\bio\bss_file.c:3
58:
unable to load certificate

[gum]

I doubt the reason a Microsoft product is not connecting to a popular competing database is technical and I don't have much hope of this being resolved ever.

[gum]

If anyone is going to attend https://azure.microsoft.com/en-us/blog/azure-database-for-postgresql-team-hosts-ask-me-anything-sess... perhaps they could ask about PowerBI desktop to PostgreSQL connectivity.