Test ODBC Connection To MSSQL Using unixODBC

In this article we will see how to test Linux ODBC connection to MSSQL using unixODBC.

Why ODBC Connection To MSSQL Is Needed?

To know more about ODBC, refer our previous blog post.
When we are using certain SAP products like HANA Smart Data Access, and get the error when configuring SDA, we need to test connection in Linux system by creating ODBC connection to MSSQL.

Steps:

• Create ODBC connection to MSSQL/SQL SERVER
• Test the Linux ODBC connection.

How To Install ODBC Connection To MSSQL

We can verify the installation by executing command odbcinst from HANA host.
we should be able to get list of commands as shown below:

We also need to Create .odbc.ini file as mentioned here.

Install SQL server ODBC Driver

For installtion of odbc driver for SQL server ,we could use the official Microsoft reference guide.

Commands for Suse Linux Enterprise server:

sudo su
curl -O https://packages.microsoft.com/keys/microsoft.asc
rpm --import microsoft.asc

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#SUSE Linux Enterprise Server 11 SP4
#Ensure SUSE Linux Enterprise 11 Security Module has been installed
zypper ar https://packages.microsoft.com/config/sles/11/prod.repo

#SUSE Linux Enterprise Server 12
zypper ar https://packages.microsoft.com/config/sles/12/prod.repo

#SUSE Linux Enterprise Server 15
zypper ar https://packages.microsoft.com/config/sles/15/prod.repo
#(Only for driver 17.3 and below)
SUSEConnect -p sle-module-legacy/15/x86_64

exit
sudo ACCEPT_EULA=Y zypper install -y msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y zypper install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo zypper install -y unixODBC-devel

SQL server driver should be visible as shown below:

Additionally we need to configure the DSN in the odbc.ini file.

Test Connection:

Test the ODBC connection by following command : 

"isql -v <DSN_Name> <SQLserver_username> <password>"