MongoDB Connector for BI

[Alper Myumyun]

Hi Everyone,

We are trying to connect BI tool (QlikView) and MongoDB via connector for BI which we have downloaded from www.mongodb.com.

So far:

1. We have installed the MongoDB connector for BI and created a new schema file:

mongodrdl /h <myhostname.com> /u <user name> /p <password> /d <mydatabasename> /o Schema.drdl

2. Then we started the daemon:

mongosqld.exe /schema Schema.drdl /mongo-uri <myhostname.com> /addr <IPaddress:3307>

2017-04-28T11:49:31.427+0300 I CONTROL    [initandlisten] mongosqld version: v2.1.0
2017-04-28T11:49:31.428+0300 I CONTROL    [initandlisten] git version: 518180ba2c547d2cc6071f955d98ec6de730c0c9
2017-04-28T11:49:31.428+0300 I CONTROL    [initandlisten] arguments: --addr 1IPaddress:3307 --mongo-uri <myhostname.com> --schema Schema.drdl
2017-04-28T11:49:31.429+0300 I CONTROL    [initandlisten] ** WARNING: Access control is not enabled for mongosqld.
2017-04-28T11:49:31.430+0300 I CONTROL    [initandlisten]
2017-04-28T11:49:31.830+0300 I NETWORK    [initandlisten] connecting to mongodb at <myhostname.com>
2017-04-28T11:49:31.832+0300 I NETWORK    [initandlisten] waiting for connections at IPaddress:3307

3. Then, our QlikView has been connected to the database and we tried to execute a query, but the following error message popped-up.

 

2017-04-28T11:50:21.091+0300 F NETWORK    [conn2] dispatch error: not authorized on <mydatabasename> to execute command { aggregate: "Quiz", pipeline: [ { $group: { count(*): { $sum: 1 }, _id: {} } }, { $project: { count(*): "$count(*)" }
 } ], cursor: {}, allowDiskUse: true }

Can you help us to identify, what we are doing wrong.

Thank you.

[Craig Wilson]

Hi Alper,

It appears that you haven't enabled auth on the mongosql daemon.  You can do that with the option --auth. You can see the documentation here: https://docs.mongodb.com/bi-connector/current/tutorial/connecting/.

Craig

[Asim Hafeez]

Hi Craig,

I went through the documentation of BI Connector with --auth option. There is not enough documentation which explains this scenario. The error I get is "must specify --sslPEMKeyFile when using --auth". Where and how do I get the .PEM file. I think TLS/SSL is not configured on MongoDB server.

Thanks,

Asim

[Craig Wilson]

To use auth in mongosqld requires ssl because we currently need the plain text passwords, hence using SSL to protect them. Traffic between mongosqld and the mongodb server does not require ssl, however.

So, you'll get the sslPEMKeyfile from your operations guys, or you can create your own using openssl. While these docs here aren't specifically for bi-connector, they will instruct you on creating pem key files: https://docs.mongodb.com/manual/tutorial/configure-ssl/

[Asim Hafeez]

Craig,

I followed the instructions you posted to create my own pem file. I was able to configure SSL too on my local DB. But when I am connecting mysql to mongosqld I am getting the below error. Can you please help with this.

Error in Mongosqld Console:

mongosqld.exe --schema c:/temp/schema_test.drdl --mongo-uri mongodb://127.0.0.1:27017 --auth --sslPEMKeyFile  c:/temp/test.pem --mongo-ssl --mongo-sslPEMKeyFile c:/temp/test.pem

2017-05-16T15:23:41.708-0400 I CONTROL    [initandlisten] mongosqld version: v2.1.0

2017-05-16T15:23:41.709-0400 I CONTROL    [initandlisten] git version: 518180ba2c547d2cc6071f955d98ec6de730c0c9

2017-05-16T15:23:41.709-0400 I CONTROL    [initandlisten] arguments: --auth true --sslPEMKeyFile c:/temp/test.pem --mongo-ssl true --mongo-uri mongodb://127.0.0.1:27017 --mongo-sslPEMKeyFile c:/temp/t

est.pem --schema c:/temp/schema_test.drdl

2017-05-16T15:23:41.716-0400 I NETWORK    [initandlisten] connecting to mongodb at mongodb://127.0.0.1:27017

2017-05-16T15:23:41.717-0400 I NETWORK    [initandlisten] waiting for connections at 127.0.0.1:3307

2017-05-16T15:24:17.608-0400 I NETWORK    [conn1] connection accepted from 127.0.0.1:65362 #1 (1 connection now open)

2017-05-16T15:24:17.610-0400 F NETWORK    [conn1] handshake error: ERROR 1043 (08S01): recv handshake response error: ERROR 1043 (08S01): ssl configuration error: unexpected EOF

2017-05-16T15:24:17.610-0400 I NETWORK    [conn1] end connection 127.0.0.1:65362 (0 connections now open)

Error in Mysql Console:

mysql --enable-cleartext-plugin --protocol tcp --port 3307 -u root --ssl-key=c:/temp/test-client.key --ssl-cert=c:/temp/test.crt -p

Enter password: ****

mysql: [ERROR] SSL error: Unable to get private key from 'c:\temp\test-client.key'

ERROR 2026 (HY000): SSL connection error: Unable to get private key

Thanks,

Asim

[Alper Myumyun]

Thank you Craig. As stated by Asim, --auth requires a sslPEMKey file. I will try to generate and use it, although Asim tried and faced a new problem.

[Craig Wilson]

So, after discussing with some colleagues, there appears to be some incompatibility between MySQL community edition and both MySQL Enterprise server as well as our bi-connector. This stems from MySQL community edition being compiled with YaSSL and MySQL Enterprise Server and our BI-Connector getting compiled with OpenSSL. There is an incompatibility between YaSSL and OpenSSL.

So, two things to try. Either (1) pull down the MySQL Enterprise edition and use the mysql shell built with that or (2) try to connect from linux/macOS.

Please let me know if either of those work.

[Asim Hafeez]

After doing some trial and errors I got it working with Non SSL configuration. But with SSL I need to still investigate further. Thanks for the support Craig. I will let you know once I try your 2 options.

Thanks,
Asim

[Alper Myumyun]

Hi Asim,

Please, let us know how you managed to make it working without SSL configuration.