In this guide, we are going to install Ms SQL Server 2019 on Ubuntu 22.04. As a bonus we are going to see how to usesqlcmdto connect to the server, create a database and run queries.
Microsoft SQL Server is a relational database management system developed by Microsoft. It is aimed to be used to store and retrieve data by applications. It can be run either on the same computer or on another across a network.
After the package installation finishes, run mssql-conf setup using its full path, and follow the prompts to set the SA password.
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).
$ sudo /opt/mssql/bin/mssql-conf setup
This is a preview version (free, no production use rights, 180-day limit starting Thu Aug 24 10:25:19 GMT 2023), continue? [Yes/No]:Yes
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from: https://aka.ms/useterms
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...
The licensing PID was successfully processed. The new edition is [Enterprise Evaluation Edition].
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 β /lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.
Once the configuration is done, verify that the service is running:
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!
Install the SQL Server command-line tools
To connect to the server, you need to have the client tools that will allow you to do so. In this section we will install SQL Server command-line tools: sqlcmd and bcp.
Import the public repository GPG keys.
1
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
Register the Microsoft Ubuntu repository.
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.
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:
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.BashCopy
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>. Let us confirm that everything works by checking the version
1
2
3
4
5
6
7
8
9
10
11
$ sqlcmd -S localhost -U SA -P 'gM3PL3wh2YNjBm7TDTcw'1> SELECT @@VERSION
2> GO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019(RTM-CU13)(KB5005679) - 15.0.4178.1 (X64) Sep 232021 16:47:49
Copyright (C)2019 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS) <X64>
(1 rows affected)
Create and query data
In this section, let us use sqlcmd to create a new database, add data, and run a simple query.
Create a new database
Use this Transact-SQL command in sqlcmd command prompt to create a test database called CitizixDB:
1
2
1>CREATEDATABASECitizixDB2>GO
Use this to query the name of all of the databases on your server