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

Without it, an apt-get: There is no public key available error will occur when installing the package.

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

Use the netcat command to check that the SQL Server port you have specified is open in the Windows Defender firewall and that it is accessible from your Linux host:

$ 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