Installing MS SQL Server 2019 on Parrot OS
Tags: linux, database, sql, sqlserver
Published: 2021-06-22 11:13:55


## Introduction

Parrot OS is a light Linux distribution based on Debian. Microsoft SQL Server or SQL Server is a relational database management system (RDBMS) developed by Microsoft. Starting with SQL Server 2017, Microsoft added Linux support and thus making any newer versions cross platform.

This post steps through the process of SQL Server 2019 Developer edition installation on Parrot OS and contains the following topics:
- downloading and configuring MS SQL Server 2019 package repository
- running the SQL Server setup process
- installation of SQL Server command line tools
- installation Azure Data Studio
- running queries in Azure Data Studio

The commands used in this post will also work on any Debian based distribution like Ubuntu, MX Linux, Linux Mint, Knoppix among many others.

## Prerequisites

Parrot OS has very minimal memory requirements (256MB) and can be run directly from a USB flash drive. However, to run MS SQL Server 2019, Parrot OS must be installed on a hard disk. The computer running Parrot OS must also meet the following requirements:
- have minimum of 2 GB memory
- have 6 GB or more free hard disk space
- contain a x64 processor with 2 or more cores

## Configure SQL Server 2019 Repository
The repository is used to acquire the database engine package, mssql-server, and related SQL Server packages.

1. Open the Terminal window

1. Register Microsoft Ubuntu repository by running `curl https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list | sudo tee /etc/apt/sources.list.d/mssql-server-2019.list` command and type the password for the super user. `tee` command will write the downloaded file to _/etc/apt/sources.list.d_ folder.

1. Run `ls /etc/apt/sources.list.d` command and confirm that _mssql-server-2019.list_ file has been copied to  _/etc/apt/sources.list.d_ folder   folder and you should see _mssql-server-2019.list_ among the files

1. Run `sudo apt-get update` to update the system

1. If you  receive an error '**The following signatures couldn't be verified because the public key is not available: NO_PUBKEY EB3E94ADBE1229CF**', add the key to trusted keys using `gpg --keyserver keyserver.ubuntu.com --recv-keys EB3E94ADBE1229CF` and re-run `sudo apt-get update` command.

## Install SQL Server Developer Edition

1. Run `sudo apt-get install -y mssql-server` command to install SQL Server

1. After the package installation completes, run `sudo /opt/mssql/bin/mssql-conf setup`

1. Type **2** to select **Developer** edition and press the **return** key

1. Type **Y** to accept the license terms and press **return** key

1. Type and confirm the system administrator password and press **return** key. The password should have a minimum of eight characters

1. Once the configuration is complete, type `systemctl status mssql-server --no-pager` to verify that the service is up and running. `--no-pager` will force any long lines to wrap.


## Install SQL Server Command Line Tools

Command-line tools are used for running SQL commands from a Terminal window. Various command-line tools are available among them [**sqsh**](https://en.wikipedia.org/wiki/Sqsh) or **SQSHELL**, [**sqlcmd**](https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15) and [**mssql-cli**](https://github.com/dbcli/mssql-cli/).

**sqsh**

Parrot OS comes bundled with **sqsh** command line tool and therefore, no installation is required.

To use `sqsh`, open a Terminal window and run `sqsh -S localhost -U sa -C 'SELECT @@VERSION` command and type the SQL Server administrator password you used  during setup. This will display the SQL Server version and Linux OS Version and the Terminal prompt is re-displayed.

To login into **sqsh** interactive terminal, run `sqsh -S localhost -U sa`. You will be prompted for the password and if the connection is successful, you will get the `sqsh` command prompt '**>1**'. You exit from the `sqsh` interactive session by typing the command `exit` or quit` to end the session.

**sqlcmd**

The **sqlcmd** utility in SQL Server lets you submit T-SQL statements or batches to a local or remote instance of SQL Server. Before  using

1. Register Microsoft Ubuntu repository by running `curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list` command

1. Run `sudo apt-get update` and `sudo apt-get install mssql-tools unixodbc-dev` to update the sources list and installation commands.

1. Add _/opt/mssql-tools/bin/_ to your **PATH** environment variable by running `echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc` and `source ~/.bashrc` commands

1. To test connectivity, run `sqlcmd -S localhost -U sa -Q 'SELECT @@VERSION` and enter the password.

To open an **sqlcmd** interactive session, you use the same command just like **sqsh** command-line tool but replace `sqsh` with `sqlcmd` - `sqlcmd -S localhost -U sa`. You also use **exit** or **quit** to end the session.

While  **sqsh** and  **sqlcmd** may have similarities,  **sqlcmd** has an improved feedback and output format than **sqsh**.

**mssql-cli**

**mssql-cli** is another cross-platform open source interactive command line query tool for SQL Server. **Auto-completion**, **Syntax highlighting** and **Multi-line queries** are some of the features that make **mssql-cli** more appealing to use compared to **sqsh** and **sqlcmd**.

However, at the time of this post, there is no support for [Ubuntu 20.04 and Debian 10](https://github.com/dbcli/mssql-cli/pull/505) which Parrot OS 4.11 is based on.

## Install and Launch Azure Data Studio

![Alt "azure data studio overview screen"](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2uqwm6cit0ofn67vaoj8.jpeg)

Some people prefer working with GUI tools instead of command-line tools. If you have installed SQL Server Management Studio (SSMS),
you can connect to an MS SQL Server database installed on Linux through SSMS. As SSMS is only available in Windows operating systems, you can use [Azure Data Studio](https://github.com/Microsoft/azuredatastudio) instead of SSMS. Azure Data Studio is a free and cross-platform.

In the following sections, I step through the process of installing, connecting and running queries on Azure Data Studio.

1. [Download Azure Data Studio](https://go.microsoft.com/fwlink/?linkid=2163436)

1. To extract the file, run `sudo dpkg -i ./Downloads/azuredatastudio-linux-<version string>.deb`. Replace `<version string>` with the version of the downloaded file. The version I downloaded is **1.29.0** so the command to run will be `sudo dpkg -i ./Downloads/azuredatastudio-linux-1.29.0.deb`.

## Creating a Connection In Azure Data Studio
1. To Launch Azure Data Studio run `azuredatastudio` from a Terminal window. You can also launch by clicking on the **Application Launcher**, typing **Azure** in the search area and select **Azure Data Studio**.

2. Click on **Add Connection** on the left sidebar below **SERVERS**

3. Under **Connection Details**, fill in the fields as follows:
    - Server Name: **localhost** or just type a **.** (dot).
    - User name: **sa**
    - Password: <password-you-created-during-installation>
    - Name (optional): optionally type a preferred name or leave blank to use **localhost** as the connection name.
4. Click on **Connect** button

## Run Query In Azure Data Studio

In Azure Data Studio, commands are run through the query window.

1. Launch **Azure Data Studio** if not already launched

2. Right click on **localhost** or the name of the connection you created

3. Select **New Query**

4. Paste or type your query statements. For our testing, type `SELECT @@VERSION`, and

5. Click on  **Run**

## Summary

In this post we went through the process of installing and setting up MS SQL Server 2019 Developer Edition on Parrot OS. The same steps would also be applicable to on any Debian based Linux distribution. After setting up of SQL Server was complete, we looked at working with SQL Server using command-line tools and Azure Data Studio.

## Resources
- [Installation guidance for SQL Server on Linux](https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup?view=sql-server-ver15)
- [Quickstart: Install SQL Server and create a database on Ubuntu](https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-linux-ver15&preserve-view=true)
- [sqsh Man Page](http://manpages.ubuntu.com/manpages/focal/man1/sqsh.1.html)
- [What is Azure Data Studio?](https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is-azure-data-studio?view=sql-server-linux-ver15)
- [Quickstart: Use Azure Data Studio to connect and query SQL Server](https://docs.microsoft.com/en-us/sql/azure-data-studio/quickstart-sql-server?view=sql-server-linux-ver15)