SQL Server - Setting Up Linux ODBC

Install the Microsoft Linux ODBC Driver

First, follow 

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017#microsoft-odbc-driver-131-for-sql-server

to install the driver

========= Below is the snippet for RHEL ================

sudo su

#Download appropriate package for the OS version

#Choose only ONE of the following, corresponding to your OS version

#Red Hat Enterprise Server 6

curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo

#Red Hat Enterprise Server 7 and Oracle Linux 7

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

#Red Hat Enterprise Server 8 and Oracle Linux 8

curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo

exit

sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts

sudo ACCEPT_EULA=Y yum install -y msodbcsql17

# optional: for bcp and sqlcmd

sudo ACCEPT_EULA=Y yum install -y mssql-tools

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

source ~/.bashrc

# optional: for unixODBC development headers

sudo yum install -y unixODBC-devel

======================================================================

Install SQL Server Management Tools

And following the on screen instruction to complete the installation. It requires server restart at the end

Setup SQL Server Authentication User

After installing SQL Server Management Tools, you can create a SQL Server Authentication user

Right click on Security -> Login, then New Login..

Create the user as needed

Run SQLCMD To Test Remote Connection

You can run the following to test remote connectivity

bash-4.2$ sqlcmd -S mysqlserver -U testuser

Password:

1> select DB_NAME()

2> go

--------------------------------------------------------------------------------------------------------------------------------

master

(1 rows affected)

1> select name from sys.databases

2> go

name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

ReportServer

ReportServerTempDB

testdb

(7 rows affected)

1> quit

Set up ODBC file

Setting in odbc file

We need to define the DSN. To do so, we should have below lines in /etc/odbc.ini file:

bash-4.2$ cat /etc/odbc.ini

[mysqlserver]

Description=Microsoft ODBC Driver 17 for SQL Server

Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.7.so.2.1

Threading = 1

server=mysqlserver

host=mysqlserver_host

Port=1433

Use isql To Test ODBC Connection

bash-4.2$ isql mysqlserver testuser testuser

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL> select name from sys.databases

+---------------------------------------------------------------------------------------------------------------------------------+

| name                                                                                                                            |

+---------------------------------------------------------------------------------------------------------------------------------+

| master                                                                                                                          |

| tempdb                                                                                                                          |

| model                                                                                                                           |

| msdb                                                                                                                            |

| ReportServer                                                                                                                    |

| ReportServerTempDB                                                                                                              |

| testdb                                                                                                                          |

+---------------------------------------------------------------------------------------------------------------------------------+

SQLRowCount returns 0

7 rows fetched

SQL>