Forum Discussion
PowerBI MongoDB BI connector - MySQL auth
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.
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.
can u pls explain clearly...am struck with getting data from mongo db,,,i can get the data from simba driver but i cant able to get the data from power bi connector..it showing the error as "mongo driver[odbc]:cant connect to the sql server"