Connect to MS SQL Server from Linux Using ODBC Driver

In this article, we will look at how to connect to a Microsoft SQL Server database from a Linux host (Ubuntu in this example) using an ODBC driver.

First, add the public GPG key of the Microsoft repository:

$ curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

Then add the Microsoft repository:

$ curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

Refresh repository list:

$ sudo apt-get update

You are now ready to install the Microsoft ODBC 18 driver and the sqlcmd command:

$ sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
$ sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18

If you want to use integrated Windows authentication (using Kerberos), install the additional packages:

$ sudo apt-get install -y unixodbc-dev libgssapi-krb5-2

Add the path to the environment variable:

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

View current ODBC settings and connected drivers:

$ odbcinst -j
$ odbcinst -q -d

Now you can create a DSN entry that will be used to connect to the MSSQL server. Edit the file /etc/odbc.ini and add the following:

[MSSQLDB1]
Driver = ODBC Driver 18 for SQL Server
Server = tcp:192.168.158.100,1433
Encrypt = yes
TrustServerCertificate=yes
# You can set the default database
# Database = salarydb

$ nc -zv 192.168.158.100 1433

Use your DSN to check SQL Server connectivity:

$ isql -v MSSQLDB1 sa Passwordd!

To connect to MSSQL from sqlcmd, the following syntax is used

$ sqlcmd -S192.168.158.100 -U sa -C -d <db_name>

If your Linux host is joined to an AD domain (how to join Ubuntu/Debian to an Active Directory domain), you can use Kerberos authentication with current user credentials.

Add to the /etc/odbc.ini file:

Trusted_Connection=yes

To connect to SQL Server using the logged-in user’s credentials, use the command

$ sqlcmd -E -SMSSQLDB1L -C