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?
I have given up for now :(. Now I can't even use the previously working odbc driver to connect.
Maybe we can all vote on this UserVoice idea:
Has anyone actually managed to get the Postgre connector to work? This is driving me crazy!
The tracer log from my Power Bi can be found here:
How can I get this thing to work?
I confirm that it is working for me.
During the installation of NGPSQL, did you tick the GAC installation ?
You need to tick the two checkboxes.
The issue is now fixed.
You must use Npgsql 3.1.8. Uninstall any newer version first. Install that (and make sure to install to GAC) and then restart your PC and Power Bi works fine. If you use a newer version of Npgsql it doesn't work.
Give it a try @kschaefers
I run into the problem of certificate when trying to access Managed Postgresql on Azure.
I've tried to install 3.1.8 (there are no option, but installing to GAC is mentioned) and restarted.
@ianwukdid you doing anything else? (machine.config? ...)
I still get this:
Details: "An error happened while reading data from the provider: 'TlsClientStream.ClientAlertException: CertificateUnknown: Server certificate was not accepted. The specified hostname was not present in the certificate. at TlsClientStream.TlsClientStream.SendAlertFatal(AlertDescription description, String message) 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)'"
@ianwukdid you do anything else?
I was finally able to connect with:
Sorry I didn't have any other idea.
I had the same message because when I installed this driver for the first time, I didn't tick the two components during the setup.
After uninstalling the driver and reinstalled-it and ticking the two components suggested (the second one - "NGGSQL GAC Installation" - is not ticked by default).
I rebooted my computer and it works.
I have no other idea.
Maybe you can try a third time 🙂
I have the same issue, but after install the two component(including NGGSQL GAC ), and restart computer,
but it still cannot work...
Why Power BI don't integrated this thing?crazy...
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 😉
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. ?
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.
Tableau might have an embedded driver in their product, I wouldn't know. About the certificate usage, it's on our radar and we're working on making the "allow exceptions" logic usable in PBI. But right now unfortunately it's not supported.
I want to give you a possible work around, is this an Amazon Redshift database, or just a normal PostgreSQL?
(If you can find a PostgreSQL ODBC driver, you can specify the flag in the connection string that would help allow the certificate exceptions.)
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).
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.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.
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.
Hi to everyone,
I've faced the same issue and I've found the solution thanks to @arify.
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#
Check out the News & Announcements to learn more.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
Mark your calendars and join us for our next Power BI Dev Camp!
Check out our new Discover Your Career Path blog post series and get all the details.