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!

[kschaefers]

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:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7170610-aws-rds-postgresql

[ianwuk]

Has anyone actually managed to get the Postgre connector to work?  This is driving me crazy!

• I have installed Microsoft Power Bi version: 2.47.4766.801 64-bit (June 2017).
• I have installed Npgsql 3.2.3.
• I have installed VS Studio 2017 Community and added the dll files to the assembly using the command prompt.
• I have edited my machine.config file so it looks like this:
  
  <system.data>
      <DbProviderFactories>
        <add name="Npgsql Data Provider" invariant="Npgsql" description=".Net Framework Data Provider for PostgreSQL Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=3.2.3.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"/>
       </DbProviderFactories>
        </system.data>
• I am running Windows 10 so I already have .NET framework 4.5 installed
• I restart my PC, open up Power Bi and try to connect to Postgre again and I get the same message that the connector requires one or more additional components to be installed before it can be used.

The tracer log from my Power Bi can be found here:

https://www.sendspace.com/file/vleze0
 
How can I get this thing to work?
 
Thanks.
Ian

[michaeldurieux]

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.

[ianwuk]

Hello.

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

[Anonymous]

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?

[Anonymous]

I was finally able to connect with:

• npgsql 3.1.8 (restart, confirmed they are in gacutils and machine.config)
• https://docs.microsoft.com/en-us/azure/postgresql/concepts-ssl-connection-security
  • changed host to <servername>.database.windows.net from <servername>.postgres.database.azure.com.
  • (not sure if needed): added the server certificate: https://docs.microsoft.com/en-us/azure/postgresql/concepts-ssl-connection-security

[michaeldurieux]

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 🙂

[jwbecalm]

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...

[kschaefers]

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 😉

[kschaefers]

BTW, connection with PostgreSQL works beautifully in Tableau... not additional setup. Just works out of the box!!

[arify]

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

[kschaefers]

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.

[arify]

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?

[kschaefers]

Hi @arify, it's just a normal PostgreSQL database. I will try the ODBC driver version.

[kschaefers]

How do I set up an ODBC driver?

[arify]

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/

[arify]

(If you can find a PostgreSQL ODBC driver, you can specify the flag in the connection string that would help allow the certificate exceptions.)

[jgrob3]

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.

Refer http://stackoverflow.com/questions/33177598/install-npgsql-for-powerbi/33190177?noredirect=1#comment...

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.

[erikskov]

Same issue.  ODBC DSN works, PostgreSQL 3.1.7 native does not (with or without Encryption selected)

[kschaefers]

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.

[Anonymous]

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#