Developers

Notes for macOS
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:

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

Configuration files
unixODBC uses two configuration files; odbcinst.ini- used to register ODBC Drivers, and odbc.ini- used to register ODBC Datasource Names (DSNs). There can also be two odbc.ini files; one for System-wide DSNs and one for user DSNs.
By default, unixODBC looks for system DSNs in /etc/odbc.ini or /usr/local/etc/odbc.ini
And it looks for user DSNs in ~/.odbc.ini (a hidden file in the user's home folder).

You can also override the default locations using environment variables, adding these to the Omnis Studio config.json file; inside the "odbcdam.ini" section

ODBCINI	Specifies the full path to the User DSN configuration file.
ODBCSYSINI	Specifies the folder name that contians the System configuration files.
ODBCINSTINI	Specifies the name of the System-wide driver definition file. The driver manager appends this to ODBCSYSINI to derive the actual path.

For example:

"macOS": {
...
"odbcdam.ini": "ODBCINI=/Users/myuser/odbc.ini,ODBCSYSINI=/usr/local/etc,ODBCINSTINI=odbcinst.ini"
...
},...

Registering the ODBC Driver
If you install the Microsoft odbc Driver 18 for SQL Server using brew (as above) then the odbcinst.ini file is configured automatically, for example:

[ODBC]
Trace=NO
TraceFile=/dev/stdout

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.18.lib
UsageCount=1

Setting up an ODBC Datasource

To register the ODBC datasource manually, use a text editor and edit the odbc.ini file directly. The DSN is registered by appending to the relevant odbc.ini file (~/.odbc.ini in this example):

[ODBC Data Sources]
ntms2019 = ODBC Driver 18 for SQL Server

[ntms2019]
Driver = ODBC Driver 18 for SQL Server
Server = 192.168.1.100
TrustServerCertificate = Yes

In the above example Driver should be either a repeat of the Driver name registered with odbcinst.ini, or the full path to the ODBC driver.
Server should be either the IP-address of the SQL Server database, or its "pingable" network domain name.
TrustServerCertificate is required in order for this version of the ODBC driver to make a connection. Failure to include this attribute may be one cause of a "Connection not open" error otherwise. The DSN accepts several other attributes, for example: UID, PWD & Database. However, the above represents the minimum required in order to make a connection.

Omnis Studio - Locating the Driver Manager
macOS Gatekeeper prevents codesigned applications from loading non-signed extensions- such as libodbc.dylib. A reliable way to provision the UnixODBC Driver manager is therefore to copy/create a symbolic link to the ODBC library from the Omnis.app/Contents/Frameworks folder.

For example:

cd "Omnis Studio 11.2 40173.app/Contents/Frameworks"
ln -s /opt/homebrew/Cellar/unixodbc/2.3.12/lib/libodbc.2.dylib libodbc.dylib

   

Using the Omnis Studio ODBC DAM, it is necessary to change the session object $mode property to 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:

Example (using notation):

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

Example (using a connection string):
Note: using a self-contained connection string negates the need for odbcinst.ini and odbc.ini files.

Do sessionObj.$mode.$assign(kODBCModeUnix)
Do sessionObj.$uselogonprompt.$assign(kODBCPromptDsnLess)
Do sessionObj.$logon('Driver={/opt/homebrew/lib/msodbcsql18.dylib};Server=192.168.1.100;Database=master;UID=henry;PWD=password;TrustServerCertificate=Yes','','','session1') Returns #F

Troubleshooting
If you receive a logon error similar to "Driver manager could not be loaded", this indicates 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 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", …

Error message: [unixODBC][Driver Manager] Connection not open.
This error means that the driver manager tried to perform an action using the driver, but the connection was not open. In other words; the ODBC DSN was not found, the DSN was found but the specified driver could not be loaded, or a connection attempt was made but failed. One cause of this error using "ODBC Driver 18 for SQL Server" is omission of the "TrustServerCertificate" attribute or failure otherwise to provide valid SSL credentials.

In this situation, you can test an ODBC connection independently of Omnis Studio using the isql utility. For example:

isql -v ntms2019 henry password

Notes for Linux
This section assumes a Linux Ubuntu setup. For other Linux operating systems, or if using a different driver version, please adjust paths and filenames accordingly.

1. Download & Install the ODBC Driver.
Please refer to the download and installation instructions on the Microsoft ODBC Driver Installation page.
From the Microsoft website, follow the instructions to download and install the msodbcsql18 ODBC driver, which typically installs to:
/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.5.so.1.1

and the required resource file will be here:
/opt/microsoft/msodbcsql18/share/resources/en_US/msodbcsqlr18.rll

2. Configure the ODBC Driver Manager.
The driver package should also install the dependent Unix driver manager package which installs to:
/usr/lib/x86_64-linux-gnu/libodbc.so.2.0.0

The Linux ODBC DAM, looks for a driver manager named "libodbc.so" so it may be necessary to create a symbolic link (or "symlink") that points from a location on the standard library search path to the target file. For example:

cd /usr/local/lib
sudo ln -s /usr/lib/x86_64-linux-gnu/libodbc.so.2.0.0 libodbc.so

3. Setup an ODBC Datasource.
You can locate your odbc.ini file by setting the ODBCINI environment variable inside the omnisX64 startup script. For example:

export LD_LIBRAY_PATH="/home/users/myuser/studio-install-folder":$LD_LIBRARY_PATH
export ODBCINI=/home/users/myuser/odbc.ini
"/home/users/myuser/studio-install-folder/omnis" $1 $2 $3 $4 $5 $6 $7 $8

Alternatively, you can rely on the standard file location used for Linux User DSNs: ~/.odbc.ini (note the leading "dot").
Hint: since .odbc.ini is a hidden file, you might like to create a symbolic link to this file as follows:

cd ~
ln -s .odbc.ini odbc.ini

To create a suitable ODBC User DSN, you should edit your odbc.ini file and add entries similar to:

[ODBC Data Sources]
ntms2019 = Microsoft ODBC Driver for SQL Server
 
[ntms2019]
Driver = /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.5.so.1.1
Description = SQL Server 19 running on SERVER-PC
UID = henry
PWD = password
Database = test
Server = 192.168.100.15
TrustServerCertificate = Yes

If your odbc.ini file already contains ODBC DSNs, the new DSN name should appear at the bottom of the [ODBC Data Sources] section. The corresponding [section] can then appear at the bottom of the file and the section name should match the name of the ODBC Data Source entry, as shown.

Notes
• The DAM's $mode property is not applicable for Linux; the ODBC DAM always looks for libodbc.so.
• Creation of a odbcinst.ini file is not necessary on Linux if the driver file is named explicitly by the ODBC User DSN.
• The standard library search path on Linux includes; locations specified by the LD_LIBRARY_PATH environment variable, the /lib, /usr/lib and /usr/local/lib folders. Each Linux variant may also have addtional locations.
• unixODBC supports System DSNs and User DSNs and each have their own odbc.ini files. System-wide DSNs noramlly reside in /etc/odbc.ini or /usr/local/etc/odbc.ini whereas User-DSNs normally reside in the dot-prefixed file; ~/.odbc.ini. The locations can also be specified using the ODBCSYSINI and ODBCINI environment variables respectively. If specified, ODBCINI identifies the full path to the odbc.ini file, e.g. /home/myUser/odbc.ini whereas ODBCSYSINI should refer to the containing folder name only, e.g. /usr/local/libxyz

Toubleshooting
Error message: [unixODBC][Driver Manager] Connection not open.
This error means that the driver manager tried to perform an action using the driver, but the connection was not open. In other words; the ODBC DSN was not found, the DSN was found but the specified driver could not be loaded, or a connection attempt was made but failed. One cause of this error using "ODBC Driver 18 for SQL Server" is omission of the "TrustServerCertificate" attribute or failure otherwise to provide valid SSL credentials.

In this situation, you can test an ODBC connection independently of Omnis Studio using the isql utility. For example:

isql -v ntms2019 henry password