Navigation

Create a System DSN

On this page

The following steps describe how to create a system Data Source Name (DSN) for the BI Connector’s mongosqld process. A DSN is a saved configuration which describes a database connection to be used by an ODBC driver. Once the DSN is created for the BI Connector, you can configure a wide range of SQL clients and BI tools to use the DSN and import data from MongoDB.

Prerequisites

Before creating a DSN, you should:

Procedure

1

Start the Microsoft ODBC Data Sources program.

Choose the program version (64-bit or 32-bit) which is appropriate for your system and ODBC driver version.

2

Select the System DSN tab.

3

Click the Add button.

Screenshot of the Windows ODBC Administrator application
4

Select a MongoDB ODBC driver from the list of available drivers.

Select either the MongoDB ODBC 1.1.0 ANSI Driver or the MongoDB ODBC 1.1.0 Unicode Driver, then click OK.

Note

ANSI ODBC/Connectors offer maximum performance but have a limited character set. Unicode ODBC/Connectors support a wider character set but may be slightly less performant.

5

Fill in the necessary form fields.

Click the Details button to expose the lower half of the form.

The following form fields are required:

Field Name Description
Data Source Name A name of your choice.
TCP/IP Server Address of the server where your mongosqld process is running. If you have enabled BI Connector on MongoDB Atlas, you can find the hostname of the server where mongosqld is running in the connection information for your cluster.
Port Port number of your mongosqld process.
Database The name of the database to connect to, e.g. test.
6

(Optional) If authentication is enabled, fill in the authentication form fields.

The following fields Required when running with --auth enabled or when connecting to an BI Connector for Atlas instance.

Field Name Description
User

Username of the MongoDB user who is authenticated to use your target database.

You can specify the following authentication options after your username as URI-style query parameters:

Connection Option Description
source

Specify the name of the database which stores the user’s credentials. If you do not specify this option, the MongoDB Connector for BI will default to the current database associated with the MySQL connection.

For authentication mechanisms such as PLAIN (LDAP) or GSSAPI (Kerberos) that delegate credential storage to other services, set the source value to $external.

Not required if defaultSource is set in your MongoDB Connector for BI configuration file.

mechanism

Specify the mechanism that the MongoDB Connector for BI should use to authenticate the connection. Accepted values include:

LDAP and Kerberos require MongoDB Enterprise. Set the source to $external when using LDAP or Kerberos.

Not required if defaultMechanism is set in your MongoDB Connector for BI configuration file.

Note

X.509 is not supported.

Example

To authenticate as user grace using the admin database and the Challenge and Response (SCRAM-SHA-1) authentication mechanism, write the username in this format:

grace?source=admin

Example

To authenticate as user grace with the LDAP (PLAIN) authentication mechanism, write the username in this format:

grace?mechanism=PLAIN&source=$external

Example

To authenticate as user grace on the EXAMPLE.COM Kerberos realm with the Kerberos authentication mechanism, write the username in this format:

grace@EXAMPLE.COM?mechanism=GSSAPI&source=$external

For more information about Kerberos configuration, see Configure Kerberos for BI Connector.

Password The authenticated user’s password.
Authentication

The default authentication method. When using the MongoDB ODBC Driver for BI Connector, this field is not required.

To use the authentication plugin in conjunction with another ODBC driver, fill in this field with the string mongosql_auth.

7

(Optional) If SSL is enabled, fill in the SSL form fields.

Access the SSL form fields by clicking Details >>, then the SSL tab. The following parameters are required when TLS/SSL is enabled:

Field Name Description
SSL Key The location of the client/server key file.
SSL Cert

The location of the client/server certificate file.

Required when running with --auth enabled and when connecting to an BI Connector for Atlas instance. Username of the MongoDB user who is authenticated to use your target database.

SSL Mode Set to REQUIRED to require an encrypted channel. If an encyrpted channel is not provided, the connection fails.
8

Click the Test button to test the ODBC connection.

If the connection is successful, click OK to add the DSN. If the connection fails, check to make sure your MongoDB user is correctly authenticated for the database named in the connection.

Example

The following example configuration assumes the following:

  • A mongosqld instance running on localhost port 3307 with authentication enabled.
  • A mongod instance running with authentication enabled.
  • A MongoDB user named dbUser who authenticates against the admin database and is authorized to use the test database.

The values you enter may be different.

Screenshot of the Windows ODBC Administrator application
1

Launch ODBC Manager.

Note

ODBC Manager is included with the MongoDB ODBC driver.

2

Click System DSN, then click Add.

ODBC Manager DSN configuration
3

Select a MongoDB ODBC driver from the list of available drivers.

Select either the MongoDB ANSI ODBC driver or the MongoDB Unicode ODBC driver, then click OK.

Note

ANSI ODBC/Connectors offer maximum performance but have a limited character set. Unicode ODBC/Connectors support a wider character set but may be slightly less performant.

4

Enter a Data Source Name (DSN).

Optionally enter a Description.

Note

Do not close the setup window. Proceed to the next step.

5

Add the necessary keywords.

  1. Add a keyword value pair by clicking the Add button.
  2. Modify the Keyword by double-clicking on it, entering the desired keyword, then pressing enter.
  3. Modify the Value by double-clicking on it, entering the desired keyword, then pressing enter.

Using the procedure above, add the following keywords:

Keyword Value
SERVER

The hostname or IP address of the MongoDB Connector for BI host.

Important

Use 127.0.0.1 to connect via TCP to localhost. Specifying a value other than an IP address, will attempt to connect via Unix socket.

PORT The IANA port number for the MongoDB Connector for BI. The default is 3307.
DATABASE

The database to use after connecting.

Note

Required when connecting with Microsoft Excel.

For the complete list of ODBC parameters, see Connector/ODBC Connection Parameters.

For example, your user DSN configuration should look similar to the following:

ODBC Manager DSN configuration

Note

Do not close the setup window. Proceed to the next step.

6

(Optional) If authentication is enabled, add the authentication keywords.

  1. Add a keyword value pair by clicking the Add button.
  2. Modify the Keyword by double-clicking on it, entering the desired keyword, then pressing enter.
  3. Modify the Value by double-clicking on it, entering the desired keyword, then pressing enter.

Using the procedure above, add the following keywords:

Keyword Value
UID

The username for the user that can access the active MongoDB Connector for BI database.

You can specify the following authentication options after your username as URI-style query parameters:

Connection Option Description
source

Specify the name of the database which stores the user’s credentials. If you do not specify this option, the MongoDB Connector for BI will default to the current database associated with the MySQL connection.

For authentication mechanisms such as PLAIN (LDAP) or GSSAPI (Kerberos) that delegate credential storage to other services, set the source value to $external.

Not required if defaultSource is set in your MongoDB Connector for BI configuration file.

mechanism

Specify the mechanism that the MongoDB Connector for BI should use to authenticate the connection. Accepted values include:

LDAP and Kerberos require MongoDB Enterprise. Set the source to $external when using LDAP or Kerberos.

Not required if defaultMechanism is set in your MongoDB Connector for BI configuration file.

Note

X.509 is not supported.

Example

To authenticate as user grace using the admin database and the Challenge and Response (SCRAM-SHA-1) authentication mechanism, write the username in this format:

grace?source=admin

Example

To authenticate as user grace with the LDAP (PLAIN) authentication mechanism, write the username in this format:

grace?mechanism=PLAIN&source=$external

Example

To authenticate as user grace on the EXAMPLE.COM Kerberos realm with the Kerberos authentication mechanism, write the username in this format:

grace@EXAMPLE.COM?mechanism=GSSAPI&source=$external

For more information about Kerberos configuration, see Configure Kerberos for BI Connector.

PWD The password associated with the UID.

For the complete list of ODBC parameters, see Connector/ODBC Connection Parameters.

For example, your user DSN configuration should look similar to the following:

ODBC Manager DSN configuration
7

(Optional) If SSL is enabled, add the SSL keywords.

  1. Add a keyword value pair by clicking the Add button.
  2. Modify the Keyword by double-clicking on it, entering the desired keyword, then pressing enter.
  3. Modify the Value by double-clicking on it, entering the desired keyword, then pressing enter.

Using the procedure above, add the required keywords for your SSL configuration:

Keyword Value
SSLKEY The path to the .pem key file.
SSLCERT The path to the SSL certificate.
SSLMODE Set to REQUIRED.

Note

Depending on your SSL implementation you may need to set some of the following ODBC keywords:

Keyword Value
SSLCA The path to the SSL certificate authority file.
ENABLE_CLEARTEXT_PLUGIN Set to 1 to enable cleartext authentication.

Note

If you are connecting with Microsoft Excel, certificates must be located in the /Library/ODBC/ directory.

For the complete list of ODBC parameters, see Connector/ODBC Connection Parameters.

For example, your user DSN configuration should look similar to the following:

ODBC Manager DSN configuration
8

Click OK to finish creating the DSN.

The following procedure has been tested with Ubuntu and RHEL, and should work in a similar fashion with other Linux distributions.

1

Check the location of your .so files

The MongoDB ODBC driver files are named libmdbodbca.so and libmdbodbcw.so. After downloading the MongoDB ODBC driver, move them to your /usr/local/lib directory.

2

Create an odbc.ini file

The file /etc/odbc.ini defines your Data Source Names. Below is an example odbc.ini file which is configured to use the MongoDB ODBC driver with a DSN called MongoDBODBC. Replace all necessary placeholder values with values for your system.

The file named libmdbodbca.so is the ANSI driver, and the file named libmdbodbcw.so is the Unicode driver. Specify the desired driver by setting the Driver parameter in your odbc.ini file.

Note

ANSI ODBC/Connectors offer maximum performance but have a limited character set. Unicode ODBC/Connectors support a wider character set but may be slightly less performant.

[MongoDBODBC]
Description = ODBC for MongoDB BI Connector
Driver      = /usr/local/lib/<libmdbodbca.so|libmdbodbcw.so>
Trace       = Off
TraceFile   = stderr
ReadOnly    = yes
SERVER      = <server address>
PORT        = 3307
USER        = <username>
PASSWORD    = <password>
DATABASE    = <dbname>
3

Install unixODBC

Install unixODBC with your preferred package manager. Alternatively, you can download binaries from the unixODBC website.

4

Test with iusql

The unixODBC package includes an SQL client called iusql. You can use it to test your DSN.

The following example uses iusql to connect to the MongoDBODBC DSN:

> iusql -v MongoDBODBC

SQL> use test;

SQL> show tables;