My organization has a M30 db cluster. I want to connect to it through my orgs powerBI account. The issue is my org uses ssh tunneling through a Linux machine to connect to the Mongo db cluster. How can I connect to this cluster from PowerBI using my local windows machine? I tried setting up the ODBC using db credentials, getting this error:
"We encounter an error while trying to connect.
Details: "Data source error occurred.
SQLSTATE: 01000
NativeError: 444
Error message: ODBC: ERROR [01000] The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 03.80
".
Any help would be appreciated, thank you!
Hello Again @Maitreya_Awad - this is a very common error within Power BI this is typically a result of something incorrect within the connection: connection string, db user credentials, network IP whitelisting). And unfortunately Power BI holds the cache pretty tightly, so once you get this error, even if you take corrective steps, the error will still remain until you clear the cache - so I typically will clear the cache with each new thing I try. Once you are successful with connection you do not need to do this.
Make sure your IP Address has been whitelisted from within Atlas. (Atlas Menu->Network Access)
Make sure your Database credentials are working (log into Compass or Mongosh to prove this is working) Atlas Menu->Database users
Here are the steps to get the connection string for either Compass or Mongosh:
- From the Atlas Menu, navigate to Data Federation
- Find your SQL Quick Start Federated Database (it will be at the bottom of the screen you may need to scroll)
3.Select the green connect button
4.Find the tile for Compass or Shell and use that information to connect.
This will help prove out an issue with user id, IP Address, or connection to the federated db.
@Alexi_Antonino Thanks for such an elaborate answer. So if I do this I won’t need to create a ssh tunnel or I would need to create it? Bcoz my org uses ssh tunnel to connect to the remote atlas cluster through a linux machine.
@Alexi_Antonino I’ve setup a ssh tunnel(local port forwading) on port 3307 of my local machine to connect to mongodb through a remote linux machine. But still I’m not able to connect PowerBi to the MongoDb. Can you please help me out or maybe we can have a call on Zoom to discuss about the same if you’re available?
Thank you.
@Alexi_Antonino Also as our cluster is M30, do I need to enable BI connector in Atlas to achieve the MongoDB and PowerBI connection unlike in M0 clusters where the BI connector isn’t applicable or needed?
Hi @Maitreya_Awad I am confused. Is your MongoDB data hosted in Atlas or on Premise? If you are on Atlas, you will want to use the Atlas SQL Interface and Custom Certified Power BI Connector with the Atlas SQL ODBC driver. If you are on-premise you will use the on-prem BI Connector for connection to Power BI.
With Atlas SQL, an M0 will work, but you are required to be on db version 5.0 or higher. Please feel free to email me to set up a zoom and I can help sort your setup/config. Alexi.Antonino@mongodb.com