Access MS SQL Server from UNIX in ten minutes

In this example for Linux we will install the unixODBC driver manager and an ODBC Driver and get data back from Microsoft SQL Server. We are ignoring access from any particular programming language or interface and just use the isql utility that comes with unixODBC but for further information about access from PHP, Perl, Python etc see
Easysoft development.

You will need to know:

  1. the name or IP address of the machine where MS SQL Server is installed.
  2. for SQL Server authentication, a valid SQL Server username/password
  1. Fire up your package manager (e.g., on Ubuntu it is under System -> Administration -> Synaptic Package Manager)
  2. Find the main unixODBC packages which are probably separated into unixODBC binary package, GUI tools and a development package. You only need to install the main unixODBC binary package for this demonstration but you will need the development package later on if you are using a programming language like Perl later.

    Install the unixODBC binary package. This usually gives you the unixODBC driver manager shared object and the command line tool isql.

    If you are not using Linux or a UNIX system which packages unixODBC as a binary you will either need to compile unixODBC yourself or (better) install the unixODBC that comes with the driver.

  3. Go to Easysoft SQL Server and download the SQL Server ODBC driver for your platform.
  4. untar the SQL Server ODBC Driver with

    tar -xvf odbc-sqlserver-1.2.0-linux-x86-glibc.tar

    and change into the created directory.

  5. Run the installer:

    sudo ./install

    or become root and run ./install

  6. You can take most of the defaults but I note the following questions:
    • it is advisable to install the driver in /usr/local/easysoft since there is extra work to perform if you do not.
    • Since you have installed the unixODBC driver manager already it is worth considering using your installed unixODBC instead of the included one (the default of installing the unixODBC which comes with the driver is usually the best default on non-Linux platforms but on Linux platforms maintaining your packaged version is usually better). As a result, if you already have unixODBC installed stick with that version.
    • When you get to the part of the install which requires a license take the default of yes, enter [2] for a SqlServer ODBC Driver license and enter the information when prompted for name, company name, email, telephone (which may be omitted - hit enter) and similarly with fax, and leave the prompt for "authorisation code" empty to get a trial license. If your machine is connected to the internet then select "[1] to automatically obtain a trial license" else you need to use the other options to obtain a trial license.
    • You will be asked if you have a SQL Server that can be accessed. I'm assuming you have so answer the prompts and it will build a connection string to connect to SQL Server and save this as a DSN (Data Source Name) you can use later to connect to SQL Server. NOTE if you are using SQL Server Express you need to append \SQLEXPRESS on to the end of the host name or IP address.
  7. You should now have a DSN in the /etc/odbc.ini file with the name you provided. If you installed unixODBC from a OS package isql and the driver shared objects should be on your PATH and installed into the dynamic linker but otherwise you'll have to add /usr/local/easysoft/unixODBC/bin to your PATH and export it. If this applies to you then you will probably have to add /usr/local/easysoft/unixODBC/lib and /usr/local/easysoft/sqlserver/lib to your LD_LIBRARY_PATH (or SHLIB_PATH, LD_RUNPATH, LIBPATH depending on your platform) and export it too.
  8. Run

    isql -v my_dsn_name username password

    where my_dsn_name is the name you provided for your DSN during installation and username and password are for you SQL Server.

    e.g.,

    isql mydsn sa password

    Type "help" and return and you should get a list of tables.

    For SQL Server there is a table called informaton_shema.tables so you should be able to do:

    select * from information_schema.tables

    and get a list of tables. You should also be able to select data from any table although isql is only a test program and does not display blobs.

Having proven you can connect to MS SQL Server you can now install programming interfaces like DBD::ODBC (perl) or pyodbc (Python) etc and access your SQL Server data.