Forum Discussion
PowerBI MongoDB BI connector - MySQL auth
Hi All!
So, as stated in the title: I want to connect PowerBI to my MongoDB using the BI Connector.
The BI Connector (installe don the server side) basically provides a way to connect to MongoDB as if it were a MySQL server (on port 3307) BUT to authenticate, one has to use a special auth plugin (mongosql_auth) that has to be installed on the client side.
The point is: it works perfectly on command line:
mysql 'user=root?source=admin' --default-auth=mongosql_auth -p -h localhost --port 3307
This connects without any problem, and then I can see my tables etc...
But, when I use the MySQL connector in PowerBI, there is no way of specifying that it has to use the mongosql_auth plugin. It returns:
Details: "MySQL: Authentication to host 'xxx.xxx.xxx.xxx' for user 'root' using method 'mysql_native_password' failed with message: Reading from the stream has failed."
I could not find either a way to do it using MySQL Workbench by the way ;)
Anyone has insight about this?
Thanks!!
I have found a solution: connecting via ODBC.
Installing the MySQL ODBC driver and creating an ODBC DSN using the ANSI connector fixes the problem, as the interface lets you specify the auth plugin to be used.
Tested succesfully: I can retrieve data from MongoDB to PowerBI using the BI connector and mongosql_auth plugin.
6 Replies
Hi gnumarco, I've been struggling with this for a while too.
One workaround is to use the MySQL ODBC driver and set up a DSN on the client, configuring SSL with client certificate, key and ca cert (obtained by using openssl the Mongodb host, https://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html). You can then use dsn=foo to set up an ODBC data source in PBI, instead of a MySQL one.
However, this is a) a pain to manage certs on each client desktop and worse, b) there seems to be a bug where after successfully listing and scanning the MongoDB collections in the PowerBI, the Get Data wizard often seems to hang. Checking the logs on the MongoDB side it appears to timeout during an SSL handshake. Incidentally, MySQL workbench can connect and query MongoDB without issues, like your command line scenario.
I'm raising a ticket with MongoDB Support today and will let you know if there is a fix on the BI Connector side. It may be possible the SSL problem would be by passed by using mongosql_auth with the MySQL ODBC driver, the release notes say it is compatible, that will be my next step.
Much better would be for Microsoft to add SSL support for the native MySQL connector in PowerBI. There is a feature request already, but it only has 27 votes currently. https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13440003-ssl-certificate-on-mysql-connection.
My current temporary solution is to use Magnitude's Simba ODBC driver for MongoDB (Progress have one too) but their prices have just ramped up and general user rollout of the driver isn't financially feasible.
Hope that helps, it's really frustrating to have MongoDB and PowerBI so close but not quite connected. I'll reply back if I get any resolution from MongoDB Support.
I have searched a bit more about this. It seems to be a wider problem: a lot of softwares that can connect to a MySQL database do not offer the possibility to choose which authentication plugin to use, even though the plugin is installed in addition to the MySQL client library.
This is a problem that PowerBI has, and, except if Microsoft fixes this, I don't really see a workaround, and I really NEED this to work in my company (Microsoft, if you are listening...).
Anyway, any help is welcome ;)
I have found a solution: connecting via ODBC.
Installing the MySQL ODBC driver and creating an ODBC DSN using the ANSI connector fixes the problem, as the interface lets you specify the auth plugin to be used.
Tested succesfully: I can retrieve data from MongoDB to PowerBI using the BI connector and mongosql_auth plugin.
Anonymous
Hi,
I'm getting some issue connecting by mysql: I've succesfully created schema.drdl and established mongosqld connection on 3307 port:
OpenSSL version: OpenSSL 1.0.1u 22 Sep 2016 waiting for connections at 127.0.0.1:3307
But when I try to connect by mysql client
mysql 'user=myUser?source=myDb' --default-auth=mongosql_auth -p -h localhost --port 3307
I've got this error: ERROR 1043 (08S01): error performing authentication: unable to saslStart conversation 0: (AuthenticationFailed) Authentication failed.
I'm using MySQL community edition, you? Should I use the enterprise one and performing a connection from mysql with authentication and TLS/SSL? (https://docs.mongodb.com/bi-connector/current/connect/mysql/)
Thanks in advance!
