setup Perl to use UnixODBC for SQL Server

(Taking reference from:  http://www.codesynthesis.com/~boris/blog/2011/12/02/microsoft-sql-server-odbc-driver-linux/ )

To setup Perl to use Unix ODBC for SQL Server, follow the steps below:

I. Install DBI
  1. Download  Perl DBI module:  http://www.cpan.org/modules/by-module/DBI/DBI-1.630.tar.gz
  2. Unpack the archive.
  3. cd to the directory created after the unpacking
  4. execute the following commands one at a time:
        perl Makefile.PL
        make
        make test
        make test TEST_VERBOSE=1   (if any of the t/* tests fail)
        make install (if the tests look okay)

II. Install UnixODBC
  1. First make sure that any older version of the unixODBC that you may have installed is removed:

    $ apt-get remove libodbc1 unixodbc unixodbc-dev

  2. Download ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz
  3. Unpack the archive.
  4. cd to the directory created after the unpacking
  5. execute the following commands one at a time:
       

    $ ./configure --disable-gui --disable-drivers 
              --enable-iconv --with-iconv-char-enc=UTF8 
              --with-iconv-ucode-enc=UTF16LE
          $ make
          $ sudo make install

III.  Setup MS ODB Linux driver

  1. Download the MS driver:  http://www.microsoft.com/download/en/details.aspx?id=28160

  2. Unpack the tar.gz archive.

  3. cd to the lib64 directory inside the directory created after the unpacking.  This 

     is where the driver (libsqlncli-11.0.so.1720.0) is located.

  4. check the missing dependencies of the driver:

        $ ldd libsqlncli-11.0.so.1720.0

     This will show something like the following:

        libcrypto.so.10 => not found
                    libodbc.so.1 => /usr/local/lib/libodbc.so.1
                    libssl.so.10 => not found
                    libuuid.so.1 => /lib/libuuid.so.1
                    libodbcinst.so.1 => /usr/local/lib/libodbcinst.so.1
                    libkrb5.so.3 => /usr/lib/libkrb5.so.3
                    libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2
                    libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6
                    libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1
                    libltdl.so.7 => /usr/lib/libltdl.so.7
                    libk5crypto.so.3 => /usr/lib/libk5crypto.so.3
                    libkrb5support.so.0 => /usr/lib/libkrb5support.so.0
        libkeyutils.so.1 => /lib/libkeyutils.so.1
        ...                

Which indicated that I had libcrypto.so.6 and libssl.so.6 missing. As a general approach to resolving missing dependencies, you can enter the library name in the Debian package search or Ubuntu package search (use the “Search the contents of packages” section) and then install the package that contains the missing library.

However, if you try to do this for libcrypto.so.10 or libssl.so.10, you won’t find any packages. The reason for this is the different versioning schemes used for these libraries in RedHat EL and Debian/Ubuntu. (The ODB driver was designed for RedHat Linux distros.) In Debian/Ubuntu the equivalent libraries are called libssl.so.1.0.0 & libcrypto.so.1.0.0 .

            So to get hold of these libraries:         

                  sudo apt-get update
                  sudo apt-get install libssl1.0.0 libssl-dev

            Now lets fix the naming of the file by creating a link:

          cd /lib/x86_64-linux-gnu
          sudo ln -s libssl.so.1.0.0 libssl.so.10
          sudo ln -s libcrypto.so.1.0.0 libcrypto.so.10

Also note that if you have “not found” next to libodbc.so.1 (the unixODBC driver manager we have just installed), then this most likely means that /usr/local/lib is not in your dynamic linker search path. If that’s the case, add it to the /etc/ld.so.conf and don’t forget to reload the cache by running ldconfig as root.
Once all the dependencies are met, we can finally run the script to install the driver. We have to use the --force option to ignore some of the compatibility tests performed by the script:

                    $ sudo bash ./install.sh install --force 

          To test the installation you can try to connect to the local host using sqlcmd:
                    $ sqlcmd -S localhost 

 Unless you are running the Linux edition of SQL Server (wink wink) you should get an error message indicating that a network connection could not be established. Any other error, such as inability to load a shared library, most likely indicates a missing dependency or a configuration error.

IV. Install DBD::ODBC

  1. Issue "sudo cpan".  If it's the first time you run this command, you may be required to setup its local libraries.  Just use all the default options in the wizard.  After following the wizard, in the cpan prompt, type:

        install DBD::ODBC

            This will install DBD::ODBC module.

That's it.  You can then execute the Perl script you have for connecting with & operating on tables of a database.  The DSN, username & password can just be embedded in your Perl script.

Note:

- My setup is:
  - OS:  Ubuntu 12.04 LTS 64 bit
  - SQL Server version:  11.0
  - unix ODBC:  2.3.0
  - DBI:  1.630
  -  MS ODB:  1.0