- 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 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!
51 Replies
Hi to everyone,
I've faced the same issue and I've found the solution thanks to arify.
1) Install the latest release of Npgsql (by now the 3.0.5 version -> Npgsql-3.0.5.msi and Setup_NpgsqlDdexProvider.exe)
2) Restart the pc
If you try to access into the database it might give you this error:
But if you follow the indications provided by arify you can solve this problem.
3) You have to enter in the "Data source settings"
4) and then edit the "Encryption" option.
p.s. For Npgsql I provide the installers instead of the .zip because it's more easy to use.
#I'M Not An Expert#
Hi everyone,
Npgsql drivers have a different behavior in the last few months' versions, that's why this is happening. We're fixing this now, it'll be in PBI releases in the next few months.
For the workaround, you can change the encryption setting of your database credential:
Sorry about the inconvenience and thanks for reporting this to us!
Oguz
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 ;)
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
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.
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.
Hi,
I have faced exaclty the same problem and I tried to set up Postgres as the documentation explains, but I get still the same message, any hints?
Thanks!
Im also having the same problem. hopefull
mfairchild This blog in Power BI Community might help click here
I'm having similar issues connecting to my AWS-hosted PostgreSQL database.
At first, I was just getting "The target machine actively refused the connection". I was able to overcome this by putting the port number from Heroku's config info on the end of the server name (sorry if this is obvious to everyone else).
e.g. ec2-54-247-242-8.compute-1.amazonaws.com:5500
Once I resolved this, I was able to input my username & password & now get a different error:
Details: "TlsClientStream.ClientAlertException: CertificateUnknown: Server certificate was not accepted. Chain status: A certificate chain processed, but terminated in a root certificate which is not trusted by the trust provider.
. The specified hostname was not present in the certificate.
at TlsClientStream.TlsClientStream.ParseCertificateMessage(Byte[] buf, Int32& pos)
at TlsClientStream.TlsClientStream.TraverseHandshakeMessages()
at TlsClientStream.TlsClientStream.GetInitialHandshakeMessages(Boolean allowApplicationData)
at TlsClientStream.TlsClientStream.PerformInitialHandshake(String hostName, X509CertificateCollection clientCertificates, RemoteCertificateValidationCallback remoteCertificateValidationCallback, Boolean checkCertificateRevocation)"From what I can tell, this means there's an incompatibility between the version of NgpSQL I have (3.1.6) and Power BI since Power BI seems to want SSL connections & a certificate but even though I followed the steps outlined here of un-checking the Encryption setting in File > Options & settings > Data Source Settings > Global Settings, I couldn't resolve this error.
I eventually gave up & went with the ODBC connection method outlined here & connected to the database after installing the PostgreSQL database drivers & setting up a user DSN. After upgrading to the lates (June 2016) Power BI desktop, I was able to use this method to see all my tables in my database.
Download from https://www.postgresql.org/download/
If the certificate issues are resolved, I'd be happy to use this method as it seems like it's a better option than ODBC but at least it's working for now.
Same issue. ODBC DSN works, PostgreSQL 3.1.7 native does not (with or without Encryption selected)
I have the same issue with npgsql 3.18. Doesn't work even when selection the "no encryption" setting.
I've also tried installing the certificates from the server without any luck.
BTW, connection with PostgreSQL works beautifully in Tableau... not additional setup. Just works out of the box!!
Hi kschaefers, do you get the certificate error too? Does your server need a specific certificate, or do you just need PBI to allow trusting the untrusted Server Certificate or allow a name mismatch etc. ?
Thanks
Hi arify, Yes exactly the same. I'm accessing our PostgreSQL database on Amazon AWS through a secure tunnel (Company policy). Therefore the server address is a local IP address like 127.0.0.1 port 5432. I added the PostgreSQL certificates but that doesn't make a difference probably cause I can't use the same server name (I have to use the IP address as mentioned above).
I think allowing PBI to trust the untrusted Server Certificate would work.
I don't understand why it's so hard to set this up in Power BI compared to Tableau?! With Tableau I didn't even have to download any additional driver. For PowerBI I had to download some strange .msi file from github, need administrator rights to install it (requires a request at our IT department). I had similar problems connecting to our EXAsol database. Again, worked out of the box with Tablea and simply couldn't get it to work in PBI, though that is probably a topic for another thread.
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
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!
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.
