Connect to Microsoft SQL Server from Linux or Mac with ODBC

Introduction

This article explains how to connect to Microsoft SQL Server using unixODBC and FreeTDS on Linux and the Microsoft ODBC driver on the Mac. It includes installation and configuration instructions for Ubuntu, CentOS and the Mac.

It covers the following topics:

  1. How to connect to Microsoft SQL Server (with code samples).
  2. How to setup ODBC:
      • Install unixODBC:

        This is needed because FreeTDS uses the unixODBC driver manager on Linux. The unixODBC install also include utilities  isql and iusql.

      • Install freeTDS on Linux:

        You can configure FreeTDS using only odbc.ini, which contains the ODBC DSN details. You can optionally using odbcinst.ini as well but it is not needed.

      • Install the Microsoft ODBC driver on the Mac:

        We used the Microsoft driver on the Mac as we were unable to get FreeTDS to work.

      • Configure ODBC using the odbc.ini file:

        We recommend using system DSNs (available to all users), which use the /usr/odbc.ini file. User DSNs can be setup in the $HOME/.odbc.ini file.

Note: FreeTDS supports the proprietary DB and CT libraries (from Sybase and Microsoft) as well as ODBC. Because we are only using ODBC we do not need to set up the FreeTDS configuration file freetds.conf (as it is only used by the other libraries that FreeTDS supports, called the DB and CT libraries).

Connect to Microsoft SQL Server [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples. The code samples will work with all operating systems.

Follow these steps to connect:

  1. Create a new channel to try out the code:
    • Create a channel like this:
      • Name: DB SQL Server Connection (or similar)
      • Source: From Translator
      • Destination: To Channel
    • Alternatively use an existing channel:

      Your channel will need a From/To Translator or a Filter component.

  2. Connect to a database using ODBC:
    • Paste this code into the Translator:

      If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote Microsoft SQL Server database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

      function main()
            local conn = db.connect{   
            api=db.SQL_SERVER, 
            name='your_odbc_server_name',
            user='your_login', 
            password='secret',
            use_unicode = true,
            live = true
         }
      
         conn:execute{sql='SELECT * FROM <your table>', live=true}
      end
    • The name is the same as the server entry in the odbc.ini file.
  3. Adapt the code to your requirements.

Setup ODBC [top]

Note: Iguana may not always support the very latest version of the FreeTDS ODBC drivers, so you can simply download an earlier version if necessary. This is particularly true if you are not able to use the the latest version of Iguana.

If you need to know which versions of MySQL are supported by the version of Iguana you are using please contact us at support@interfaceware.com.

CentOS:

Note: The procedure and screenshots in this section are from CentOS 7 — and may differ somewhat if you are using a different version of CentOS , or another Red Hat based Linux.

  1. Install the unixODBC driver manager:
    1. Terminal command: Get the latest package info:
      yum check-update
    2. Terminal command: Install unixodbc:
      yum install unixODBC.x86_64
  2. Install the Free TDS ODBC package:
    1. Terminal command: Install the EPEL package that contains freetds (terminal command):
      sudo yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
    2. Terminal command: Install freetds:
      sudo yum install freetds
  3. Check file names and create symbolic links to the versioned file names if needed:
    1. Terminal command: Check if the expected files exist:
      ls /usr/lib64/libtds* -l
    2. In this case version numbers were appended to the library file names so we will create symbolic links from the original names:
      libtds filename check
    3. Terminal command: Create a symbolic link from libtdsodbc.so to libtdsodbc.so.0.0.0:
      sudo ln -s /usr/lib64/libtdsodbc.so.0.0.0 /usr/lib64/libtdsodbc.so
    4. Terminal command: Create a symbolic link from libtdsS.so to libtdsS.so.2.0.0:
      sudo ln -s /usr/lib64/libtdsS.so.2.0.0 /usr/lib64/libtdsS.so
    5. Terminal command: Check the that the symbolic links now exist:
      ls /usr/lib64/libtds* -l
    6. And as we can see the expected symbolic links now exist:
      libtds confirm symlinks
  4. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular Microsoft SQL Server and MySQL use Server to identify the server name, whereas some other databases use Servername (for example Oracle and PostgreSQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    Note: The Port parameter is not a valid ODBC entry for Microsoft SQL Server — instead you should append it to the Server parameter separatedby a comma, like this:

    Server = your_server_name, 1433
    1. Format your entries like this: :
      • Bare minimum:
        Note: Change [My SQL Server Connection name] to to the name you prefer for your ODBC connection.

        [My SQL Server Connection name]
        Driver = /usr/lib64/libtdsodbc.so
        Setup  = /usr/lib64/libtdsS.so
        Server = your_server_name, 1433
        
      • With user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My SQL Server Connection name]
        Driver   = /usr/lib64/libtdsodbc.so
        Setup    = /usr/lib64/libtdsS.so
        Server   = your_server_name, 1433 <network name or IP address for the server + optional Port number>
        Database = dbname <if omitted the default database will be used>
        Username = dbusername <if omitted then integrated security is used - needs kerberos>
        Password = dbpassword
  5. Check the location of ODBC ini files on your system:
    1. Terminal command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/home/parallels/.odbc.ini)
        odbc ini locations
  6. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
        Note: You need administrator permission to edit this file.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal command: Append the entry from your template file:
          odbcinst -i -d -h -f template_file
    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal command: Append the entry from your template file:
          odbcinst -i -d -l -f template_file
    3. An ODBC ini file will look something like this after a new entry is added:
      new odbc.ini slq server entry
  7. If the connection does not work speak to your DBA (database administrator) to troubleshoot the settings.

Ubuntu:

Note: The procedure and screenshots in this section are from Ubuntu 18.04 — and may differ somewhat if you are using a different version of Ubuntu, or another Debian based Linux.

  1. Install the unixODBC driver manager:
    1. Terminal command: Get the latest package info:

      sudo apt update

    2. Terminal command: Install unixodbc:

      sudo apt install unixodbc

  2. Install the Free TDS ODBC package
    • Terminal command: Install freetds:

      sudo apt install tdsodbc

  3. Check file names and create symbolic links to the versioned file names if needed:
    1. Terminal command: Check if the expected files exist:

      ls /usr/lib/x86_64-linux-gnu/odbc/libtds* -l

    2. And as we can see the the expected files exist:
      libtds filename check
  4. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular Microsoft SQL Server and MySQL use Server to identify the server name, whereas some other databases use Servername (for example Oracle and PostgreSQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    Note: The Port parameter is not a valid ODBC entry for Microsoft SQL Server — instead you should append it to the Server parameter separatedby a comma, like this:

    Server = your_server_name, 1433
    1. Format your entries like this:
      • Bare minimum:
        Note: Change [My SQL Server Connection name] to to the name you prefer for your ODBC connection.

        [My SQL Server Connection name] 
        Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so 
        Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so 
        Server = your_server_name, 1433
      • With user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My SQL Server Connection name]
        Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
        Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
        Server = your_server_name, 1433 <network name or IP address for the server + optional Port number>
        Database = dbname <if omitted the default database will be used>
        Username = dbusername <if omitted then integrated kerberos security is used>
        Password = dbpassword
  5. Check the location of ODBC ini files on your system:
    1. Terminal command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/home/parallels/.odbc.ini)
        odbc ini locations
  6. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
        Note: You need administrator permission to edit this file.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal command: Append the entry from your template file:
          odbcinst -i -d -h -f template_file
    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal command: Append the entry from your template file:
          odbcinst -i -d -l -f template_file
    3. An ODBC ini file will look something like this after a new entry is added:
      new odbc.ini sql server entry
  7. If the connection does not work speak to your DBA (database administrator) to troubleshoot the settings.

Mac:

We used the

Note: The procedure and screenshots in this section are from macOS Mojave — and may differ somewhat if you are using a different version of macOS.

  1. Install the latest Microsoft ODBC Driver for SQL Server:
    1. Go to the homebrew website.
    2. Terminal command: Run the Install Homebrew command (from the top of the page).

      I 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 commands: Install Microsoft ODBC Driver 17 for SQL Server (msodbcsql17):

      You can check for the latest version here: Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS. At the time of writing version 17 was the latest.

      brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
      brew update
      brew install --no-sandbox msodbcsql17 mssql-tools
    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 msodbcsql17 directory.
      • The msodbcsql17 drivers:
        msodbcsql17 ODBC drivers
      • Symbolic link (alias) for the msodbcsql17 driver in /usr/local/lib:
        link to msodbcsql17 driver
  2. Setup the unixODBC driver manager:

    We need to use unixODBC because the default Apple driver manager does not work with some databases, see Setup unixODBC on the Mac for more information.

  3. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular MySQL and Microsoft SQL Server use Server to identify the server name, whereas some other databases use Servername (for example Oracle and PostgreSQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    1. Format your entries like this:
      • Bare minimum UNICODE driver (libmyodbc8w.so):
        Note: Change [My SQL Server connection name] to the name you prefer for your ODBC connection.

        [My SQL Server connection name] 
        Driver = /usr/local/lib/libmsodbcsql.17.dylib
        Server = your_server_name, 1433
        
      • With user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My SQL Server connection name] 
        Driver = /usr/local/lib/libmsodbcsql.17.dylib
        Server = your_server_name, 1433 <network name or IP address for the server "," port no> 
        Database = dbname <if omitted the default database will be used> 
        Username = dbusername <if omitted then integrated security is used - needs kerberos> 
        Password = dbpassword
  4. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /usr/local/etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/Users/julianmuir/.odbc.ini):
        odbcinst -j
  5. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified in step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:

          odbcinst -i -d -l -f template_file

    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:

          odbcinst -i -d -h -f template_file

    3. An ODBC ini file will look something like this after a new entry is added:
      oddb.ini new entry

If you have problems configuring the ODBC driver or connection speak to your DBA (database administrator).

Troubleshooting [top]

  • Problems with connection settings:

    Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

  • Iguana may not always support the very latest version of the FreeTDS ODBC drivers:

    Please contact us at support@interfaceware.com if you need to know the latest supported version of the FreeTDS ODBC drivers.

  • Incorrect port number:

    SQL Server defaults to port 1433, but a different port can be used. Speak to your database administrator (DBA) or network administrator.

  • Using integrated security:

    Kerberos is needed on Linux, this SQL Server page Using Integrated Authentication has some information

  • Versioned Packages:

    When a package installs a shared library, it can append a version number, so double check the actual library names and if needed symlink them to the library names shown in the previous section (libtdsodbc.so & libtdsS.so), for example:

    sudo ln -s /usr/lib/x86_64-linux-gnu/libodbc.so.1 /usr/lib/x86_64-linux-gnu/libodbc.so

If you need more help please contact us at support@interfaceware.com.

More Information [top]

Leave A Comment?