SQL 2019 out of box driver 2.3.8 does not work with CosmosDB (MongoDB API)
I have a BDC SQL 2019 Kubernetes cluster deployed.
When connected via master node and following instructions to create an external table against CosmosDB (MongoDB API) it fails with error:
ODBC error: [Microsoft][MongoDBODBC] (110) Error from MongoDB Client: Server at xxxxx.documents.azure.com:10255 reports wire version 2, but this version of libmongoc requires at least 3 (MongoDB 3.0) (Error Code: 15) Additional error <2>: ErrorMsg: [Microsoft][MongoDBODBC] (110) Error from MongoDB Client: Server at darminm.documents.azure.com:10255 reports wire version 2, but this version of libmongoc requires at least 3 (MongoDB 3.0) (Error Code: 15), SqlState: HY000, NativeError: 110 .
Total execution time: 00:00:02.187
Same error on regular SQL 2019 install is solved by changing
We had to make a change to the “Polybase ODBC Driver for MongoDb.ini” file of the SQL Server files with below changes:
From (does not work):
Driver=PolyBase ODBC Driver for MongoDb\2.3.8\MongoDBODBCsb64.dll
Setup=PolyBase ODBC Driver for MongoDb\2.3.8\MongoDBODBCsb64.dll
Driver=PolyBase ODBC Driver for MongoDb\MongoDBODBCsb64.dll
Setup=PolyBase ODBC Driver for MongoDb\MongoDBODBCsb64.dll
To reproduce the problem create an Azure CosmosDB with MongoDB API 3.6 and execute following commands:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Summer2020!';
CREATE DATABASE SCOPED CREDENTIAL mongodb_creds
WITH IDENTITY = 'yourusername', SECRET = 'yourprimarypassword';
CREATE EXTERNAL DATA SOURCE MongoDB
WITH (LOCATION = 'mongodb://xxxxx.documents.azure.com:port',
-- PUSHDOWN = ON | OFF,
CREDENTIAL = mongodb_creds);
CREATE SCHEMA MongoDB
CREATE EXTERNAL TABLE MongoDB.Cities (
[id] NVARCHAR(24) COLLATE SQLLatin1GeneralCP1CIAS NOT NULL,
[id] NVARCHAR(MAX) COLLATE SQLLatin1GeneralCP1CIAS,
[city] NVARCHAR(MAX) COLLATE SQLLatin1GeneralCP1CIAS)
DATA_SOURCE = MongoDB )
This will throw error.
If .ini files is updated creation of external Azure CosmosDB is correct.