Installing MS SQL Server Client on Linux (Vertica nodes)

blur close up code computer
Photo by luis gomes on Pexels.com

We are running Centos 6.5 (Redhat version) of Linux on our DEV, STG, PROC clusters. Centos 6.5 comes with default unixODBC 2.2.x installed!

unixODBC 2.2.x is NOT supported by MS SQL Server Linux client. If you try to install any VERSIONs SQL-Server client for Linux 6.x without installing unixODBC 2.3.0 (Not 2.3.+) it’ll will end up in tears…. (sad). Even the latest version of the DM 2.3.6 does not work.

This section outlines the steps for uninstalling default unixODBC 2.2.x, installing unixODBC Driver Manager version 2.3.0, configure and validate, and then install Microsoft ODBC Driver 11 for SQL Server.

Download the MS ODBC Driver:

Go to https://www.microsoft.com/en-us/download/details.aspx?id=36437 and download the appropriate zip file to your Linux server. Extract the .gz file ina temporary location and install the driver as follows:

$ sftp sbalas002c@COENGS-VRTD01
sbalas002c@coengs-vrtd01's password:
Connected to COENGS-VRTD01.
sftp> put Downloads/msodbcsql-11.0.2270.0.tar.gz /tmp/.
Uploading Downloads/msodbcsql-11.0.2270.0.tar.gz to /tmp/./msodbcsql-11.0.2270.0.tar.gz
Downloads/msodbcsql-11.0.2270.0.tar.gz                                                                                                100% 1156KB 596.7KB/s   00:01
sftp>
Version 11 is for Linux 5 and 6, where as version 13 is certified for Linux 6 and 7.

Extract and install the driver:

[root@COENGS-VRTD01 ~]# ll /tmp/
total 1168
drwxr-xr-x 2 root       root           4096 Nov 15 22:42 hsperfdata_root
-rw-r--r-- 1 sbalas002c linux-users 1184123 Apr  7 14:13 msodbcsql-11.0.2270.0.tar.gz
[root@COENGS-VRTD01 ~]# cd /tmp/
[root@COENGS-VRTD01 tmp]# tar -xz -f msodbcsql-11.0.2270.0.tar.gz
[root@COENGS-VRTD01 tmp]# ll
total 1172
drwxr-xr-x 2 root       root           4096 Nov 15 22:42 hsperfdata_root
drwxrwxr-x 6 root       root           4096 Jan 15  2013 msodbcsql-11.0.2270.0
-rw-r--r-- 1 sbalas002c linux-users 1184123 Apr  7 14:13 msodbcsql-11.0.2270.0.tar.gz
[root@COENGS-VRTD01 tmp]# cd msodbcsql-11.0.2270.0
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# ls
bin  build_dm.sh  docs  include  install.sh  lib64  LICENSE  README  WARNING
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# ./install.sh install --accept-license --force 1> install_std.log 2> install_err.log
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# ll
total 88
drwxrwxr-x 2 root root  4096 Jan 15  2013 bin
-rwxr-xr-x 1 root root 10001 Jan 15  2013 build_dm.sh
drwxrwxr-x 2 root root  4096 Jan 15  2013 docs
drwxrwxr-x 2 root root  4096 Jan 15  2013 include
-rw-r--r-- 1 root root     0 Apr  7 14:21 install_err.log
-rwxr-xr-x 1 root root 23323 Jan 15  2013 install.sh
-rw-r--r-- 1 root root  1036 Apr  7 14:21 install_std.log
drwxrwxr-x 2 root root  4096 Jan 15  2013 lib64
-rw-r--r-- 1 root root 17327 Jan 15  2013 LICENSE
-rw-r--r-- 1 root root  7103 Jan 15  2013 README
-rw-r--r-- 1 root root  1105 Jan 15  2013 WARNING
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# cat install_std.log
Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft ODBC Driver 11 for SQL Server
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ......................... FAILED
unixODBC Driver Manager configuration correct ...................... NOT CHECKED
Microsoft ODBC Driver 11 for SQL Server already installed .......... NOT CHECKED
Microsoft ODBC Driver 11 for SQL Server files copied ........................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft ODBC Driver 11 for SQL Server registered ................... INSTALLED
Install log created at /tmp/msodbcsql.16234.24983.880/install.log.
One or more steps may have an *. See README for more information regarding
these steps.
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#

Please note above that install FAILED with Error:  unixODBC Driver Manager version 2.3.0 installed ……………………. FAILED

Also you can test the MS ODBC driver for SQL Server by using dltest tool.

[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# dltest /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 SQLGetInstalledDrivers
[dltest] ERROR dlopen: file not found
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#

We have to remove the default unixODBC Driver manager which is version 2.2.x in CentOS 6.5, re-install 2.3.0 version and then continue with MS ODBC driver.

Verify and Remove unixODBC 2.2.14:

[root@COENGS-VRTD01 ~]# odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[root@COENGS-VRTD01 ~]# isql --version
unixODBC 2.2.14
[root@COENGS-VRTD01 ~]#

You can remove the unixODBC driver manager using yum remove.

[root@COENGS-VRTD01 tmp]# yum remove unixODBC
Loaded plugins: fastestmirror, security
Setting up Remove Process
Resolving Dependencies
--> Running transaction check
---> Package unixODBC.x86_64 0:2.2.14-14.el6 will be erased
--> Processing Dependency: libboundparam.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libesoobS.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libgtrtst.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libmimerS.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libnn.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libodbccr.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libodbcdrvcfg1S.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libodbcdrvcfg2S.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libodbcminiS.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libodbcnnS.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libodbctxtS.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: liboplodbcS.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: liboraodbcS.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libsapdbS.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libtdsS.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: libtemplate.so.2()(64bit) for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Processing Dependency: unixODBC = 2.2.14-14.el6 for package: unixODBC-devel-2.2.14-14.el6.x86_64
--> Running transaction check
---> Package unixODBC-devel.x86_64 0:2.2.14-14.el6 will be erased
--> Finished Dependency Resolution
Dependencies Resolved
========================================================================================================================================================================
 Package                                     Arch                                Version                                       Repository                          Size
========================================================================================================================================================================
Removing:
 unixODBC                                    x86_64                              2.2.14-14.el6                                 @base                              1.1 M
Removing for dependencies:
 unixODBC-devel                              x86_64                              2.2.14-14.el6                                 @base                              182 k
Transaction Summary
========================================================================================================================================================================
Remove        2 Package(s)
Installed size: 1.3 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Erasing    : unixODBC-devel-2.2.14-14.el6.x86_64                                                                                                                  1/2
  Erasing    : unixODBC-2.2.14-14.el6.x86_64                                                                                                                        2/2
warning: /etc/odbcinst.ini saved as /etc/odbcinst.ini.rpmsave
warning: /etc/odbc.ini saved as /etc/odbc.ini.rpmsave
  Verifying  : unixODBC-devel-2.2.14-14.el6.x86_64                                                                                                                  1/2
  Verifying  : unixODBC-2.2.14-14.el6.x86_64                                                                                                                        2/2
Removed:
  unixODBC.x86_64 0:2.2.14-14.el6
Dependency Removed:
  unixODBC-devel.x86_64 0:2.2.14-14.el6
Complete!
[root@COENGS-VRTD01 tmp]#

Download, extract, and install unixODBC

Download unixODBC version 2.3.0 from https://sourceforge.net/projects/unixodbc/files/unixODBC/2.3.0/ into a temporary location.

$ sftp sbalas002c@COENGS-VRTD01
sbalas002c@coengs-vrtd01's password:
Connected to COENGS-VRTD01.
sftp> put Downloads/unixODBC-2.3.0.tar.gz /tmp/.
Uploading Downloads/unixODBC-2.3.0.tar.gz to /tmp/./unixODBC-2.3.0.tar.gz
Downloads/unixODBC-2.3.0.tar.gz                                                                                                       100% 1762KB 702.7KB/s   00:02
sftp> quit

Login as root (or you must have sudo to root). You have to set CPPFLAGS environment variable and configure make environment.

[root@COENGS-VRTD01 tmp]# ll
total 4580
drwxr-xr-x  2 root       root           4096 Nov 15 22:42 hsperfdata_root
drwxrwxr-x  6 root       root           4096 Apr  7 14:21 msodbcsql-11.0.2270.0
-rw-r--r--  1 sbalas002c linux-users 1184123 Apr  7 14:13 msodbcsql-11.0.2270.0.tar.gz
-rw-r--r--  1 root       root            301 Apr  7 23:58 tmpodbcinst.ini
drwxr-xr-x 19       1000        1000    4096 Apr 20  2010 unixODBC-2.3.0
-rw-r--r--  1 sbalas002c linux-users 1804749 Apr  8 13:43 unixODBC-2.3.0.tar.gz
drwxrwxr-x 20       1000        1000    4096 Apr  7 20:11 unixODBC-2.3.6
-rw-r--r--  1 sbalas002c linux-users 1661914 Apr  7 19:52 unixODBC-2.3.6.tar.gz
[root@COENGS-VRTD01 tmp]# tar -xz -f unixODBC-2.3.0.tar.gz
[root@COENGS-VRTD01 tmp]# cd unixODBC-2.3.0
[root@COENGS-VRTD01 unixODBC-2.3.0]#
[root@COENGS-VRTD01 unixODBC-2.3.0]# CPPFLAGS="-DSIZEOF_LONG_INT=8"
[root@COENGS-VRTD01 unixODBC-2.3.0]# export CPPFLAGS
[root@COENGS-VRTD01 unixODBC-2.3.0]# ./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
checking for gawk... (cached) gawk
checking for bison... bison -y
checking for style of include used by make... GNU
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
. . . . SNIP SNIP SNIP  . . . .
configure: creating ./config.status
config.status: creating Makefile
config.status: creating config.h
config.status: executing depfiles commands
config.status: executing libtool commands
[root@COENGS-VRTD01 unixODBC-2.3.0]# make
[root@COENGS-VRTD01 unixODBC-2.3.0]# make install
[root@COENGS-VRTD01 unixODBC-2.3.0]# isql --version
unixODBC 2.3.0
[root@COENGS-VRTD01 unixODBC-2.3.0]#

Now we are ready to install Microsoft ODBC Driver 11 for SQL Server. Change your working directory into msodbcsql-11.0.2270.0 (where you extracted

Furthermore, before installing the Microsoft ODBC Driver 11 for SQL Server, we can perform dltest and it will succeed. However at this state, all is still not well.

[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# dltest /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 SQLGetInstalledDrivers
SUCCESS: Loaded /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
SUCCESS: Found SQLGetInstalledDrivers
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#  ./install.sh verify
Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft ODBC Driver 11 for SQL Server
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND
Install log created at /tmp/msodbcsql.15141.9396.20306/install.log.
One or more steps may have an *. See README for more information regarding
these steps.
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# cat /tmp/msodbcsql.15141.9396.20306/install.log
[Sun Apr 8 14:09:28 UTC 2018] Verifying on a 64 bit Linux compatible OS
[Sun Apr 8 14:09:28 UTC 2018] Checking that required libraries are installed
[Sun Apr 8 14:09:30 UTC 2018] Verifying if unixODBC is present
[Sun Apr 8 14:09:30 UTC 2018] Verifying that unixODBC is version 2.3.0
[Sun Apr 8 14:09:30 UTC 2018] Checking if Microsoft ODBC Driver 11 for SQL Server is already installed in unixODBC 2.3.0
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#

Let us re-install Microsoft ODBC Driver 11 for SQL Server with Force option. Force option will be required as sym-links for SQLCMD and BCP have already been created and install will try to recreate, fail and stop.

[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# ./install.sh install --accept-license
Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft ODBC Driver 11 for SQL Server
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND
Microsoft ODBC Driver 11 for SQL Server files copied ........................ OK
Symbolic links for bcp and sqlcmd created ............................... FAILED
Microsoft ODBC Driver 11 for SQL Server registered ............... NOT ATTEMPTED
See /tmp/msodbcsql.1763.10653.13214/install.log for more information about installation failures.
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# tail /tmp/msodbcsql.1763.10653.13214/install.log
scripts/script_feedBack.js
scripts/script_loc.js
scripts/script_manifold.js
scripts/SplitScreen.js
styles/Presentation.css
toc.htm
[Sun Apr 8 14:16:32 UTC 2018] Creating symbolic links
[Sun Apr 8 14:16:32 UTC 2018] Linking bcp to bcp-11.0.2270.0
ln: creating symbolic link `/usr/bin/bcp': File exists
[Sun Apr 8 14:16:32 UTC 2018] Failed to link bcp to bcp-11.0.2270.0.
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#

Re-installing with force option works, fine. However, before re-installing let try to configure odbc.

[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# cat /etc/odbcinst.ini
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# odbc_config --version --longodbcversion --cflags --ulen --libs --odbcinstini --odbcini
2.3.0
3.52
-DHAVE_UNISTD_H -DHAVE_PWD_H -DHAVE_SYS_TYPES_H -DHAVE_LONG_LONG -DSIZEOF_LONG_INT=8 -I/usr/include
-DSIZEOF_SQLULEN=8
-L/usr/lib64 -lodbc
/etc/odbcinst.ini
/etc/odbc.ini
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# odbcinst -j
unixODBC 2.3.0
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#  odbcinst -q -d -n "ODBC Driver 11 for SQL Server"
odbcinst: SQLGetPrivateProfileString failed with .
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# ./install.sh verify
Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft ODBC Driver 11 for SQL Server
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND
Install log created at /tmp/msodbcsql.16773.836.32407/install.log.
One or more steps may have an *. See README for more information regarding
these steps.
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#

With –force option:

[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# ./install.sh install --accept-license --force
Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft ODBC Driver 11 for SQL Server
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND
Microsoft ODBC Driver 11 for SQL Server files copied ........................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft ODBC Driver 11 for SQL Server registered ................... INSTALLED
Install log created at /tmp/msodbcsql.17079.11927.26282/install.log.
One or more steps may have an *. See README for more information regarding
these steps.
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#

Let us verify if ODBC driver manager and driver for MS SQL Server is installed properly.

[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]# cat  /etc/odbcinst.ini
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#  odbcinst -q -d -n 'ODBC Driver 11 for SQL Server'
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1
[root@COENGS-VRTD01 msodbcsql-11.0.2270.0]#

Everything looks good now. Let us try connecting to a SQL Server DB using SQLCMD.

[sbalas002c@COENGS-VRTD01 ~]$ sqlcmd -S COENGS-AQTPDB1 -U ********* -P *********
1> select name from sys.Databases;
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
ReportServer
ReportServerTempDB
LSAX1
SubscriberListETL
SSISDB
distribution
AQTProdV5
(11 rows affected)
1> quit
[sbalas002c@COENGS-VRTD01 ~]$

Now we can use shell scripts or python or any other tools from Linux environment to interact, extract update/insert data into MS SQL Server.

REFERENCES:

http://www.unixodbc.org/
https://www.microsoft.com/en-us/download/details.aspx?id=36437
http://lifeofageekadmin.com/install-microsoft-sql-drivers-unixodbc-2-3-0-rhel-6-64-bit/
https://gerardnico.com/db/oracle/odbc_linux
https://docs.snowflake.net/manuals/user-guide/odbc-linux.html
https://sourceforge.net/projects/unixodbc/files/unixODBC/2.3.0/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s