Mongosqld runs fine, but ODBC fails on test and PowerBI throws error 10060. Connector not working.

[Anonymous]

**The Goal**

I need to get data from a MongoDB updated every 15 minutes to use to build into a PowerBI report.

**The Gear**

I am connected from my windows machine via ssh to an RHEL server (server a). This server is running powerbi connector (SQLD) which is connected to my MongoDB that is running on a different server (server b). I'm also running MySQL on server b. My powerBI connector is installed on server b.

**Exactly where I'm at**

I am using the steps listed here (and all the associated pages) and have tried everything listed short of writing a config file, as the fact that things are working on mongosqld's end makes me think I don't need it... and if I can't get it working manually, having a config file won't exactly help.

https://docs.mongodb.com/bi-connector/current/connect/powerbi/

Using:
mongosqld --mongo-uri="mongodb://10.xxx.xxx.xx" --auth --mongo-username="ThisGuy" --mongo-password="test"

I successfully map the schema and show an active connection in the command window. I can also access my database from compass using an authorization enabled URL.

When I set up an ODBC connector I use the IP of server a, the user and password from my url, and port 3307. Nothing shows up in the dropdown, when I click 'test' I get the following message:

>Connection Failed
[MongoDB][ODBC 1.4(w) Driver]Can't connect to MySQL server4 on '10.xxx.xxx.xxx' (10060)

I have also tried 3306, 27017, and 27015. Just to be safe I also added firewall rules for all traffic on these ports. I've tried this many times, including (just for the hell of it, and I'm kind of new to this stuff) the ip of server b, the ip of my machine, the credentials for MySQL, basically any combination of these things that I can think of.

In powerBI, my odbc driver shows up, and when selected in the dropdown, it asks for a username and password. I have tried both mongo credentials and MySQL. Not sure which I should be using?

regardless, I get the following error inside PowerBI:

> Details: "ODBC: ERROR [HY000] [MySQL][ODBC 1.4(w) Driver]Can't connect to MySQL server on '10.xxx.xxx.xxx' (10061)
ERROR [HY000] [MySQL][ODBC 1.4(w) Driver]Can't connect to MySQL server on '10.xxx.xxx.xxx' (10061)"

**Thoughts**

I don't control either server, although I have root access, being new to this tech and company I am wary of screwing anything up that a co-worker will have to fix. I read in a different SO thread that maybe I need to downgrade the version of MySQL that is running on the server and that it could fix the problem, but I don't think that it will actually help and am afraid I might screw up something else on the server if I do this:

>The C Authentication plugin was developed against MySQL 5.7.18 Community Edition (64-bit), and tested with MySQL 5.7.18 Community Edition and the latest version of MongoDB Connector for BI. The plugin is not compatible with MySQL Server or Connector/ODBC driver version 8 and later.
https://dba.stackexchange.com/questions/219550/access-denied-when-connecting-to-mongosqld-with-mysql

Maybe the problem is that server B is listening to server a on port 3307, and that there is another unknown port (not mentioned above) that my ODBC driver must be listening to? I'm not sure how to test for this when you get a step away like this.

So that's it. I'm really stuck and would love some help, I am going to try the downgrade tomorrow if nothing else shakes loose and will keep this thread updated.

Thank you for reading

[v-kelly-msft]

Hi  @Anonymous ,

Check whether below threads help:

https://www.progress.com/blogs/mongodb-odbc-driver-for-data-integration-with-power-bi

https://hevodata.com/learn/mongodb-to-power-bi/

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Hi @v-kelly-msft ,

I have double checked my process looking at both of those posts, and had no luck. I decided to use blank queries to load and proccess the data which is working but tedious. I would still appreciate a solution/ I think it would help some more people because I cannot find a solution to this problem after 3+ days of searching online.

[v-kelly-msft]

Hi  @Anonymous ,

Is your issue solved now?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[v-kelly-msft]

Hi  @Anonymous ,

Sorry for the late reply.

I found a reply from another post which may help:

I assume you are referring to the MongoDB ODBC Driver for BI Connector 8 which also requires a compatible version of the MongoDB Connector for BI 8 to be installed and correctly configured.

The system error: 10060 message indicates the ODBC driver is unable to establish a connection to the Connector for BI. I suggest testing that the Connector for BI is working before moving on to the ODBC driver set up.

Are you using a local version of the Connector for BI or Atlas-hosted?

https://www.mongodb.com/community/forums/t/powerbi-odbc-connector-error/3611

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

I restarted mongosqld without authentication " mongosqld --mongo-uri="mongodb://10.xxx.xxx.xx"

Then was able to connect to mongosqld from mysql using " sudo mysql -u root -p --protocol tcp --port 3307"

Connection shows up as confirmed on mongosqld command, but still no connection or luck from the ODBC driver

Also have reinstalled the software on my machine, still stumped