In this post, we will do a walk through example of installing and configuring unixODBC and the SQL Server on Linux drivers as well as the HammerDB connection options to enable HammerDB on Linux to be able to connect to SQL Server. In the example, we have SQL Server running on Windows and are using a virtualized Linux instance to run HammerDB to connect to SQL Server.
unixODBC
Firstly get unixODBC from here http://www.unixodbc.org/ and configure making sure the that the –enable-fastvalidate option is given. In this example, we install in the /usr/local directory using the command make and make install after configure.
./configure --prefix=/usr/local/unixODBC --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-threads=yes --enable-fastvalidate
Microsoft ODBC Drivers
Then install the Microsoft ODBC Drivers using the instructions here. Once installed, we will see the drivers successfully installed and find the dependent libraries we need.
root@REDPOLL:/opt/microsoft# ls
msodbcsql17 msodbcsql18
root@REDPOLL:/opt/microsoft# ls msodbcsql18/lib64/
libmsodbcsql-18.1.so.2.1
Add the drivers to /usr/local/odbcinst.ini either using the obcinst command or through manual editing of odbcinst.ini.
root@REDPOLL:/usr/local/unixODBC/etc# more odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.2.1
UsageCount=1
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1
UsageCount=1
We can verify the configuration and drivers as follows:
root@REDPOLL:/usr/local/unixODBC# ./bin/odbcinst -j
unixODBC 2.3.11
DRIVERS............: /usr/local/unixODBC/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/unixODBC/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/unixODBC/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/unixODBC/etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
root@REDPOLL:/usr/local/unixODBC# ./bin/odbcinst -d -q
[ODBC Driver 17 for SQL Server]
[ODBC Driver 18 for SQL Server]
As our hammerdb user we can then set our .bash_profile and ensure that the ODBC drivers are found.
hammerdb@REDPOLL:~$ more .bash_profile
export PATH="$PATH:/opt/mssql-tools/bin"
export PATH=:/usr/local/unixODBC/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/unixODBC/lib:$LD_LIBRARY_PATH
export ODBCSYSINI=/usr/local/unixODBC/etc
export ODBCINI=/usr/local/unixODBC/etc/odbc.ini
hammerdb@REDPOLL:~$ odbcinst -d -q
[ODBC Driver 17 for SQL Server]
[ODBC Driver 18 for SQL Server]
Test Connectivity
In this example, as we are running HammerDB for Linux in a VM to connect back to the Windows host we need to either configure or temporarily disable Defender Firewall to allow HammerDB to connect to SQL Server.
And also enable remote connections to SQL Server from SSMS.
We also need to use SQL Server Authentication, and set a password for the sa user.
And configure HammerDB to connect from Linux to Windows, and therefore we set the TCP check option as well as using the SQL Server authentication with the user and password we set previously.
Build Schema
We can now run HammerDB to verify that we can connect to SQL Server from our Linux client.
Run Test
We the build is complete, we can begin running a test from our SQL Server on Linux client.
And confirm that our Linux host has connected and is running the workload on SQL Server.
Summary
In this post, we have shown how to configure the ODBC Drivers for SQL Server on Linux for HammerDB. When the test complete, do not forget to re-enable Windows Firewall if you disabled it.