Written by 09:29 Tools & technologies

How to Install SQL Server Tools on Ubuntu 18.04 Linux System – Step by Step

How to Install SQL Server Tools on Ubuntu 18.04 Linux System – Step by Step

In the previous articles, we covered sequential steps necessary to build a virtual machine, install Ubuntu 18.04 operating system, and configure SQL Server 2019 on that Ubuntu machine. This is 3rd part of this series. Our goal is to demonstrate the step-by-step process of installing SQL Server Tools and its components on the Ubuntu 18.04 Linux system.

Install SQL Server Tools on Ubuntu Linux System

Before we proceed to explore the current topic, I would suggest you refer to the previous parts of the to refresh your knowledge:

Thus, I have already installed SQL Server 2019 on Ubuntu 18.04 and shown its connectivity through SQL Server Management Studio from a remote server.

If you want to connect to SQL Server instances locally on the Ubuntu Linux server, we first need to install the SQL Server command-line tools. These tools include sqlcmd and bcp utilities:

  • SQLCMD is a popular command-line utility that we use to establish SQL Server connections to execute queries.
  • BCP is one of the SQL Server utilities that serve to process the import and export of data.

The role of these SQL Server tools is crucial when if it comes to Linux-based SQL Server instances. They are very helpful for establishing the SQL Server connection or executing any query locally on the Linux server.

Install SQL Server Tools on Ubuntu Linux system

Steps to Install the SQL Server Command-line Tools

We have to perform the following steps to install these SQL Server command-line tools:

  • Register the Microsoft Ubuntu Repository
  • Install the SQL Server command-line tools
  • Configure the path environment variables in a bash shell

I would recommend you checking and validating the SQL Server service on this Linux machine whether our installed version of SQL Server is running fine or not.

Open the Ubuntu terminal screen and connect to the server. Run the below command to get the status of the running SQL Server instance:

--Check SQL Server Service Status
systemctl status mssql-server

See the output – our SQL Server is running fine on our target system:

status of the running SQL Server instance

Import the Public Repository GPG Key

To import the public repository GPG key, run the below commands:

--Import public repository GPG key 
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

As you can see, the import is successful. The status is OK:

Import the Public Repository GPG Key

Register the Microsoft Ubuntu Repository

We need to register the Microsoft Ubuntu repository for Ubuntu 18.04 version. The command is below:

--Register Microsoft Ubuntu repository for Ubuntu 18.04
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Note: If you are using a different version of Ubuntu, make sure to change the repository path. For instance, if you are using Ubuntu 20.04, change the repository path from /ubuntu/18.04 to /ubuntu/20.04.

See the output of the above command.

Register the Microsoft Ubuntu Repository

Now, we are updating the source list of packages by executing the below command:

--Update source list
sudo apt-get update
source list of packages

Once the updates are done, we proceed to the next step.

Install the SQL Server Command-line Tools

We will install the command-line tools along with the UnixODBC package – it is the SQL Server driver for Linux-based systems.

Execute the following command:

--Install SQL Server command line tools
sudo apt-get install mssql-tools unixodbc-dev

The screen will show all 37 packages to be installed. The system will check the required disk space for this installation and ask for your consent to proceed: enter Y for installation or enter n to abort the process.

I have selected Y to proceed and pressed Enter.

Install the SQL Server Command-line Tools

Once you have chosen to go ahead with this installation, the terminal screen will show the next series of execution.

series of execution

The next screen is named Package configuration. It will allow you to configure and accept the license terms for the Microsoft ODBC driver for Linux-based SQL Server.

Note: If you are facing any issues while installing this ODBC package, check and verify the existing version of your msodbcsql package. If you installed its version v17 that was briefly available, remove it before installing the msodbcsql17 package. This way, you will avoid conflicts.

As we can see, the package name is msodbcsql17. Now, we will accept the license terms of this package and proceed with this installation by choosing Yes and pressing Enter.

l accept the license terms of this package and proceed with this installation

Now, accept the license terms for mssql-tools package on the following screen. Again, select Yes and click Enter:

accept the license terms for mssql-tools package

The new screen will appear to execute other packages once you accept the license terms from the above screen.

execute other packages once you accept the license terms

Wait until the execution is completed.

You can see all packages in the process of setting up and configured in the below screen. The last few lines show that the mssql-tools package has been set up successfully:

mssql-tools package has been set up successfully

The mssql-tools package is configured. Now you can go ahead.

Configure the Path Environment Variables in a Bash Shell

Type SQLCMD to validate if Ubuntu can recognize this new utility. The thing is, we have already installed it as a part of mssql-tool package – SQL Server tools.

In our case, the Ubuntu system did not recognize SQLCMD.

To fix this issue, we’ll add /opt/mssql-tools/bin/ to our PATH environment variable in a bash shell by running the below commands:

--Run below command to use SQL Server Tools from the bash shell for login sessions
--Modify the PATH in the ~/.bash_profile file
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

--Run below command to use SQL Server Tools from the bash shell for interactive/non-login sessions
--Modify the PATH in the ~/.bashrc file
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Now, after executing the above two commands, Ubuntu has recognized SQLCMD.

Although, SQLCMD is still throwing an error. That is because we have not passed the full command line.

SQLCMD is throwing an error

I have used the full command line using SQLCMD. As you can see, the database connection is established successfully. I have passed the Ubuntu server IP address along with the login name “sa”. You can use your login name and password:

--Establish a database connection using SQLCMD
--Replace your server IP after -S parameter
--Replace your login name after -U parameter
--Enter your login password once prompt will ask you to enter
Sqlcmd -S192.168.6.131 -Usa

I have also executed a basic SQL query to check the installed version of SQL Server. It shows Microsoft SQL Server 2019.

basic SQL query to check the installed version of SQL Server

Now, we have successfully installed SQL Server Tools. We can run queries and database activities.

Additional Information

The approach we explored in this article is the online installation where your Ubuntu system is connected to the Internet. If your Linux system is not connected to the Internet or does not have access to online repositories, you need to include more steps.

First, download the package from the Microsoft repository and copy that package to the target server. Then you can install it there.

Make sure to download and copy the dependency packages – the msodbcsql package, which is a dependency. This package also has a dependency on the unixodbc-dev package for Ubuntu.

Copy all these downloaded packages to the target Ubuntu server and then run the below commands to install these packages:

--Run below commands to install SQL Server Tools in offline mode
sudo dpkg -i msodbcsql_<version>.deb
sudo dpkg -i mssql-tools_<version>.deb

We hope that this article will help you in your work. If you’d like to share some practical tips from experience, feel free to add them in the Comments section below.

Tags: , , Last modified: September 16, 2021
Close