Create a System DSN

On this page

Prerequisites

Procedure

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:

Install the BI Connector and configure it to connect to your replica set.
Note
This prerequisite doesn't apply if you are using BI Connector for Atlas.
Download and install Visual C++ Redistributable for Visual Studio 2015.
Download and install the MongoDB BI Connector ODBC Driver.

Install the BI Connector and configure it to connect to your replica set.
Note
This prerequisite doesn't apply if you are using BI Connector for Atlas.
Download and install the MongoDB BI Connector ODBC Driver.

Install the BI Connector on Red Hat Enterprise-based Linux or Debian-based Linux and configure it to connect to your replica set.
Note
This prerequisite doesn't apply if you are using BI Connector for Atlas.
Download and extract the MongoDB BI Connector ODBC Driver for your platform.

Procedure

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.
Select the System DSN tab.
Click the Add button.
click to enlarge
Select a MongoDB BI Connector ODBC Driver from the list of available drivers.
Select either the MongoDB ODBC ANSI Driver or the MongoDB ODBC Unicode Driver, then click OK.
Note
The ANSI ODBC driver offers maximum performance but has a limited character set. The Unicode ODBC driver supports a wider character set but may be slightly less performant as a result.

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`.

(Optional) If authentication is enabled, provide connection information.

The following fields are 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:

SCRAM-SHA-1 (default)
SCRAM-SHA-256
PLAIN (LDAP SASL)
GSSAPI (Kerberos)

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.

X.509 is not supported.

For 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

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

grace?mechanism=PLAIN&source=$external

For 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.

You can't use curly braces ({ }) in your database password because these are special characters in the ODBC driver.

Authentication

The default authentication method. When using the MongoDB BI Connector ODBC Driver, 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.

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

Access the TLS/SSL form fields by clicking Details >>, then the SSL tab. The following parameters are available and may be required, depending on your TLS/SSL configuration:

Field Name	Description
SSL Key	The location of the client/server key file.
SSL Cert	The location of the client/server certificate file.
SSL CA File	The location of the file containing a list of trusted certificate authorities.
SSL Cipher	The location of the file containing a list of permitted ciphers.
SSL Mode	Set to `REQUIRED` to require an encrypted channel. If an encyrpted channel is not provided, the connection fails.
RSA Public Key	The location of the PEM file that contains the RSA public key.

Note

The BI Connector for Atlas uses TLS/SSL but does not require any TLS/SSL settings to be configured in your system DSN.

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.

Launch ODBC Manager.
Note
ODBC Manager is included with the MongoDB BI Connector ODBC Driver.
Important
The 1.0.16 edition of ODBC Manager included with the MongoDB BI Connector ODBC Driver is not compatible with macOS Catalina or later versions of macOS. If you are on Catalina or a later version of macOS, download and install the latest version (1.0.19) of ODBC manager.
Click System DSN, then click Add.
Select a MongoDB BI Connector 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
The ANSI ODBC driver offers maximum performance but has a limited character set. The Unicode ODBC driver supports a wider character set but may be slightly less performant as a result.
Enter a Data Source Name (DSN).
Optionally enter a Description.
Note
Do not close the setup window. Proceed to the next step.

Add the necessary keywords.

Add a keyword value pair by clicking the Add button.
Modify the Keyword by double-clicking on it, entering the desired keyword, then pressing enter.
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 using TCP to localhost. If you specify a value other than an IP address, the BI Connector attempts to connect using Unix socket.
PORT	The IANA port number for the MongoDB Connector for BI. The default is `3307`.
DATABASE	The database to use after connecting. You must use this keyword 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:

Note

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

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

Add a keyword value pair by clicking the Add button.
Modify the Keyword by double-clicking on it, entering the desired keyword, then pressing enter.
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

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:

SCRAM-SHA-1 (default)
SCRAM-SHA-256
PLAIN (LDAP SASL)
GSSAPI (Kerberos)

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.

X.509 is not supported.

For 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

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

grace?mechanism=PLAIN&source=$external

For 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.

You can't use curly braces ({ }) in your database password because these are special characters in the ODBC driver.

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

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

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

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

Note

The BI Connector for Atlas uses TLS/SSL but does not require any TLS/SSL settings to be configured in your system DSN.

Using the procedure above, add the following keywords depending on your TLS/SSL configuration:

Keyword	Value
`SSLKEY`	The path to the .pem key file.
`SSLCERT`	The path to the SSL certificate.
`SSLMODE`	Set to `REQUIRED`.
`SSLCA`	The path to the SSL certificate authority file.
`SSLCIPHER`	The path to the file containing a list of permitted ciphers.
`RSAKEY`	The path to the PEM file containing the RSA public key.
`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:

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.

Check the location of your `.so` files

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

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 BI Connector 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

The ANSI ODBC driver offers maximum performance but has a limited character set. The Unicode ODBC driver supports a wider character set but may be slightly less performant as a result.

[MongoDBODBC]
DESCRIPTION = ODBC for MongoDB BI Connector
DRIVER      = /usr/local/lib/libmdbodbcw.so
TRACE       = Off
TRACEFILE   = stderr
READONLY    = yes
SERVER      = <server address>
PORT        = <port number>
USER        = <username>
PASSWORD    = <password>
DATABASE    = <dbname>
; optional SSL configuration
SSLKEY      = </path/to/.pem-key-file>
SSLCERT     = </path/to/ssl-certificate>
SSLMODE     = DISABLED|PREFERRED|REQUIRED
SSLCA       = </path/to/ssl-ca-list-file>
SSLCAPATH   = </path/to/ssl-ca-file-directory>
SSLCIPHER   = </path/to/ssl-cipher-file>
RSAKEY      = </path/to/rsa-public-key-file>

Note

All odbc.ini parameters are case-insensitive.

The previous example contains only one DSN, but it is possible to include multiple DSNs in an odbc.ini file.

The following table describes the parameters used in the previous example.

Parameter	Description
`DESCRIPTION`	Optional. Text description of the DSN.
`DRIVER`	Path to ODBC driver file.
`TRACE`	`On` or `Off`. Enable ODBC tracing.
`TRACEFILE`	If ODBC tracing is enabled, the path to the file in which to store the trace output.
`READONLY`	`yes` or `no`. Enforce a read-only ODBC session.
`SERVER`	Address of the server where a BI Connector `mongosqld` instance is running.
`PORT`	Port number of the `mongosqld` instance.
`USER`	Username of the MongoDB user with permission to connect to the `mongosqld` instance.
`PASSWORD`	Password of the MongoDB user with permission to connect to the `mongosqld` instance.
`DATABASE`	Name of the MongoDB database to use.
`SSLKEY`	Full path to an SSL key file to use for establishing a secure connection.
`SSLCERT`	Name of the SSL certificate file to use for establishing a secure connection.
`SSLMODE`	Accepted values are: `DISABLED` `PREFERRED` `REQUIRED`
`SSLCA`	Full path to a file with a list of trust SSL Certificate Authorities.
`SSLCAPATH`	Full path to a directory that contains trusted SSL CA certificates in PEM format.
`SSLCIPHER`	Full path to the file containing a list of permitted ciphers.
`RSAKEY`	Full path to the PEM file containing the RSA public key.
`ENABLE_CLEARTEXT_PLUGIN`	Set to `1` to enable cleartext authentication.

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

Note

The BI Connector for Atlas uses TLS/SSL but does not require any TLS/SSL settings to be configured in your system DSN.

Your DSN is now ready to use. If you wish to test your DSN, proceed to step 3.

Install unixODBC

Install unixODBC with your preferred package manager.

On Ubuntu:

sudo apt-get install unixodbc

On RHEL:

sudo yum install unixODBC

Alternatively, you can download binaries from the unixODBC website.

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, then looks for tables in a database named test:

iusql -v MongoDBODBC

SQL> use test;
SQL> show tables;

Create a System DSN.leafygreen-ui-m0pgrr{-webkit-align-self:center;-ms-flex-item-align:center;align-self:center;padding:0 10px;visibility:hidden;}.leafygreen-ui-a30zj9{color:#889397;vertical-align:middle;margin-top:-2px;}.css-fmznk8{margin-top:-85px;position:absolute;padding-bottom:2px;}

Prerequisites

Note

Note

Note

Procedure

Note

Note

Note

Important

Note

Note

Note

Note

Note

Check the location of your .so files

Create an odbc.ini file

Note

Note

Note

Install unixODBC

Test with iusql

Create a System DSN

Check the location of your `.so` files

Create an `odbc.ini` file