A great way to Link SQL Server and MongoDB with BI Connector

Step 1 - Ensure MongoDB BI Connector is installed and configured . This will support the access to the MongoDB server. How to unlock the secrets of MongoDB BI Connector

Step 2 - Install MongoDB ODBC Driver for BI Connector - provides connectivity between a SQL client and the MongoDB Connector for BI

More details on https://docs.mongodb.com/bi-connector/master/reference/odbc-driver/

Step 3 - Create the System DSN

You'll need the following base information to connect to the MongoDB BI Connector 

Server Name\Port  

User. 

Password

Read further for full  details of the Create System DSN options

Step 4 - Create the Linked Server

This is some sample code to create the Linked server , that will utilise the System DSN you created in the previous step . The provider to use is the Microsoft OLE DB Provider for ODBC Drivers. The Data source is the name of the Data Source you created. 

You will also need to set the user name and password for the passthrough from the SQL Server Linked Server to MongoDB.The username and password should be supplied by the MongoDB DBA

USE [master]
GO

/****** Object:  LinkedServer [M]    Script Date: 08/01/2020 07:42:49 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'M', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'MongoDB_DEV'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'M',@useself=N'False',@locallogin=NULL,@rmtuser=N'userView_read?source=mdb1',@rmtpassword='########'
GO

Once you've confirmed the connection works - via the Test Connection , you're ready to start viewing the MongoDB objects which the user has privileges to view. 

One of the problems I've encountered is when trying to return a MongoDB collection column which is a varchar and the following error is encountered.

OLE DB provider "MSDASQL" for linked server "M" returned message "Requested conversion is not supported.".

The query would be something like : select * from OPENQUERY(M,'select col1 from MyCollection')

For this issue a BI Connector switch was created. --MaxVarcharlength.   Add this switch to the start up of mongosqld and the varchar based column data is now available.

Use a value of 8000 . e.g MaxVarcharlength: 8000

From the MongoDB docs "MaxVarcharlenght Specifies the maximum length, in characters, for all varchar fields. If mongosqld encounters a string that is longer than the maximum length, mongosqld truncates it to the maximum length and logs a warning."

!