Getting error when adding DSN for MongoDB in MS Excel

[sagar-gopale]

I am using

• MacOs Ventura version 13.3.1(a).
• MS Excel version 16.78
• I have installed iODBC version 3.52.16 driver for MacOS Ventura from https://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads
• I have tried this with local mongo server as well as remote Mongo cluster from Atlas.
• I am able to connect to the Mongo cluster using MongoAtlas client(To be sure that there are no network restrictions)
• The mongosqld utility is also running and it is able to sample schemas from MongoDB(as seen in logs).

Below is what my mongosqld config looks like for local mongodb connection.

## This is a example configuration file for mongosqld.

## The full documentation is available at:
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#configuration-file

## Network options - configure how mongosqld should accept connections.
## https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#network-options
net:
  bindIp: "127.0.0.1"
  port: 3307
  ssl:
    mode: "disabled"
mongodb:
  net:
    uri: "mongodb://localhost:27017" # https://docs.mongodb.com/manual/reference/connection-string/#mongodb-uri
    ssl:
      enabled: false

## Logging options
systemLog:
  path: './mongosql.log'
  quiet: false
  verbosity: 1
  logAppend: true
  logRotate: "reopen" # "rename"|"reopen"

## Schema options
schema:
  refreshIntervalSecs: 0
  stored:
    mode: "auto" # "auto"|"custom"
    source: "<myDatabase>" # the database where schemas are stored in stored-schema modes
    # name: "" # the named schema to read/write to in stored-schema modes
  sample:
    size: 1000 # The amount of random documents we sample from each collection.
    namespaces: ["*.*"]
processManagement:
  service:
    name: "mongosql"
    displayName: "MongoSQL Service"
    description: "MongoSQL accesses MongoDB data with SQL"

I am using below values when adding System DSN in MS Excel.

- SERVER=127.0.0.1
- PORT=3307
- DATABASE=<myDatabase>
- UID=<myUsername>?source=admin # In case of remote MongoDB Atlas cluster
- PWD=<myPwd> # In case of remote MongoDB Atlas cluster

I can see below drivers in MS Excel when adding System DSN. I have tried with both.

I get below error dialog when I try to create DSN.

I am not sure how to debug this. I have tried the solution at #34. Changing the permission of odbcinst.ini that is present in /Library/ODBC.
NOTE: I am the administrator on this system.

[HughWilliams]

Are you running on a macOS Intel or M1 machine ?

Where did you obtain the indicated MongoDB 1.4.5 ODBC drivers from ?

If you create the ODBC DSN using the iODBC Administrator directly , rather than in MSExcel, does the same error occur creating the ODBC DSN in the iODBC Administrator ?

[sagar-gopale]

@HughWilliams

1. I am running a M1 machine.
2. I considered that the listing of ODBC 1.4.5 is coming because I installed iODBC version 3.52.16 driver for MacOS Ventura from I have installed iODBC version 3.52.16 driver for MacOS Ventura from https://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads. Is it not the case?
3. I have not tried using iODBC Administrator directly. I did not know about this and update.

[HughWilliams]

iODBC does not provide an ODBC driver just the ODBC Driver Manager components, so some thing or someone else must have installed the MongoDB ODBC 1.4.5 driver on your machine.

What is in the /Library/ODBC/odbc.ini and /Library/ODBC/odbcinst.ini file on your machine, which should provide the file name of the ODBC driver and location, and might be a hint as to its origin ?

[smalinin]

@sagar-gopale
Don't try add DSN under MS Excell. Run iODBC Administrator App directly and create new DSN.
MS Excell is sandboxed app, so if you run iODBC Administrator under MS Excell, you must have issues with files access.

[smalinin]

@sagar-gopale
Also look at => #29 (comment)
and #97
If your MongoDB ODBC driver uses some dynamic libs that is installed for example to /usr/local/libs or MongoDB ODBC driver installed to /usr/local/libs or etc, so MongoDB ODBC driver could NOT be loaded by MSExcell , because sandboxed app doesn't have access to this locations.

[1R053]

with #109 implemented MongoDB works for me in x64 mode (e.g. in the Administrator64 app). It does not work in Arm mode (e.g. in the regular Administrator app), as the driver is not compiled for that yet apparently. Unfortunately, the default Excel installation seems to require Arm mode.

The sandboxing issues seem to be resolved with #109 for the Administrator app - at least in local builds. Unfortunately, Excel seems to have different sandboxing. E.g. Sqlite will work in administrator currently, but not in Excel due to sandboxing.