DEV Community

Kagunda JM
Kagunda JM

Posted on • Edited on • Originally published at kags.me.ke

Installing MS SQL Server 2019 on Parrot OS

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

  2. 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.

  3. 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

  4. Run sudo apt-get update to update the system

  5. 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

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

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

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

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

  6. 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 or SQSHELL, sqlcmd and 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

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

  3. 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

  4. 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 which Parrot OS 4.11 is based on.

Install and Launch Azure Data Studio

Alt "azure data studio overview screen"

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 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

  2. 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:
    • 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

Top comments (0)