Connect to Atlas SQL with ODBC Driver

Download the latest MongoDB ODBC Driver version from the MongoDB download center.

Verify the integrity of the downloaded package.

The MongoDB release team digitally signs all software packages to certify that a particular MongoDB package is a valid and unaltered MongoDB release. Complete the following steps to verify the ODBC driver binary against its SHA256 key:

1. Download the .sha256 file for Windows x64 from the MongoDB ODBC Drivers Downloads page.

2. Compare the signature file to the MongoDB installer hash using the following Powershell script:

   $sigHash = (Get-Content $Env:HomePath\Downloads\mongodbodbc.msi.sha256 | Out-String).    SubString(0,64).ToUpper(); `
   $fileHash = (Get-FileHash $Env:HomePath\Downloads\mongodbodbc.msi).Hash.Trim(); `
   echo $sigHash; echo $fileHash; `
   $sigHash -eq $fileHash

   The command outputs three lines:

   • A SHA256 hash that you downloaded directly from MongoDB.

   • A SHA256 hash computed from the MongoDB ODBC driver binary you downloaded from MongoDB.

   • A True or False result depending if the hashes match.

   If the hashes match, the MongoDB binary is verified.

Install the ODBC driver.

To install the ODBC driver, run the installation file that you downloaded to open the Atlas SQL ODBC Setup Wizard. Follow the steps in the Setup Wizard.

Configure a System DSN.

To configure your ODBC connection:

1. Open your ODBC Data Source Administrator.

   Note

   If you use a 64-bit processor, be sure to open the 64-bit ODBC Data Source Administrator.

2. Navigate to the System DSN tab.

3. Add a new System DSN.

4. When prompted to select a driver for your data source, select the MongoDB Atlas SQL ODBC Driver.

5. Enter your connection information. You must enter:

   Note

   You can get your connection information from the Connect using the Atlas SQL Interface modal.

   Field	Description
   DSN	A name for your new DSN.
   Username	A database username to use to connect to your database.
   Password	The database user's password.
   MongoDB URI	Your MongoDB deployment URI.
   Database	The name of the database to which to connect.
   Enable maximum	Checkbox to enforce maximum string length of 4000 characters. You must enable this option to work with BI tools like Microsoft SQL Server Management Studio that can't support variable length string data with unknown maximum length.

6. Once you enter the required connection information, you can test your connection with your ODBC Data Source Administrator.

Configure logging for the ODBC driver.

Enable SQL trace logging within the ODBC driver either by setting it in the connection string or by adding a key value pair to the Windows System registry. You can configure the ODBC driver log level by adding a key-value pair to the Windows System registry, either directly or by using a .ini file. To learn more, see the Windows documentation on configuring the system registry.

If you set in both the driver and the connection string, the connection string setting takes precedence. You can specify the desired log level. Logs are hierarchical, which means Atlas SQL logs all messages at or above the level you set. You can configure one of the following log levels:

• error - to log only error messages

• warn - to log all warnings

• info - to log messages of info, errors, and warnings

• debug - to log all messages of error and info

• trace - to log all trace messages

• off - to disable logging

info excludes debug and trace messages. debug messages don't appear in the logs.

The default log level is info. You can change the log level by appending the key loglevel and value to your ODBC connection string.

loglevel=debug

loglevel=error

By default, Atlas SQL writes logs to a file named mongo_odbc.log that it creates in the logs folder. Atlas SQL writes logs to the file in the following path:

C:\Users<user>\Documents\MongoDB\Atlas SQL ODBC<version>\logs\mongo_odbc.log

Atlas SQL automatically rotates the logs when the file size reaches .5MB. The log files are named mongo_odbc.log.x where the x denotes the number of rotation. Atlas SQL only keeps up to 10 log files. When there are 10 log files, Atlas SQL automatically rotates and shuffles such that mongo_odbc.log.1 becomes mongo_odbc.log.2 and so on.

Download the MongoDB ODBC Driver.

curl https://translators-connectors-releases.s3.amazonaws.com/mongosql-odbc-driver/ubuntu2204/1.4.3/release/mongoodbc.tar.gz --output mongoodbc.tar.gz

Verify the integrity of the downloaded package:

The MongoDB release team digitally signs all software packages to certify that a particular MongoDB package is a valid and unaltered MongoDB release. The atlas-sql-odbc.asc key is available on pgp.mongodb.com

1. Run the following command to download the .sig file.

   curl -LO https://translators-connectors-releases.s3.amazonaws.com/mongosql-odbc-driver/ubuntu2204/1.4.3/release/mongoodbc.tar.gz.sig

2. Run the following command to download then import the key file.

   curl -LO https://pgp.mongodb.com/atlas-sql-odbc.asc
   gpg --import atlas-sql-odbc.asc

   HIDE OUTPUT

   gpg: key 1CCF1A1263CDD699: public key "Atlas SQL ODBC Release Signing Key <packaging@mongodb.com>" imported
   gpg: Total number processed: 1
   gpg:               imported: 1

3. Run the following command to verify the MongoDB installation file.

   gpg --verify mongoodbc.tar.gz.sig mongoodbc.tar.gz

   GPG should return this response:

   gpg: Signature made Wed May 22 13:24:36 2024 MDT
   gpg:                using RSA key 0C5F007ABC491E4A
   gpg: Good signature from "Atlas SQL ODBC Release Signing Key <packaging@mongodb.com>" [unknown]

   If the package is properly signed, but you do not currently trust the signing key in your local trustdb, gpg will also return the following message:

   gpg: WARNING: This key is not certified with a trusted signature!
   gpg:          There is no indication that the signature belongs to the owner.
   Primary key fingerprint: 1CF5 B0D7 B2F8 9E16 52D8 BA79 0C5F 007A BC49 1E4A

   If you receive the following error message, confirm that you imported the correct public key:

   gpg: Can't check signature: public key not found

Extract the ODBC driver:

The following example extracts the archive contents to /usr/local/lib/mongoodbc. The mongoodbc directory contains a LICENSE file, a README.MD file, and a bin directory. The bin directory contains the libatsql.so ODBC driver library.

sudo tar -zxf mongoodbc.tar.gz --directory /usr/local/lib

Locate the ODBC driver configuration files.

1. Run the following command:

   odbcinst -j

2. Note the locations of the configuration files for the DRIVERS, SYSTEM DATA SOURCES, and USER DATA SOURCES.

   Example:

   unixODBC 2.3.9
   DRIVERS............: /etc/odbcinst.ini
   SYSTEM DATA SOURCES: /etc/odbc.ini
   FILE DATA SOURCES..: /etc/ODBCDataSources
   USER DATA SOURCES..: /home/ubuntu/.odbc.ini
   SQLULEN Size.......: 8
   SQLLEN Size........: 8
   SQLSETPOSIROW Size.: 8

Configure the ODBC driver.

1. Open the odbcinst.ini file in your preferred editor.

   Example:

   sudo vim /etc/odbcinst.ini

2. Add the following entries to the file and specify the path to the libatsql.so ODBC driver library.

   Example:

   [ODBC Drivers]
   MongoDB Atlas SQL ODBC Driver = Installed
   [MongoDB Atlas SQL ODBC Driver]
   Driver=/usr/local/lib/mongoodbc/bin/libatsql.so

Configure the System DSN or User DSN.

If multiple users share the DSN, configure the System DSN. If multiple users user shouldn't share the DSN, configure the User DSN. If a single user uses the DSN, you can configure the System DSN or User DSN.

1. Open the SYSTEM DATA SOURCES file or USER DATA SOURCES file in your preferred editor.

   System DSN Example:

   sudo vim /etc/odbc.ini

   User DSN Example:

   sudo vim /home/ubuntu/.odbc.ini

2. Add the following entries to the file and specify the appropriate values.

   Note

   You can get your connection information from the Connect using the Atlas SQL Interface modal.

   Field	Description
   Password	Database user's password.
   Driver	Path to the libatsql.so ODBC driver library.
   Database	Name of the database to which to connect.
   User	Database username to use to connect to your database.
   Uri	MongoDB deployment URI.
   UnicodeTranslationOption	Unicode encoding for Atlas SQL. Set to utf16.
   enable_max_string_length	Optional. Flag to enforce maximum string length of 4000 characters. To enable, set value to 1 and to disable, set value to 0. If omitted, defaults to 0. You must enable this option to work with BI tools like Microsoft SQL Server Management Studio that can't support variable length string data with unknown maximum length.

   Example:

   [ODBC Data Sources]
   MongoDB_Atlas_SQL = "MongoDB Atlas SQL ODBC Driver"
   [MongoDB_Atlas_SQL]
   Password = your_password
   Driver = /usr/local/lib/mongoodbc/bin/libatsql.so
   Database = sample_mflix
   User = your_username
   Uri = mongodb://datalake.region.a.query.mongodb.net/?ssl=true
   UnicodeTranslationOption = utf16

Test your connection.

Run the following command:

iusql -v MongoDB_Atlas_SQL

The following example shows a successful connection:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

If the connection fails, verify the details in your configuration files against your Atlas Data Federation instance.

[MongoDB][API] Buffer size "0" not large enough for data.