Unable to establish ODBC connection via mongoDB BI connector for Tableau reporting

Working with Data Connectors & Integrations
, ,
1

Hi,

I have been trying to establish successful ODBC connection using mongoDB BI connector. But I am not successful. Here are the steps I have done:

  • Setup a Atlas shared mongodb cluster
  • Able to connect from my desktop using mongodb compass
  • Installed mongoDB BI connector
  • Generated DDRL (using shard host addresses) using mongodrdl
  • Created a config file to run mongosqld to start the service. Here is the config file

systemLog:
logAppend: false
path: ‘c:\logs\jg-mongosql.log’
verbosity: 2

security:
enabled: true

mongodb:
net:
uri: ‘cluster0.99ltrf9.mongodb.net:27017’
auth:
username: ‘username’
password: ‘password’
ssl:
enabled: true
PEMKeyFile: ‘C:\opt\certs\mdb.pem’
CAFile: ‘C:\opt\certs\mdbca.crt’

net:
bindIp: 127.0.0.1
port: 3307
ssl:
mode: ‘allowSSL’
PEMKeyFile: ‘C:\opt\certs\mdb.pem’
CAFile: ‘C:\opt\certs\mdbca.crt’

schema:

If you’ve generated a DRDL schema file using mongodrdl, you can supply the

path for mongosqld to use that schema DRDL file.

path: ‘C:\Program Files\MongoDB\Connector for BI\2.14\bin\jgschema.drdl’

processManagement:
service:
name: jg-mongosqld
displayName: jg-mongosqld
description: “BI Connector SQL proxy server”

But I keep getting following error in the log to establish the odbc connection:

2022-09-17T19:12:58.762+0530 I NETWORK [initandlisten] waiting for connections at 127.0.0.1:3307
2022-09-17T19:13:03.765+0530 E NETWORK [initandlisten] unable to load MongoDB information: failed to create admin session for loading server cluster information: unable to execute command: server selection error: context deadline exceeded, current topology: { Type: Unknown, Servers: [{ Addr: <protected - mongodb cluster address>, Type: Unknown, Average RTT: 0, Last error: connection() error occured during connection handshake: dial tcp: lookup cluster0.99ltrf9.mongodb.net: no such host }, ] }

Looking forward for the support to resolve the issue. Appreciate your support.

Best Rgards
JG

2

Hi @J_G and welcome to the MongoDB Community forums. :wave:

The URI you’re using (cluster0.99ltlrf9.mongodb.net) is an SRV record which eases connections to a replica set. There error is stating that this host does not exist, which would make sense as it’s a DSN record.

We can see this by using a tool such as dig:

$ dig  cluster0.99ltrf9.mongodb.net SRV

; <<>> DiG 9.10.6 <<>> cluster0.99ltrf9.mongodb.net SRV
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 56311
;; flags: qr rd ra; QUERY: 1, ANSWER: 3, AUTHORITY: 0, ADDITIONAL: 1

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 512
;; QUESTION SECTION:
;cluster0.99ltrf9.mongodb.net.  IN      SRV

;; ANSWER SECTION:
cluster0.99ltrf9.mongodb.net. 60 IN     SRV     0 0 27017 ac-ozsmqgm-shard-00-00.99ltrf9.mongodb.net.
cluster0.99ltrf9.mongodb.net. 60 IN     SRV     0 0 27017 ac-ozsmqgm-shard-00-01.99ltrf9.mongodb.net.
cluster0.99ltrf9.mongodb.net. 60 IN     SRV     0 0 27017 ac-ozsmqgm-shard-00-02.99ltrf9.mongodb.net.

;; Query time: 106 msec
;; SERVER: 192.168.1.1#53(192.168.1.1)
;; WHEN: Sat Sep 17 10:37:15 MDT 2022
;; MSG SIZE  rcvd: 243

Here we can see that SRV record mentions three hosts. I would try putting one of those hosts names into your ODBC connection to see if that works for you.