Exploratory Community

Connecting to MS SQL Server through ODBC from Mac

#1

Hi!
Here is how I was able to create ODBC DSN for MS SQL Server, to import SQL Server data into Exploratory 3.0 on Mac.

  1. Update homebrew to the latest first.
    brew update

  2. Install libiodbc with homebrew
    brew install libiodbc

  3. Start interactive mode on homebrew to Install freetds. This should start a new shell of interactive mode.
    brew install freetds -i
    If you get error telling freetds is already installed, run the following to uninstall it once, and retry the above command.
    brew uninstall freetds

  4. Run following commands in the interactive mode session to install freetds that works with libiodbc.

$ ./configure --prefix=/usr/local/Cellar/freetds/1.1.5 --with-tdsver=7.3 --mandir=/usr/local/Cellar/freetds/1.1.5/share/man --sysconfdir=/usr/local/etc --with-iodbc --with-openssl=/usr/local/opt/openssl --enable-sybase-compat --enable-krb5 --enable-odbc-wide
$ make
$ make install
$ exit
  1. Make sure that freetds ODBC driver library file is successfully created.
    ls /usr/local/lib/libtdsodbc.so

  2. To install latest version of ODBC Manager, download Actual ODBC Pack from http://www.actualtech.com/download.php and install it. Along with trial version of their ODBC drivers, ODBC Manager is installed under /Applications/Utilities/ .

  3. Start ODBC Manager and click “Drivers” tab

  4. Click “Add” and register the driver file /usr/local/lib/libtdsodbc.so as “freetds”.

  5. Click “OK”, and you will see “freetds” added to the list of your ODBC drivers.

  6. Click “User DSN”, and you will see the list of your DSNs. We will add one more here.

  7. Click “Add” and choose “freetds” as the driver for our new ODBC DSN.

  8. Click “OK”, and you will see following dialog to create new ODBC DSN.

  9. Name your DSN, and add 3 keywords, which are “Server”, “Port”, and “Database”, according to the respective values for your SQL Server. Click “OK”, and you will see screen like below.

  10. Now you have new ODBC DSN with which you can connect Exploratory to your SQL Server.

  11. Follow this page http://docs.exploratory.io/import/odbc.html to register the ODBC DSN we just created to Exploratory, and you are all set to import your data from SQL Server to Exploratory!