Hi,
I have used the BI Connector to connect to a MongoDB cluster successfully using Power BI, VS 2019 SSIS and MySQL Workbench.
Using MS SQL SSMS (SQL Express) I was able to create a linked server, I can see the cluster and the collections in Object Explorer. However when I try to query the data using OPENQUERY I get the following error:
Msg 7399, Level 16, State 1, Line 23
The OLE DB provider “MSDASQL” for linked server “M” reported an error. The provider did not give any information about the error.
Msg 7350, Level 16, State 2, Line 23
Cannot get the column information from OLE DB provider “MSDASQL” for linked server “M”.
I have been researching this for days now and I have tried many different approaches but nothing resolves the error.
- SQL Service is running as NT Service\MSSQL$SQLEXPRESS. I have tried changing this to Network Service, Local Service and Local System - and restarting the service, but none of these worked.
- Running SSMS as Administrator
- Logging in using Windows Auth and logging in using SQL Auth (sa) - both accounts are sysadmin
- Provider > MSDASQL > Allow inprocess is checked
- The ODBC DSN is 64 bit and is a System DSN
The fact that I can connect and access the data in the collections from other applications leads me to believe it is some setting within SSMS and not an issue with my access/credentials to MongoDB.
Any help would be greatly appreciated.
Thanks,
Nichola