BI Connector to MongoSQL Migration

Parent: Atlas SQL Interface MongoSQL · Published reference · snapshot 2026-10-15

Depth-first rabbithole dossier for BI Connector to MongoSQL Migration; source-anchored research pack.

These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.

Definitions

[high] The MongoDB SQL Interface is the recommended replacement for the legacy BI Connector, providing a unified set of SQL drivers and connectors for both Atlas and self-managed deployments. (verified as of 2026-10-15) Sources: https://www.mongodb.com/docs/sql-interface/changelog.md, https://www.mongodb.com/docs/atlas/cluster-additional-settings.md, https://www.mongodb.com/community/forums/t/how-can-i-refresh-the-schema-thats-presented-to-sql-clients/264675 [source]

Structure and components

[low] There is no official EOL for SQL Interface components, including drivers, connectors, and the MongoDB SQL Schema Builder. Sources: https://www.mongodb.com/docs/sql-interface/index.md [source]
[low] Connection URIs for the SQL Interface must not contain usernames or passwords, as such URIs are rejected; for authentication mechanisms other than Username/Password, these fields can be left blank. Sources: https://learn.microsoft.com/en-us/power-query/connectors/mongodb-atlas-sql-interface [source]

Problems, failure modes and limitations

[low] The BI Connector is deprecated as of the 2025.3 release and will be retired 1-2 releases after deprecation, with the new MongoDB SQL Connector recommended as its replacement on the Tableau Exchange. (verified as of 2026-10-15) Sources: https://help.tableau.com/current/pro/desktop/en-us/examples_mongodb.htm [source]
[low] Both `LIMIT` and `OFFSET` in MongoSQL must be positive integers, and using them without `ORDER BY` does not guarantee consistent results. Sources: https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[low] If the BI Connector for Atlas was not previously enabled, it cannot be enabled; if it was already enabled, it can only be turned off and cannot be re-enabled after disabling. Sources: https://www.mongodb.com/docs/atlas/cluster-additional-settings.md [source]
[low] A generic ODBC error `The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 03.80` is displayed when the connection cannot be established successfully. Sources: https://learn.microsoft.com/en-us/power-query/connectors/mongodb-atlas-sql-interface [source]

Comparisons and alternatives

[low] The underlying architecture of the SQL Interface differs from the BI Connector, which may require adapting schemas or queries to avoid breaking changes during migration. Sources: https://www.mongodb.com/docs/sql-interface/transition-bic-to-atlas-sql.md [source]
[low] Changes in MongoSQL translation of `WHERE` clauses comparing fields to literal values now use MongoDB match language instead of `$expr` expressions, applying type bracketing to compare only values of the same type. (verified as of 2026-10-15) Sources: https://www.mongodb.com/docs/sql-interface/changelog.md [source]

Facts and statements

[medium] The MongoSQL Transition Readiness Tool generates a report based on past BI Connector usage, providing real-time schema analysis and highlighting queries that need syntax changes for MongoSQL compatibility. (verified as of 2026-10-15) Sources: https://www.mongodb.com/docs/sql-interface/transition-bic-to-atlas-sql.md, https://www.mongodb.com/docs/sql-interface/changelog.md [source]
[low] For on-premises BI Connector, logs are located at the configured `mongosqld` log path, and the tool requires downloading `mongosql.gz`. Sources: https://www.mongodb.com/docs/sql-interface/transition-bic-to-atlas-sql.md [source]
[medium] The MongoDB Connector for Business Intelligence (BI Connector) for Atlas and on-premises reached end of life (EOL) in September 2026 and is no longer supported. (verified as of 2026-10-15) Sources: https://www.mongodb.com/docs/sql-interface/index.md, https://www.mongodb.com/docs/sql-interface/transition-bic-to-atlas-sql.md, https://www.mongodb.com/docs/atlas/cluster-additional-settings.md [source]
[high] The SQL Interface provides live, read-only access to MongoDB data using the MongoSQL dialect, which is based on the SQL-92 standard but has specific limitations. Sources: https://www.mongodb.com/docs/sql-interface/index.md, https://www.mongodb.com/docs/sql-interface/transition-bic-to-atlas-sql.md, https://www.mongodb.com/docs/sql-interface/language-reference.md, https://mongodb.com/company/blog/product-release-announcements/real-time-insights-through-atlas-sql-interface [source]
[low] MongoSQL does not support the `date` data type, interval arithmetic, `INTERSECT` or `EXCEPT` set operations, or MongoDB Vector Search and Search features. Sources: https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[low] MongoSQL data types correspond to BSON types, and explicit type conversions are performed using the `CAST` function or the `::` operator. Sources: https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[low] In MongoSQL, numeric types are mutually comparable without casting, and literal integers are typed as `INT` within the 32-bit signed range or `LONG` otherwise, while floating-point numbers are typed as `DOUBLE`. Sources: https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[low] MongoSQL distinguishes between `NULL` (field exists with null value) and `MISSING` (field is absent), sorting `MISSING` before `NULL`, and `NULL` before all other values. Sources: https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[high] For Atlas federated database instances, each underlying Atlas cluster must run MongoDB 5.0 or later, and the instance must be mapped to one or more data stores. (verified as of 2026-10-15) Sources: https://www.mongodb.com/docs/sql-interface/index.md, https://www.mongodb.com/docs/sql-interface/connect.md, https://learn.microsoft.com/en-us/power-query/connectors/mongodb-atlas-sql-interface [source]
[medium] MongoSQL supports complex transformations such as flattening nested objects and unwinding arrays directly within SQL queries, and starting in version 2.0.0, array functions allow native operation on array values. (verified as of 2026-10-15) Sources: https://www.mongodb.com/docs/sql-interface/index.md, https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[low] MongoSQL requires nested `SELECT` statements to have an alias, and `GROUP BY` keys can be top-level fields or subfields, with subfields requiring aliases in the SELECT or GROUP BY clause. Sources: https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[low] The `WHERE` clause in MongoSQL must statically have type `BOOL` or `NULL` and may evaluate to `MISSING`, acting as a filter on incoming data. Sources: https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[low] MongoSQL identifiers support all UTF-8 characters except the null character `\x00` and are case-sensitive whether delimited or not. Sources: https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[low] Duplicate checking in MongoSQL follows MongoDB equality semantics, where field order matters for document comparison and both element order and value matter for array comparison. Sources: https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[low] MongoSQL converts MongoDB's flexible document values into types using a schema, and if a static type constraint is not satisfied, the query fails to compile. Sources: https://www.mongodb.com/docs/sql-interface/language-reference.md [source]
[low] The Readiness Report Tool escapes query text and user names from BI Connector logs in HTML reports and neutralizes formula elements in CSV reports to prevent execution of malicious formulas in spreadsheet applications. Sources: https://www.mongodb.com/docs/sql-interface/changelog.md [source]
[low] The Power BI Connector for MongoDB ships with Power BI Desktop, and Atlas supports only the latest version of Power BI Desktop due to monthly updates. (verified as of 2026-10-15) Sources: https://www.mongodb.com/docs/sql-interface/connect.md [source]
[low] To configure the JDBC driver in tools, specify the Driver Name as `MongoSQL`, the Class Name as `com.mongodb.jdbc.MongoDriver`, and add the `all.jar` file to the Libraries tab. Sources: https://www.mongodb.com/docs/sql-interface/connect.md [source]
Fact 130 states that using the MongoDB Atlas SQL connector requires an Atlas federated database setup, while Facts 14, 29, and 72 state that the SQL Interface can be enabled directly on a dedicated cluster without a federated database instance. Sources: https://www.mongodb.com/docs/sql-interface/transition-bic-to-atlas-sql.md, https://www.mongodb.com/docs/sql-interface/changelog.md, https://learn.microsoft.com/en-us/power-query/connectors/mongodb-atlas-sql-interface [source]

Related concepts

MongoSQL — is a part of BI Connector to MongoSQL Migration
Connector — is a part of BI Connector to MongoSQL Migration
Migration — is a part of BI Connector to MongoSQL Migration

Children

No children recorded.

← the whole tree · 3D view· how to read this page