Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

6 REPLIES 6
laciodrom_80
Helper IV
Helper IV

 

@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!

 

 

Luca
Anonymous
Not applicable

Hi.

I managed to do it with the community edition.

Are you sure your user is defined on the database "myDb", and not on the "admin" DB?

d1g1talw00d
New Member

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

 

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.

Anonymous
Not applicable

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 😉

Anonymous
Not applicable

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"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors