Setup unixODBC on the Mac

Introduction

This article explains how to setup the unixODBC driver manager on Mac.

We need to use unixODBC on the Mac because the default driver manager is not compatible with ODBC drivers for some databases, for example:

  • Oracle
  • PostgreSQL
  • Microsoft SQL Server ODBC (from Microsoft)
  • FreeTDS for Microsoft SQL Server (3rd party open source)

It covers the following topics:

  1. How to install unixODBC.
  2. How to setup a symbolic link to the unixODBC driver manager:

    We need to link the default driver manager file (libiodbc.dylib) to the unixodbc driver manager file (libodbc.dylib), see the How it works section for more information.

Setup unixODBC [top]

  1. Install unixODBC:
    1. Go to the homebrew website.
    2. Terminal command: Run the Install Homebrew command (from the top of the page).

      We could have copied the command here but it may change in the future — so it is safer just to copy from the homebrew site. If you already have homebrew installed you can skip this step.

    3. Terminal commands: Check that homebrew is up to date and healthy:
      brew update 
      brew doctor
    4. Terminal command: Install unixodbc:
      brew install unixodbc
    5. Use Finder to confirm the installation worked, you should see the following:

      The /usr/local directory is hidden in Finder, so use Go>Go to Folder and enter “/usr/local” to view it.

      • A Cellar directory is created to store homebrew installed items.
      • The unixodbc directory and other the libtool directory for the libtool dependency:
      • The unixodbc dynamic library files:
        unixodbc homebrew installed files
  2. Turn off System Integrity Protection (SIP):

    We need to do this before we can perform the next step — because it requires changes to files in the /usr/lib directory. The /usr/lib directory is protected (even when using sudo) while SIP is enabled, therefore we need to disable SIP to allow us to make changes.

    1. Restart in Recovery mode:
      1. Restart from the Apple Menu.
      2. Hold down the Command+R keys while the Mac reboots.
      3. When the Mac restarts you should see the macOS Utilities screen:

        If the Mac reboots normally then you did not press the Command+R keys soon enough — just restart again and make sure you press the the Command+R keys immediately you hear the chime when Mac restarts (or just hold the keys immediately after selecting restart until a few seconds after the chime).

        recovery mode utilities

    2. Select Terminal from the Utilities menu:
      select Utilities>Terminal
    3. Terminal Command: Disable SIP:
      csrutil disable

      run csrutil disable

  3. Setup a symbolic link (alias) to the unixODBC driver manager:

    Iguana loads the default libiodbc.dylib library on the Mac. We need Iguana to use the unixODBC libodbc.dylib library instead. To achieve this we replace libiodbc.dylib with a symbolic link (symlink) that points to the unixODBC libodbc.dylib library instead.

    1. Rename /usr/libiodbc.dylib to /usr/libiodbc.IODBC.dylib:

      If you are unable to rename the symbolic link (alias) it means that SIP is still enabled — you will need to repeat the previous step to disable SIP.

      1. Use finder to rename the file:

        The /usr/lib directory is hidden in Finder, so use Go>Go to Folder and enter “/usr/lib” to view it.

      2. Terminal Command: Alternatively use this command:
        sudo mv /usr/lib/libiodbc.dylib /usr/lib/libiodbc.IODBC.dylib
      3. The symbolic link (alias) should now look like this:
        renamed libiodbc.dydlib
    2. Create a new /usr/libiodbc.dylib symbolic link (alias) to …/libiodbc.dylib
      1. Terminal Command: Create a new symbolic link:

        Replace <version> to match the version of the unixodbc libraries that you are using.

        sudo ln -s /usr/local/Cellar/unixodbc/<version>/lib/libodbc.2.dylib /usr/lib/libiodbc.dylib
      2. The new symbolic link (alias) should now look like this:
        new libiodbc.dylib link
      3. Right click on the link and choose Show Original to confirm it is pointing to libiodbc.dylib:
        show original
      4. You should see the original file highlighted (in blue), something like this:
        original unixodbc drver
  4. Optional: Turn System Integrity Protection (SIP) back on:

    Apple recommends that SIP is turned on to help protect your machine from viruses and accidental changes to system files. If your Mac is a development or testing machine then having SIP disabled not a big issue (your choice), but if this is a production server you really should re-enable SIP.

    Note: If this machine is going to be a Production Server we strongly recommend re-enabling SIP.

    1. Terminal Command: Follow the step (2) instructions using “enable” instead of “disable”:
      csrutil enable
    2. You can test that it worked by trying to rename /usr/lib/libiodbc.IODBC.dylib to something like libiodbc.IODBCxxxx.dylib:

      If you can still rename it then you need to repeat the enable process.

How it works [top]

unixODBC:

We need to use unixODBC on Mac because the default Apple driver manager (libiodbc.dylib) is not compatible with ODBC drivers for some databases. This makes sense as many of the drivers are also used for Linux where unixODBC is dominant — and because of this they are designed to be dependent on various unixODBC libraries.

For example:

  • Oracle:

    The Oracle Instant Client ODBC drivers for Mac appear to dependent on unixODBC, as we were unable to get them working with the default lidiobdc.dylib manager libraries.

  • PostgreSQL:

    The homebrew formulae page for the psqlodbc ODBC drivers states it is dependent on openssl, postgresql and unixodbc.

  • Microsoft SQL Server ODBC:

    The Microsoft SQL Server ODBC drivers for Mac appear to dependent on unixODBC, as we were unable to get them working with the default lidiobdc.dylib manager libraries.

  • FreeTDS for SQL Server:

    The homebrew formulae page for the freetds ODBC drivers states it is dependent on openssl, and unixodbc.

Tip: The Homebrew Formulae page is quite useful for checking dependencies. You can also use the otool -L command to check dependencies for dynamic libraries.

To use otools you will need to install Xcode, because otools is part of the CLI Developer Tools package (in the XCode download).

Symbolic link (alias) to the unixODBC driver file:

Iguana automatically recognizes and loads the default Apple ODBC Manager file (libiodbc.dylib). Iguana does not have an option to choose a different odbc driver manager like unixODBC (libodbc.dylib).

Therefore to use the unixODBC driver we simply replace the default ODBC manager link (libiodbc.dylib), with one that points to the unixODBC dynamic library (libodbc.dylib).

More information [top]

Leave A Comment?