Omnis Technical Note TNSQ0038August 2019
Updated June 2021

Using the Microsoft ODBC Driver for SQL Server on Linux and macOS

for Omnis Studio 10.0.1 or above
by Gary Ashford, Omnis Engineering

Introduction
This technote relates to the "Microsoft ODBC Driver 17 for SQL Server" available for Linux and macOS, released in 2018.
Use of this driver requires the Unix ODBC Driver Manager (libodbc.so / libodbc.dylib). While this library may already be present on Linux operating systems, for macOS it can be extracted and installed from the download package referred to below.

Show discussion for:

Notes for macOS Catalina and Big Sur
Please refer to the download and installation instructions on the Microsoft ODBC Driver Installation page. To install using Homebrew, enter the following commands from the terminal window:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql17 mssql-tools

The Homebrew script installs all files needed to make a connection using the ODBC driver, including:

/usr/local/lib/libodbc.dylib (libodbc.2.dylib)Unix ODBC Driver Manager
/usr/local/lib/libodbcinst.dylib (libodbcinst.2.dylib)Unix ODBC driver installation libary
/usr/local/lib/libmsodbcsql.17.dylibMicrosoft SQL Server 17 ODBC Driver
/usr/local/share/msodbcsql17/resources/en_US/msodbcsql17.rllODBC Driver resource file

The ODBC driver is registered with the Unix ODBC Driver Manager using the odbcinst.ini file which we will place in ~/Library/ODBC/odbcinst.ini. You can create this file yourself or copy the file from Homebrew's "Cellar" folder. You can locate the file using the terminal command:

brew list msodbcsql17 --verbose

then copy the file to your ~/Library/ODBC folder. The default contents of this file are as follows:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib

Gatekeeper Mitigation
macOS Gatekeeper prevents codesigned applications from loading non-signed extensions- such as libodbc.dylib. It is therefore necessary to copy or place a symbolic link to the ODBC library from the Omnis.app/Contents/Frameworks folder.

Example 1: Copy the ODBC libary directly into the Frameworks folder:

cd "Omnis Studio 10.2 30204.app/Contents/Frameworks"
cp /usr/local/Cellar/unixodbc/2.3.9_1/lib/libodbc.2.dylib .
ln -s libodbc.2.dylib libodbc.dylib

Example 2: Create a symbolic link inside the Frameworks folder:

cd "Omnis Studio 10.2 30204.app/Contents/Frameworks"
ln -s /usr/local/Cellar/unixodbc/2.3.9_1/lib/libodbc.2.dylib libodbc.dylib

Setting up an ODBC Datasource
Prior to macOS Big Sur, you can find the macOS ODBC Manager in /Applications/utilities. From the Drivers tab, you can register the ODBC driver manually if required (press Add then locate the driver file as shown).
To add an ODBC User DSN, select the Users tab and press Add. Enter the key-value pairs in the dialog as shown, amending your connection details as required.

To register the ODBC datasource manually, use a text editor and edit the odbc.ini file directly (either /Library/ODBC/odbc.ini or ~/Library/ODBC/odbc.ini). Example::

[ODBC Data Sources]
ntms2017 = ODBC Driver 17 for SQL Server

[ntms2017]
Driver = /usr/local/lib/libmsodbcsql.17.dylib
Description = SQL Server 17 on SERVER-PC
UID = henry
PWD = password
Database = test
Server = 192.168.100.15

 

Connecting from Omnis Studio 10.0.x:
Before starting Omnis, navigate to Omnis Studio 10.0.1.app->Contents/MacOS/xcomp/ini and edit your odbcdam.ini file.
If you require only User-DSNs, the ODBCINI entry should be set to your local user's odbc.ini file, e.g.

ODBCINI=/Users/myUser/Library/ODBC/odbc.ini

If you want to differentiate between User and System DSNs, you can additionally create an ODBCSYSINI entry, e.g.

ODBCINI=/Users/myUser/Library/ODBC/odbc.ini
ODBCSYSINI=/Library/ODBC/odbc.ini

One of the entries should point to the odbc.ini file you edited in the previous step.

 

Connecting from Omnis Studio 10.1 and later:
Please note that in Studio 10.1 and later, separate ".ini" files have been replaced by entries in the "macOS" section of the studio/config.json file.
Locate this section in the file, then edit the "odbcdam.ini" key with the required information, e.g.

"odbcdam.ini": "ODBCINI=/Users/myUser/Library/ODBC/odbc.ini, ODBCSYSINI=/Library/ODBC/odbc.ini"

 Tip: Avoid using ~ when specifying the path to your odbc.ini file; use an explicit path.

Using the Omnis Studio ODBC DAM, it is necessary to change the session object $mode property to kODBCModeUnix.

Do sessionObj.$mode.$assign(kODBCModeUnix)

This should be done before logging on and tells the ODBC DAM to unload the default driver manager and load the Unix ODBC Driver manager (libodbc.dylib on macOS).
Using the SQL Browser session editor, this property can be set via the Advanced tab as shown:

The logon hostname should match the name of the ODBC DSN created above. Add your username, password and optional database details, and save the session template.
You should then be able to open the ODBC session and connect to SQL Server:

Or, using notation:

Do sessionObj.$mode.$assign(kODBCModeUnix)
Do sessionObj.$database.$assign('test')
Do sessionObj.$logon('ntms2017','henry','password','session1') Returns #F

Troubleshooting
If you receive a logon error similar to "Driver manager could not be loaded", this indicaates a problem locating or loading the ODBC driver manager library. Make sure that you either copied the libodbc.2.dylib into Omnis' Frameworks folder and/or make sure that the symbolic link libodbc.dylib exists and points to libodbc.2.dylib. Remember to set the ODBC DAM's $mode session property to kOdbcModeUnix in order to use the Microsoft ODBC Driver.

Error message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
Locate your Omnis writable files folder (e.g. /Users/myUser/Library/Application Support/Omnis/Omnis Studio 30204/) and edit the Studio/config.json file. Under the "macos" section, make sure that the ODBCINI key value matches the location of your odbc.ini file. If you defined a User DSN as opposed to the System DSN, you may need to change the location, or add an ODBCSYSINI entry to differentiate User and System DSNs, for example:

"odbcdam.ini": "ODBCINI=/Users/myUser/Library/ODBC/odbc.ini", "ODBCSYSINI": "/Library/ODBC/odbc.ini", …

References
Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS

 

Search Omnis Developer Resources

 

Hit enter to search

X