How To Install SQL Server 2019 Command Line Tools On Ubuntu 22.04

On this short article, we will show you how to install  command line tools for SQL Server 2019.

Introduction

When we want to use MS SQL Server 2019 as the database server of the application we are developing, of course we need a tool to be able to access the SQL Server database. Microsoft has created tools that can be used to connect from the client to the MS SQL Server database, called mssql-tools. To create a database, we need to connect with a tool that can run Transact-SQL statements on SQL Server.

The following steps install the SQL Server command-line tools: sqlcmd and bcp :

1. Import the public repository GPG keys
2. Register the Microsoft Ubuntu repository
3. Update the sources list and Install
4. Setting PATH environment variable
5. Testing Connect

The following is a detailed explanation of each items.

1. Import the Public Repository GPG keys

On this step we will import public repository GPG keys of Microsoft MS SQL server to our Ubuntu system.

$ sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

Output :

ramans@otodiginet:~$ sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 983 100 983 0 0 2421 0 --:--:-- --:--:-- --:--:-- 2421
OK

2. Register the Microsoft Ubuntu Repository

This step can be done by running the command line.

$ sudo curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Output :

ramans@otodiginet:~$ sudo curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 89 100 89 0 0 229 0 --:--:-- --:--:-- --:--:-- 229
deb [arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/20.04/prod focal main

$ sudo curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Output :

ramans@otodiginet:~$ sudo curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 89 100 89 0 0 229 0 --:--:-- --:--:-- --:--:-- 229
deb [arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/20.04/prod focal main

3. Update the sources list and Install

On this step, we will update the sources list and run the installation of MS SQL Server command line tools by executing following command line :

$ sudo apt-get update
$ sudo apt-get install mssql-tools unixodbc-dev

Output :

ramans@otodiginet:~$ sudo apt-get update
Hit:1 http://mirror.telkomuniversity.ac.id/ubuntu focal InRelease
Hit:2 http://mirror.telkomuniversity.ac.id/ubuntu focal-updates InRelease 
Hit:3 http://mirror.telkomuniversity.ac.id/ubuntu focal-backports InRelease 
Hit:4 http://mirror.telkomuniversity.ac.id/ubuntu focal-security InRelease 
Hit:5 https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal InRelease 
Hit:6 https://dl.yarnpkg.com/debian stable InRelease 
Hit:7 https://deb.nodesource.com/node_14.x focal InRelease 
Get:8 https://packages.microsoft.com/ubuntu/20.04/prod focal InRelease [10.5 kB] 
Hit:11 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu focal InRelease 
Hit:12 http://ppa.launchpad.net/teejee2008/ppa/ubuntu focal InRelease 
Get:13 https://packages.microsoft.com/ubuntu/20.04/prod focal/main armhf Packages [10.6 kB] 
Get:14 https://packages.microsoft.com/ubuntu/20.04/prod focal/main amd64 Packages [78.2 kB] 
Get:16 https://packages.microsoft.com/ubuntu/20.04/prod focal/main arm64 Packages [10.6 kB] 
Hit:17 http://ppa.launchpad.net/ubuntu-mozilla-daily/ppa/ubuntu focal InRelease 
Hit:9 https://downloads.apache.org/cassandra/debian 311x InRelease 
Hit:10 https://downloads.apache.org/cassandra/debian 40x InRelease 
Hit:15 https://packagecloud.io/sensu/stable/ubuntu focal InRelease
Fetched 110 kB in 3s (37.5 kB/s)
Reading package lists... Done

ramans@otodiginet:~$ sudo apt-get install mssql-tools unixodbc-dev
Reading package lists... Done
Building dependency tree 
Reading state information... Done
The following additional packages will be installed:
autoconf automake autotools-dev libltdl-dev libodbc1 libtool m4 msodbcsql17 odbcinst odbcinst1debian2
unixodbc
Suggested packages:
autoconf-archive gnu-standards autoconf-doc gettext libtool-doc unixodbc-bin gfortran
| fortran95-compiler gcj-jdk m4-doc
The following NEW packages will be installed:
autoconf automake autotools-dev libltdl-dev libodbc1 libtool m4 msodbcsql17 mssql-tools odbcinst
odbcinst1debian2 unixodbc unixodbc-dev
0 upgraded, 13 newly installed, 0 to remove and 149 not upgraded.
Need to get 3,075 kB of archives.
After this operation, 8,976 kB of additional disk space will be used.
Do you want to continue? [Y/n] Y
. . .
Setting up msodbcsql17 (17.7.2.1-1) ...
Setting up unixodbc-dev (2.3.7) ...
Setting up mssql-tools (17.7.1.1-1) ...
Processing triggers for install-info (6.7.0.dfsg.2-5) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.2) ...

For more convenience, we will update mssql-tool by submitting following command line :

$ sudo apt-get update
$ sudo apt-get install mssql-tools

Output :

ramans@otodiginet:~$ sudo apt-get update
Hit:1 http://mirror.telkomuniversity.ac.id/ubuntu focal InRelease
Hit:2 http://mirror.telkomuniversity.ac.id/ubuntu focal-updates InRelease 
Hit:3 http://mirror.telkomuniversity.ac.id/ubuntu focal-backports InRelease 
Hit:4 https://deb.nodesource.com/node_14.x focal InRelease 
Hit:5 https://dl.yarnpkg.com/debian stable InRelease 
Hit:6 http://mirror.telkomuniversity.ac.id/ubuntu focal-security InRelease 
Hit:7 https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal InRelease 
Hit:8 https://packages.microsoft.com/ubuntu/20.04/prod focal InRelease 
Hit:9 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu focal InRelease 
Hit:11 http://ppa.launchpad.net/teejee2008/ppa/ubuntu focal InRelease 
Hit:13 http://ppa.launchpad.net/ubuntu-mozilla-daily/ppa/ubuntu focal InRelease 
Hit:10 https://downloads.apache.org/cassandra/debian 40x InRelease 
Hit:14 https://downloads.apache.org/cassandra/debian 311x InRelease 
Hit:12 https://packagecloud.io/sensu/stable/ubuntu focal InRelease
Reading package lists... Done

ramans@otodiginet:~$ sudo apt-get install mssql-tools
Reading package lists... Done
Building dependency tree 
Reading state information... Done
mssql-tools is already the newest version (17.7.1.1-1).
0 upgraded, 0 newly installed, 0 to remove and 149 not upgraded

4. Setting PATH Environment Variable

On this stage, we will set path environment variable fo mssql-tools, by this setting we will make sqlcmd or bcp accessible from the bash shell. For this purpose we will perform the following tasks.

For interactive session

ramans@otodiginet:~$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

For non-interactive session

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

5. Testing Connect

After all are set, we will test mssql-tool by use it to connect to MS SQL Server database locally. To perform this task, we will use the following command line :

$ sqlcmd -S 127.0.0.1 -U SA

Output :

ramans@otodiginet:~$ sqlcmd -S 127.0.0.1 -U SA
Password:

1>

Select all datbase

1> select name from sys.databases;
2> go
name 
--------------------------------------------------------------------------------------------------------------------------------
master 
tempdb 
model 
msdb

(4 rows affected)

Create a new database

1> create database otodigi_db01;
2> go

Conclusion

On this short tutorial, we have learnt how to install Microsof SQL Server command line tools for accessing database. I hope this article will be helpful.