Install MS SQL Server Client on Linux (Vertica nodes)

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!!!

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…. . 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@CENTOS65
sbalas002c@CentOS65's password:
Connected to CENTOS65.
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.

Install

[root@CENTOS65 ~]# 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@CENTOS65 ~]
# cd /tmp/
[root@CENTOS65 tmp]
# tar -xz -f msodbcsql-11.0.2270.0.tar.gz
[root@CENTOS65 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@CENTOS65 tmp]
# cd msodbcsql-11.0.2270.0
[root@CENTOS65 msodbcsql-11.0.2270.0]
# ls bin  build_dm.sh  docs  include  install.sh  lib64  LICENSE  README  WARNING
[root@CENTOS65 msodbcsql-11.0.2270.0]
#
[root@CENTOS65 msodbcsql-11.0.2270.0]
#
[root@CENTOS65 msodbcsql-11.0.2270.0]
# ./install.sh install --accept-license --force 1> install_std.log 2> install_err.log
[root@CENTOS65 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@CENTOS65 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@CENTOS65 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@CENTOS65 msodbcsql-11.0.2270.0]# dltest /opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0 SQLGetInstalledDrivers

[dltest]
ERROR dlopen: file not found
[root@CENTOS65 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@CENTOS65 ~]# 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@CENTOS65 ~]
# isql --version unixODBC 2.2.14
[root@CENTOS65 ~]#

You can remove the unixODBC driver manager using yum remove.

Remove unixODBC Expand source

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.Download unixODBC 2.3.0

$ sftp sbalas002c@CENTOS65
sbalas002c@CentOS65's password:
Connected to CENTOS65.
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.

Configure and install unixODBC

[root@CENTOS65 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@CENTOS65 tmp]# tar -xz -f unixODBC-2.3.0.tar.gz
[root@CENTOS65 tmp]# cd unixODBC-2.3.0
[root@CENTOS65 unixODBC-2.3.0]#
[root@CENTOS65 unixODBC-2.3.0]# CPPFLAGS="-DSIZEOF_LONG_INT=8"
[root@CENTOS65 unixODBC-2.3.0]# export CPPFLAGS
[root@CENTOS65 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@CENTOS65 unixODBC-2.3.0]# make
[root@CENTOS65 unixODBC-2.3.0]# make install
[root@CENTOS65 unixODBC-2.3.0]# isql --version unixODBC 2.3.0
[root@CENTOS65 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@CENTOS65 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@CENTOS65 msodbcsql-11.0.2270.0]#
[root@CENTOS65 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@CENTOS65 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@CENTOS65 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@CENTOS65 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@CENTOS65 msodbcsql-11.0.2270.0]
#
[root@CENTOS65 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@CENTOS65 msodbcsql-11.0.2270.0]
#

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

[root@CENTOS65 msodbcsql-11.0.2270.0]# cat /etc/odbcinst.ini

[root@CENTOS65 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@CENTOS65 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@CENTOS65 msodbcsql-11.0.2270.0]
#  odbcinst -q -d -n "ODBC Driver 11 for SQL Server" odbcinst: SQLGetPrivateProfileString failed with .
[root@CENTOS65 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@CENTOS65 msodbcsql-11.0.2270.0]
#
With --force option:
[root@CENTOS65 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@CENTOS65 msodbcsql-11.0.2270.0]
#
Let us verify if ODBC driver manager and driver for MS SQL Server is installed properly.
[root@CENTOS65 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@CENTOS65 msodbcsql-11.0.2270.0]
#
[root@CENTOS65 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@CENTOS65 msodbcsql-11.0.2270.0]
#

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

[sbalas002c@CENTOS65 ~]$ 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@CENTOS65 ~]$

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