No way to disable SSL for Mongo BI auth enabled instance: support non cleartext auth plugins

[thanhthang20]

Describe the bug
I start MongoBI connector with --sslMode = disabled (mongosqld cannot accept connections secured using TLS/SSL)

But Cube is always request with SSL causing this error "handshake error: ERROR 1759 (HY000): ssl is required when using cleartext authentication"

To Reproduce
Steps to reproduce the behavior:

1. Start mongosqld with --sslMode = disabled
2. Start Cube with npm run dev
3. Hit browser with http://localhost:4000/#/schema
4. See error

Expected behavior
Can load the schema

Screenshots
If applicable, add screenshots to help explain your problem.

Version:
"@cubejs-backend/server@0.10.54"

Additional context
Add any other context about the problem here.

[paveltiunov]

Hey @thanhthang20 ! Thanks for posting this one! Seems like this is returned by mongosqld rather than driver. AFAIK there's currently no way to connect to mongosqld using clear text auth method without SSL.

There's an option to use another method for auth: https://docs.mongodb.com/bi-connector/v2.1/tutorial/connecting/#urioption.mechanism. It'll require implementation in cube.js mongobi-driver though.

[thanhthang20]

@paveltiunov yes, this is returned by mongosqld. The problem is mongosqld doesn't require SSL, while the driver is forcing ssl handshake

[paveltiunov]

@thanhthang20 Makes sense. Could you please debug and see what's in data object at this line: https://github.com/cube-js/cube.js/blob/master/packages/cubejs-mongobi-driver/driver/MongoBIDriver.js#L46? And do the same with different auth options: https://docs.mongodb.com/bi-connector/master/reference/mongosqld/#cmdoption-mongosqld-defaultauthmechanism?

I believe cleartext is proposed by mongosqld rather than driver itself.

[paveltiunov]

@thanhthang20 Is this still an issue?

[paveltiunov]

Reopening to track non cleartext auth plugins support for Mongo BI driver.

[paveltiunov]

Workaround solution: https://stackoverflow.com/questions/58868085/cube-js-error-ssl-is-required-when-using-cleartext-authentication-using-mongod/58869914#58869914

[sharkyza]

Hi Pavel - using the suggestion in your link I get this on cube.js side:
Error: Access denied for user ''
at Packet.asError (C:\Users\node_modules\mysql2\lib\packets\packet.js:708:17)
at ClientHandshake.execute (C:\Users\node_modules\mysql2\lib\commands\command.js:28:26)
at Connection.handlePacket (C:\Users\node_modules\mysql2\lib\connection.js:408:32)
at PacketParser.onPacket (C:\Users\node_modules\mysql2\lib\connection.js:70:12)
at PacketParser.executeStart (C:\Users\node_modules\mysql2\lib\packet_parser.js:75:16)
at TLSSocket. (C:\Users\node_modules\mysql2\lib\connection.js:328:25)
at TLSSocket.emit (events.js:210:5)
at addChunk (_stream_readable.js:308:12)
at readableAddChunk (_stream_readable.js:289:11)
at TLSSocket.Readable.push (_stream_readable.js:223:10)

*with these cube.js .env settings:
CUBEJS_DB_HOST=localhost
CUBEJS_DB_NAME=
CUBEJS_DB_USER=
CUBEJS_DB_PASS=
CUBEJS_WEB_SOCKETS=true
CUBEJS_DB_TYPE=mongobi
CUBEJS_API_SECRET=de8bec84f2f1466efb46e32a6c81fb866ab35eaafbda8b83d362037fa7ce635d848f4fce5023ec77248f12f1771272b234f79812a32d4585d049f3ecca3924a3
#CUBEJS_DB_SSL=<SSL_PROFILE>
#CUBEJS_DB_SSL_CA=<SSL_CA>
#CUBEJS_DB_SSL_CERT=<SSL_CERT>
#CUBEJS_DB_SSL_CIPHERS=<SSL_CIPHERS>
#CUBEJS_DB_SSL_PASSPHRASE=<SSL_PASSPHRASE>
#CUBEJS_DB_SSL_REJECT_UNAUTHORIZED=<SSL_REJECT_UNAUTHORIZED>
CUBEJS_DB_SSL=true
CUBEJS_DB_SSL_REJECT_UNAUTHORIZED=false

and this on Mongo BI Connector side:

2020-01-02T17:17:45.561+0200 I NETWORK [conn1] connection accepted from 127.0.0.1:64115 #1 (1 connection now open)
2020-01-02T17:17:45.580+0200 E NETWORK [conn1] handshake error: unable to authenticate conversation 0: sasl conversation error: unable to authenticate using mechanism "SCRAM-SHA-1": (AuthenticationFailed) Authentication failed.
2020-01-02T17:17:45.580+0200 I NETWORK [conn1] end connection 127.0.0.1:64115 (0 connections now open)

when starting by command line:
"C:\Program Files\MongoDB\Connector for BI\2.13\bin\mongosqld.exe" --auth --sslMode requireSSL --sslPEMKeyFile "C:\Program Files\MongoDB\Connector for BI\2.13\bin\mongo.pem" --mongo-username user --mongo-password user --addr 0.0.0.0:3306 --sampleNamespaces ABC.cde --sampleSize 0 --sampleRefreshIntervalSecs 600

Any advice would be appreciated - thanks!

[paveltiunov]

@sharkyza Hey Jaques! You should provide correct credentials in .env file in order to connect.

[sharkyza]

@pavel - should it be the MongoDB credentials that the "Connector for BI" uses to access the MongoDB? Or is it the authentication settings one sets for "Connector for BI" in the mongosqld-config.yml ?

On Fri, Jan 3, 2020 at 9:30 AM Pavel Tiunov ***@***.***> wrote: @sharkyza <https://github.com/sharkyza> Hey Jaques! You should provide correct credentials in .env file in order to connect. —

[mongosqld-config.zip](https://github.com/cube-js/cube.js/files/4018501/mongosqld-config.zip)

…

You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#222>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACK5WHM3A6B4XVKHMQOAABLQ33SP5ANCNFSM4I5BNVBQ> .

[sharkyza]

@pavel - should it be the MongoDB credentials that the "Connector for BI" uses to access the MongoDB? Or is it the authentication settings one sets for "Connector for BI" in the mongosqld-config.yml ?

Ok, :-) I got it going
I made the mongo pem file for SSL and where it asked for server address I put 'localhost' - not sure if it helped but is working now. (As mentioned in https://stackoverflow.com/questions/58868085/cube-js-error-ssl-is-required-when-using-cleartext-authentication-using-mongod/58869914#58869914 )

Then for CUBEJS_DB_NAME I used the Mongo dB name of the dB I was trying to access with the username and password set up for MongoDB - didn't know they would be passed through the Mongo Connector for BI...

So that was just a note for posterity - thanks Pavel
Cheers

[shinebayar-g]

I guess this should be documented here

[paveltiunov]

@shinebayar-g Yeah. It would be great to have it there. Would love if you can provide PR for that!

[shinebayar-g]

I haven't got it working yet. I created self signed certificate and started mongosqld with

openssl req -newkey rsa:2048 -nodes -keyout key.pem -x509 -days 365 -out cert.pem
cat cert.pem key.pem > bundle.pem

mongosqld --auth --mongo-username myusername --mongo-password mypassword --sslMode requireSSL --sslPEMKeyFile /mnt/my-cert/bundle.pem

bundle.pem contains cert.pem + key.pem content straight out of self signed certificate. Looks like mongosqld is now happy with it.

Then I started cubejs api server with CUBEJS_DB_SSL=true and CUBEJS_DB_SSL_CERT=/mnt/my-cert/cert.pem

Now it's giving

_tls_common.js:129
c.context.setCert(cert);
^
Error: error:0909006C:PEM routines:get_name:no start line
at Object.createSecureContext (_tls_common.js:129:17)
at Connection.startTLS (/cubejs-api/node_modules/mysql2/lib/connection.js:308:31)
at ClientHandshake.handshakeInit (/cubejs-api/node_modules/mysql2/lib/commands/client_handshake.js:124:18)
at ClientHandshake.execute (/cubejs-api/node_modules/mysql2/lib/commands/command.js:39:22)
at Connection.handlePacket (/cubejs-api/node_modules/mysql2/lib/connection.js:417:32)
at PacketParser.onPacket (/cubejs-api/node_modules/mysql2/lib/connection.js:75:12)
at PacketParser.executeStart (/cubejs-api/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket.<anonymous> (/cubejs-api/node_modules/mysql2/lib/connection.js:82:25)
at Socket.emit (events.js:310:20)
at addChunk (_stream_readable.js:286:12) {
library: 'PEM routines',
function: 'get_name',
reason: 'no start line',
code: 'ERR_OSSL_PEM_NO_START_LINE'
}

Tried to change order of key & cert in bundle.pem file. No difference.

Didn't try CUBEJS_DB_SSL_REJECT_UNAUTHORIZED=false env yet. Because I can't find any reference of it in docs.
Any idea?

Edit: just tried CUBEJS_DB_SSL_REJECT_UNAUTHORIZED=false env, no difference.
Edit2 : tried with CUBEJS_DB_SSL_CERT=/mnt/my-cert/bundle.pem option, no difference

[priyesh2609]

@shinebayar-g Did you get the solution for it? Because I seem to be running into the same issue.

[shinebayar-g]

@drdeath2609 no. Looks like mongodb isn't usable

[legion-zver]

@shinebayar-g, @priyesh2609 your need use not path in CUBEJS_DB_SSL_CERT

if (process.env.CUBEJS_DB_SSL_CERT) {
  process.env.CUBEJS_DB_SSL_CERT = fs.readFileSync(process.env.CUBEJS_DB_SSL_CERT, {encoding: "utf8"});
}

[joseabraham]

In my case I was trying to connect to Mongo Atlas , I fixed this one by adding these to my .env
CUBEJS_DB_USER = mongoAtlasUser
CUBEJS_DB_PASS = mongoAtlasPass
CUBEJS_DB_NAME= mongoAtlasDBName

My connection string and Mongosqld command looks like this
mongosqld --auth -u mongoAtlasUser -p mongoAtlasPass --sslMode allowSSL --sslPEMKeyFile test.pem --logPath mongosqld.log --mongo-uri "mongodb://cluster0-shard-XX-XX.ihjry.mongodb.net:27017,cluster0-shard-XX-X1.ihjry.mongodb.net:27017,cluster0-shard-XX-X3..ihjry.mongodb.net:27017/?ssl=true&replicaSet=Cluster0-shard-0&retryWrites=true&w=majority"

I used this to create the pem file
openssl req -nodes -newkey rsa:2048 -keyout test.key -out test.crt -x509 -days 365 -subj "/C=US/ST=test/L=test/O=test Security/OU=IT Department/CN=test.com"
cat test.crt test.key > test.pem

[dlariosuniandes]

I keep getting my credentials rejected from Mongo Atlas after generating a test certificate. Am I doing something wrong?

[dlariosuniandes]

In my case I was trying to connect to Mongo Atlas , I fixed this one by adding these to my .env
CUBEJS_DB_USER = mongoAtlasUser
CUBEJS_DB_PASS = mongoAtlasPass
CUBEJS_DB_NAME= mongoAtlasDBName

My connection string and Mongosqld command looks like this
mongosqld --auth -u mongoAtlasUser -p mongoAtlasPass --sslMode allowSSL --sslPEMKeyFile test.pem --logPath mongosqld.log --mongo-uri "mongodb://cluster0-shard-XX-XX.ihjry.mongodb.net:27017,cluster0-shard-XX-X1.ihjry.mongodb.net:27017,cluster0-shard-XX-X3..ihjry.mongodb.net:27017/?ssl=true&replicaSet=Cluster0-shard-0&retryWrites=true&w=majority"

I used this to create the pem file
openssl req -nodes -newkey rsa:2048 -keyout test.key -out test.crt -x509 -days 365 -subj "/C=US/ST=test/L=test/O=test Security/OU=IT Department/CN=test.com"
cat test.crt test.key > test.pem

Did you do something on CubeJS side my user credentials are being rejected after setting up the test certificate. What type of cluster do you have?

[kodeine]

@dlariosuniandes @joseabraham @shinebayar-g
Guys, were you able to make it work with mongo bi ?

[kodeine]

@hassankhan this is kinda old ticket and we're still facing issues connecting to mongo bi, can you please help. also the helm charts for SSL values are wrong too. I'll push a PR for that shortly.

[PawlikMateusz]

@kodeine Do you use a kind of sidecar in your environment for eg. a setup with Kubernetes + Linkerd2/Istio?
I had to add the certificate to MongoBI as mentioned in https://stackoverflow.com/questions/58868085/cube-js-error-ssl-is-required-when-using-cleartext-authentication-using-mongod/58869914#58869914
Additionally, I had to add pod annotations like this:
cubejs-api+cubejs-referesh-worker pods: config.linkerd.io/skip-outbound-ports: "3307"
MongoBI pods: config.linkerd.io/opaque-ports: "3307"

[kodeine]

I was able to fix this, did have to update helm chart. My issue was mongobi wasnt using ssl so once that was done and fixing of chart, everything worked

[PawlikMateusz]

Small update, in the case o service mesh and sidecar containers it's even better to switch MongoBi to port 3306, then istio/linkerd will automatically know how to handle this kind of traffic (server-speak-first protocol)

[khushalsrashtasoft]

@sharkyza hi. I just read the the whole conversation. and seems that you have solved correctly. But i have some errors to resolve on same issue. I am using mongoDB and converting it into query data using mongosqld. but whenever I try to fetch the data in cube server, mongoDB doesn't grant access to cube server. I don't know what is stopping it. please help.

Here is my query: #5689