SQL Server 2019 – Polybase MongoDB

Hi Guys,

Following the release of new SQL Server 2019 and it’s new feature, Polybase MongoDB connector, I decided to give it a try.

I managed to set up the External table, following Microsoft tutorial:

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-mongodb?view=sql-server-ver15

However, I had few other issues on the way.

Our MongoDB is in Atlas and I just managed to create the external table connect to the cluster that I had enabled the BI connector. For the clusters without the BI connector enabled, I get the following error:

Msg 105082, Level 16, State 1, Line 23

105082;Generic ODBC error: [Microsoft][MongoDBODBC] (110) Error from MongoDB Client: Invalid reply to listCollections command. (Error Code: 14) Additional error <2>: ErrorMsg: [Microsoft][MongoDBODBC] (110) Error from MongoDB Client: Invalid reply to listCollections command. (Error Code: 14), SqlState: HY000, NativeError: 110

Couldn’t find a workaround for that yet, so will move on with the cluster with BI Connector enabled.

So following Microsoft steps, after creating the External table, with no issues, I tried to Query that new table and then I get this error:

Msg 8680, Level 17, State 1, Line 2

Internal Query Processor Error: The query processor encountered an unexpected error during the processing of a remote query phase.

I found a post that suggests to query from sys.dm_exec_compute_node_errors. When I tried to do so, I got the same error message after a minute.

Checking the errorlog, I found this entry:

Failed stream scan. hr: -2113929215, resultcode : 1

Then I figured that dmv gets data from SELECT * FROM [DWDiagnostics].[dbo].[pdw_errors], which was accessible. I was then able to find the error message:

"Unable to find computer account in AD : NT AUTHORITY\SYSTEM"

I changed the user starting Polybase services to Network and then I managed to query MongoDB Atlas. However, still not able to connect to local MongoDB, getting on SQL:

Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException: 105082;Generic ODBC error: [Microsoft][MongoDBODBC] (110)

Error from MongoDB Client: No suitable servers found (`serverSelectionTryOnce` set): [connection closed calling ismaster on '192.168.54.78:27017']

(Error Code: 13053) Additional error <2>: ErrorMsg: [Microsoft][MongoDBODBC] (110) Error from MongoDB Client:

No suitable servers found (`serverSelectionTryOnce` set): [connection closed calling ismaster on '192.168.54.78:27017'] (Error Code: 13053), SqlState: HY000, NativeError: 110 .

at Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.Execute(IQueryContext queryCtx, Boolean isSubBatch, Boolean isBatchInProgress)

at Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.OnExecuteRequest(IClientInterface client, String query, Boolean isInBatch, StatementExecutionContext executionContext, IDictionary`2 replacementInfos, String database, Statement pregeneratedStatement, SqlFrontEndRequest request)

On Mongodb logs I was getting:

Error receiving request from client: SSLHandshakeFailed: SSLHandshakeFailed

So I had to generate a certificate on server hosting MongoDB and restart Mongo enabling SSL.

https://docs.mongodb.com/manual/tutorial/configure-ssl/

Restart MongoDB using SSL:

mongod –replSet rs0 –port 27017 –bind_ip localhost,192.168.54.79 –dbpath /srv/mongodb/rs0-1 –smallfiles –oplogSize 128 –ipv6 –sslMode requireSSL –sslPEMKeyFile /etc/ssl/mongodb.pem &

Finally was able to connect to MongoDB using SQL Server.

Any questions, I am happy to answer. 🙂