MongoDB Connector for BI from C#

Question

I'm trying to use the MongoDB Connector for BI from a C# application. I'm using the MongoDB Connector for BI v2.2.

My MongoDB service is a replica set from MongoDB Atlas cloud service.

mongosqld

I started mongosqld.exe with the following command, and all started OK:

mongosqld.exe --mongo-uri "mongodb://bitsion-pro-mongodb01-shard-00-00-kbtkm.azure.mongodb.net:27017,bitsion-pro-mongodb01-shard-00-01-kbtkm.azure.mongodb.net:27017,bitsion-pro-mongodb01-shard-00-02-kbtkm.azure.mongodb.net:27017/?replicaSet=BITSION-PRO-MONGODB01-shard-0" --schema ".\drdl" --mongo-ssl --auth --sslPEMKeyFile mongoatlaspre.pem --addr 0.0.0.0:3307

mysql.exe

Then I connected my mysql.exe client and all went OK:

mysql.exe --host 127.0.0.1 --port 3307 -u usrDGago?source=admin -p DB_TuCarpeta_PRU --ssl-mode required --ssl-key mongoatlaspre.key --ssl-cert mongoatlaspre.crt --enable-cleartext-plugin

C# app

My C# app is a very simple console app that uses MySql.Data nuget package to connect to the mongosqld service.

Package:

<package id="MySql.Data" version="6.9.9" targetFramework="net461" />

This is my snippet:

string cnnString = "Server=localhost; Port=3307;Database=DB_TuCarpeta_PRU;Uid=usrDGago?source=admin;Pwd=XXXXXXXXXX;CertificateFile=E:\mongoatlaspre.pfx;CertificatePassword=!QAZxsw2;SSL Mode=Required;";
var connection = new MySqlConnection(cnnString);
connection.Open();

Error in C#:

The host localhost does not support SSL connections.

Note if I remove SSL Mode=Required or change for None, then the error is:

Authentication to host 'localhost' for user 'usrDGago?source=admin' using method 'mysql_native_password' failed with message: ssl is required when using cleartext authentication.

Also I removed ?source=admin at the end of the Uid parameter, but with the same results.

Error in mongodbsqld console:

2017-09-11T12:00:31.950-0300 I NETWORK    [conn5] connection accepted from [::1]:58321 #5 (1 connection now open)
2017-09-11T12:00:31.959-0300 F NETWORK    [conn5] handshake error: ERROR 1043 (08S01): recv handshake response error: ERROR 1105 (HY000): connection was bad
2017-09-11T12:00:31.960-0300 I NETWORK    [conn5] end connection [::1]:58321 (0 connections now open)

Can you give some insights about how to solve this problem?

Thanks in advance.

Answer 1

The .NET MySQL driver does not support clear text authentication. Therefore, you'll need to use an ODBC connection using MySQL's ODBC driver instead of the .NET driver.

Answer 2

I use ODBC connection using MySQL ODBC 5.3 Unicode Driver.

My connection string as:

string cnnString = $"DSN=BI Connector DSN;Database=Northwind;Uid=admin;Pwd=1234;"

This works, but queries very slowly executes!