Linux Ubuntu | Debian

How to Install and Configure Microsoft SQL 2022 on Ubuntu 22.04 LTS

January 9, 2024
TTC

Welcome to totatca.com. In this article, we will explore together How to Install and Configure Microsoft SQL Server 2022 on Ubuntu 22.04 LTS.

Microsoft SQL Server on Ubuntu is an official version developed by Microsoft to run on the Linux operating system. This provides a higher level of choice and flexibility for organizations and developers looking to deploy SQL Server on non-Windows operating systems.

Here are some key points about SQL Server on Ubuntu:

  • Support for SQL Server Applications: SQL Server on Ubuntu supports a variety of SQL Server applications, including enterprise and web applications.
  • Integration with Linux Technologies: SQL Server is optimized to run on the Linux platform and integrates well with the features of this operating system.
  • Docker and Kubernetes Support: SQL Server on Ubuntu can be deployed in container environments using Docker and managed through Kubernetes.
  • Cross-Platform Management Tools: Microsoft provides SQL Server Management Studio (SSMS) for Linux, allowing administrators and developers to manage SQL Server databases without switching to a Windows environment.
  • Security and Compliance: SQL Server on Ubuntu adheres to high-security standards and complies with information security regulations.
  • Community and Documentation: With the growing Linux user community, there are many resources and forums supporting the deployment and management of SQL Server on Ubuntu.

Read more

Prerequisites

  • Operating system: An Ubuntu 22.04 LTS machine with at least 2 GB of memory
  • User privileges: root or non-root user with sudo privileges

Step 1 – Update system

Before we begin, please update your system using the following command.

sudo apt update

Step 2 – Install Microsoft SQL Server 2022 on Ubuntu 22.04 LTS

Run the command below to download the public key, convert it from ASCII to GPG format, and save it to the specified location:

curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg –dearmor -o /usr/share/keyrings/microsoft-prod.gpg

Download and register the SQL Server Ubuntu repository using the command below:

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

Run the command below to re-update repositories:

sudo apt update

Everything is ready, execute the command below to install Microsoft SQL Server:

sudo apt install mssql-server -y

After a successful installation, execute the command below to configure some options:

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

You need to answer some questions such as choosing the version you want to install, accepting license terms, and setting a password for the sa account as shown below (take note of the highlighted section):

hg@ttc06:~$ sudo /opt/mssql/bin/mssql-conf setup
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.
  9) Standard (Billed through Azure) - Use pay-as-you-go billing through Azure.
 10) Enterprise Core (Billed through Azure) - Use pay-as-you-go billing through Azure.

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.
By choosing an edition billed Pay-As-You-Go through Azure, you are verifying 
that the server and SQL Server will be connected to Azure by installing the 
management agent and Azure extension for SQL Server.

Enter your edition(1-10): 3     ### I installed the Express edition, so enter 3 and press Enter
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 Yes to accept the license terms

Enter the SQL Server system administrator password:    ### Set a Password for SA account
Confirm the SQL Server system administrator password:    ### Comfirm the Password for SA account
Configuring SQL Server...

The licensing PID was successfully processed. The new edition is [Express 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.
hg@ttc06:~$

Up to this point, the installation of MS SQL Server 2022 is complete. Execute the command below to start it:

sudo systemctl start mssql-server

If you want MS SQL Server 2022 to start automatically every time the system boots, execute the command below:

sudo systemctl enable mssql-server

And check its status

sudo systemctl status mssql-server

install-ms-sql-server-2022-on-ubuntu-22

Microsoft SQL Server listens on ports 1433 and 1434. You need to open these ports if you want to connect remotely to the Microsoft SQL server. Skip this step if you are not using the UFW firewall.

sudo ufw status

sudo ufw allow 1433

sudo ufw allow 1434

sudo ufw reload

sudo ufw status

install-ms-sql-server-2022-on-ubuntu-22

Step 3 – Install the SQL Server command-line tools sqlcmd

To use command-line tools in Microsoft SQL Server 2022 on Ubuntu, we need to install the Microsoft ODBC 18 package. Execute the command below to import the GPG key of the public repository.

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

And register the Microsoft Ubuntu repository:

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

Re-update the sources list

sudo apt update

Now, run the command below to install the unixODBC developer package:

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

The configuration window for msqlodbcsql18 appears, select Yes to accept the server terms and hit Enter

To continue to select Yes and hit Enter

install-ms-sql-server-2022-on-ubuntu-22

To use the sqlcmd and bcp commands accessible from the bash shell for any login version, we need to add /opt/mssql-tools18/bin/ to your shell’s PATH environment variable. Use the following commands:

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

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

source ~/.bashrc

Now, you can use the sqlcmd command. Run the following command to check the Microsoft SQL version installed on your Ubuntu:

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

Enter the password for the previously declared SA account. If successful, you will see a result similar to the image below:

Step 4 – Connect remotely to Microsoft SQL Server 2022 on Ubuntu from a Windows Client

First, download SQL Server Management Studio (SSMS) from here and install it on your Windows client.

Once the download is ready, open SSMS and configure the settings as follows:

  • Server type: Database Engine
  • Server name: IP-of-Ubuntu
  • Authentication: SQL Server Authentication
  • Login: sa
  • Password: Your password

Click on the Connect button. If successful, you will see a result similar to the one below:

install-ms-sql-server-2022-on-ubuntu-22

That’s all. Wishing you success!!!

Video

Leave a Reply

Your email address will not be published. Required fields are marked *