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: (110) Error from MongoDB Client: Invalid reply to listCollections command. (Error Code: 14) Additional error <2>: ErrorMsg: (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 .., 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: (110) Error from MongoDB Client: No suitable servers found (serverSelectionTryOnce set): (Error Code: 13053) Additional error <2>: ErrorMsg: (110) Error from MongoDB Client: No suitable servers found (serverSelectionTryOnce set): (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, IDictionary2 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/

cd /etc/ssl/ openssl req -newkey rsa:2048 -new -x509 -days 365 -nodes -out mongodb-cert.crt -keyout mongodb-cert.key cat mongodb-cert.key mongodb-cert.crt > mongodb.pem

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. :)