How to Install and Configure MS SQL Server 2022 on Ubuntu 22.04 LTS

In this tutorial, we will show you how to install Microsoft SQL Server on Ubuntu 22.04 LTS. For those of you who didn’t know, Microsoft SQL Server is a relational database management system developed by Microsoft. It is a highly popular database system that provides an efficient and secure way to store and retrieve data. SQL Server is used by businesses of all sizes to manage and store data for applications, websites, and other systems. While it is commonly used on Windows servers, SQL Server can also be installed on Ubuntu, a popular Linux distribution.

This article assumes you have at least basic knowledge of Linux, know how to use the shell, and most importantly, you host your site on your own VPS. The installation is quite simple and assumes you are running in the root account, if not you may need to add ‘sudo‘ to the commands to get root privileges. I will show you the step-by-step installation of the Microsoft SQL Server database on Ubuntu 22.04 (Jammy Jellyfish). You can follow the same instructions for Ubuntu 22.04 and any other Debian-based distribution like Linux Mint, Elementary OS, Pop!_OS, and more as well.

Step 1: Prepare

Update system

# apt update

Install curl if not already installed

# apt install curl

Step 2: Install MS SQL Server 2022 on Ubuntu 22.04 LTS

Download and Import the public Repository GPG security key

# curl https://packages.microsoft.com/keys/microsoft.asc | tee /etc/apt/trusted.gpg.d/microsoft.asc

Register the SQL Server 2022 on the Ubuntu repository

# curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list | tee /etc/apt/sources.list.d/mssql-server-2022.list

Re-update repositories

# apt update

Install MS SQL Server

# apt install mssql-server -y

Set up MS SQL Server

/opt/mssql/bin/mssql-conf setup

Choose the version you want to install. In this example, I enter 3(Express)

Enter “Yes” to accept the license terms

Enter the password for the Administrator account

Start and Enable MS SQL service

# systemctl start mssql-server

# systemctl enable mssql-server

Check the status of MS SQL to ensure it’s running

# systemctl status mssql-server

Step 3: Install the MS SQL Server Command-line Tools sqlcmd

Import the public repository GPG keys

# curl https://packages.microsoft.com/keys/microsoft.asc | tee /etc/apt/trusted.gpg.d/microsoft.asc

Register the Microsoft Ubuntu repository

# curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | tee /etc/apt/sources.list.d/mssql-release.list

Re-update repositories

# apt update

Install the MS SQL Server command with the unixODBC developer package

# apt-get install mssql-tools18 unixodbc-dev -y

Then,click Yes -> Yes to continue

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

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

# echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc

source ~/.bashrc

Test the SQL Command-line with a command to check the version of MS SQL

# sqlcmd -S localhost

# sqlcmd -S localhost -U SA -C -Q 'select @@VERSION'

Step 4: Firewall configuration

MS SQL Server listens on ports 1433 and 1434 . If you need to connect remotely to your Microsoft SQL Server,you need to open these two ports

# ufw allow 1433
# ufw allow 1434
# ufw reload
# ufw status

Step 5: Connect To SQL Server 2022

To verify your SQL server is working correctly, you can connect to it locally by using the folwloing comamnd:

# sqlcmd -S localhost -U sa -P '<YourPassword>'

Note: If the connection doesn’t succeed, you can add the -No option to sqlcmd to specify that encryption is optional, not mandatory.

# sqlcmd -S localhost -U sa -N -P '<YourPassword>' 

Once you lanched your shell, you can check your system databases with the following commands:

1> select name,database_id from sys.databases; 
2> go 

To end your session, you can use:

exit

Conclusion

Remember, while SQL Server runs on Linux, there are differences in file paths, scripts, and tools compared to Windows. Always refer to the Linux-specific documentation for MS SQL Server for guidance. Hope you enjoy it.

Good Luck!