How to Install Microsoft SQL Server 2019 on Ubuntu 20.04 (Step-by-Step)

Microsoft SQL Server is a relational database management system from Microsoft. SQL Server 2019 runs on Linux and is available in free editions (Developer, Express) and paid editions (Standard, Enterprise). This guide walks you through installing Microsoft SQL Server 2019 on Ubuntu 20.04: adding the Microsoft repository, running mssql-conf setup to set the SA password and edition, installing the mssql-tools (including sqlcmd and bcp), and connecting locally to create a database and run Transact-SQL queries.

In this guide you’ll:

Add the Microsoft SQL Server 2019 repository and install the mssql-server package on Ubuntu 20.04
Configure SQL Server with mssql-conf setup (SA password, edition) and enable the mssql-server systemd service
Install mssql-tools (sqlcmd, bcp) and add them to your PATH
Connect with sqlcmd and run basic T-SQL (create database, table, insert, select)

Related guides:

Prerequisites

Updated ubuntu 20.04 server
Access to the commandline as root user

Ensure that the server is up to date

Before proceeding, it is always a good practice to make sure that the server we are working on has updated packages. Use this command to ensure that the server is updated

1
2
sudo apt update
sudo apt upgrade -y

Ensure that some necessary packages are installed

1
sudo apt install -y wget curl

Installing MsSQL Server 2019 in Ubuntu

MsSQL Server is not available in the default Ubuntu repos. To set up the repo and install ubuntu, follow these steps:

Import the public repository GPG keys:

1
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

1
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"

Run the following commands to update the new repo and install SQL Server:

1
2
sudo apt-get update
sudo apt-get install -y mssql-server

Configuring MsSQL Server

After the package installation finishes, run mssql-conf setup and follow the prompts to set the SA password and choose your edition.

1
sudo /opt/mssql/bin/mssql-conf setup

Make sure to specify a strong password for the SA account (Minimum length 8 characters, including uppercase and lowercase letters, base 10 digits and/or non-alphanumeric symbols).

This is the output on my server

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
$ sudo /opt/mssql/bin/mssql-conf setup
sudo: unable to resolve host ubuntusrv.citizix.com: No address associated with hostname
Choose an edition of SQL Server:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID) - CPU Core utilization restricted to 20 physical/40 hyperthreaded
  7) Enterprise Core (PAID) - CPU Core utilization up to Operating System Maximum
  8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=2109348&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=2104294&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:Yes

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...


ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service &#x2192; /lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

Starting and enabling the service

Once the configuration is done, verify that the service is running:

1
sudo systemctl status mssql-server --no-pager

This is the output on my server

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ sudo systemctl status mssql-server --no-pager
sudo: unable to resolve host ubuntusrv.citizix.com: No address associated with hostname
 mssql-server.service - Microsoft SQL Server Database Engine
     Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2021-11-24 09:45:21 UTC; 1min 29s ago
       Docs: https://docs.microsoft.com/en-us/sql/linux
   Main PID: 21584 (sqlservr)
      Tasks: 136
     Memory: 592.6M
     CGroup: /system.slice/mssql-server.service
             &#x251C;&#x2500;21584 /opt/mssql/bin/sqlservr
             &#x2514;&#x2500;21615 /opt/mssql/bin/sqlservr

Nov 24 09:45:25 ubuntusrv.citizix.com sqlservr[21615]: [78B blob data]
Nov 24 09:45:25 ubuntusrv.citizix.com sqlservr[21615]: [84B blob data]
Nov 24 09:45:25 ubuntusrv.citizix.com sqlservr[21615]: [145B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [96B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [66B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [75B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [96B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [100B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [71B blob data]
Nov 24 09:45:26 ubuntusrv.citizix.com sqlservr[21615]: [124B blob data]

If you plan to connect remotely, you might also need to open the SQL Server TCP port (default 1433) on your firewall.

At this point, SQL Server 2019 is running on your Ubuntu machine and is ready to use!

If you want to enable the service to start on boot use this command

1
sudo systemctl enable mssql-server

Install the SQL Server command-line tools

To create a database and run queries, you need a tool that can execute Transact-SQL (T-SQL) against SQL Server. The following steps install the official command-line tools: sqlcmd and bcp.

Use the following steps to install the mssql-tools on Ubuntu.

Import the public repository GPG keys.

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

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

Update the sources list and run the installation command with the unixODBC developer package.

1
2
sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.

To make sqlcmd/bcp accessible from the bash shell for login sessions, modify your PATH in the ~/.bash_profile file with the following command:

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

To make sqlcmd/bcp accessible from the bash shell for interactive/non-login sessions, modify the PATH in the ~/.bashrc file with the following command:

1
2
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Connecting locally

Upon successful installation, we can connect to the database server and perform some operations

Run sqlcmd with parameters for your SQL Server name (-S), the user name (-U), and the password (-P). In this tutorial, you are connecting locally, so the server name is localhost. The user name is SA and the password is the one you provided for the SA account during setup.

1
sqlcmd -S localhost -U SA -P '<YourPassword>'

You can omit the password on the command line to be prompted to enter it.

If you later decide to connect remotely, specify the machine name or IP address for the -S parameter, and make sure port 1433 is open on your firewall.

If successful, you should get to a sqlcmd command prompt: 1>.

1
2
$ sqlcmd -S 127.0.0.1 -U SA -P 'Sup4Srcr7Pa$$';
1>

Executing basic sql queries

In this section, we will explore using sqlcmd to create a new database, add data, and run a simple query.

Note: You must type GO on a new line to execute the commands.

To create a new database, use this:

1
2
1> CREATE DATABASE CitizixDB
2> GO

To get names of all the databases:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
1> SELECT Name from sys.Databases
2> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
CitizixDB

(5 rows affected)

Let us create a new table, Users, and insert new rows.

Switch context to the new CitizixDB database:

1
2
3
1> use CitizixDB
2> GO
Changed database context to 'CitizixDB'.

Create new table named Users:

1
2
1> CREATE TABLE Users (id INT, name NVARCHAR(50), age INT)
2> GO

Insert data into the new table:

1
2
3
4
5
6
1> INSERT INTO Users VALUES (1, 'etowett', 72); INSERT INTO Users VALUES (2, 'kchep', 36);
2> GO

(1 rows affected)

(1 rows affected)

Select data from our database table

1
2
3
4
5
6
7
1> SELECT * FROM Users WHERE age > 50;
2> GO
id          name                                               age
----------- -------------------------------------------------- -----------
          1 etowett                                                     72

(1 rows affected)

To end your sqlcmd session, type QUIT:

1
1> QUIT

Frequently Asked Questions (FAQ)

What is Microsoft SQL Server 2019?

Microsoft SQL Server 2019 is the current long-term release of SQL Server that runs on Linux (including Ubuntu), Windows, and in Docker. It includes the database engine, T-SQL, and support for free editions (Developer, Express) and paid editions (Standard, Enterprise). On Ubuntu you install the mssql-server package from Microsoft’s repository.

What port does SQL Server use?

SQL Server listens on TCP port 1433 by default. For remote connections, open port 1433 in your firewall (e.g. sudo ufw allow 1433/tcp). Local connections use localhost or 127.0.0.1 as the server name in sqlcmd (-S localhost).

How do I connect to SQL Server from the command line on Ubuntu?

Install mssql-tools (which includes sqlcmd), add /opt/mssql-tools/bin to your PATH, then run: sqlcmd -S localhost -U SA -P 'YourPassword'. You can omit -P to be prompted for the password. Use the SA account and password you set during mssql-conf setup.

What is the SA account?

SA (system administrator) is the default SQL Server admin login. You set the SA password when you run sudo /opt/mssql/bin/mssql-conf setup. The password must be at least 8 characters and include uppercase, lowercase, digits, and/or non-alphanumeric symbols.

Can I run SQL Server 2019 in Docker instead?

Yes. For a containerized setup without installing on the host, see How to run MS SQL Server 2019 with Docker and Docker Compose.

Conclusion

You now have Microsoft SQL Server 2019 installed on Ubuntu 20.04: the mssql-server service is running and enabled, and you can connect with sqlcmd to create databases and run T-SQL. For the same setup on CentOS/Rocky Linux, see how to install and set up MS SQL Server on CentOS 8; for Docker, see MS SQL Server 2019 with Docker Compose.