- Download the AWS RDS public certificate (https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem)
- 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-command)
- 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)
- Install the certificate from the newly created (.crt) file.(http://www.cs.virginia.edu/~gsw2c/GridToolsDir/Documentation/ImportTrustedCertificates.htm)
- Connect!
Forum Discussion
Connect PowerBI Desktop with PostgreSQL
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 t...
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
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 !
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
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.
- Download and install Npgsql (latest version from the official github repo : link here).
- Download the AWS RDS public certificate (certificate to download) on your desktop for instance.
- 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).
- Set OpenSSL to your PATH variable (See Youtube video here).
- 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)
- Install the certificate from the newly created (.crt) file. (Just follow these steps)
- Reboot your computer :-)
- Connect!
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 :(
