How to Install and Use Microsoft SQL Server on Debian 11
MSSQL Server or Microsoft SQL Server is an RDBMS (Relational Database Management System) developed by Microsoft. As a database server, it is used to store data for your applications. The SQL Server is ideal for multiple purposes of applications, you can use MS SQL Server as the database for your desktop applications, use it for your web applications, or any small server applications.
This tutorial will show you how to install and use Microsoft SQL database Server on Debian 11. First, you will set up the MSSQL Server repository and then install it. After, you will set up the UFW firewall and install MSSQL Tools that will be used for managing the MSSQL Server. Last, you will use the MSSQL Tools to connect to the MSSQL Server and learn the basic operations for CRUD (Create, Read, Update, and Delete) on MSSQL Server.
Prerequisites
To follow and complete this tutorial, you will need the following requirements:
- A Linux server running Debian 11 with min 4 GB of memory - This example uses a Debian server with hostname 'mssql-deb' and IP address '192.168.5.10'.
- A non-root user with sudo root/administrator privileges.
When these requirements are ready, you can start the MSSQL Server installation process.
Setting Up Repository
In this first step, you will set up the MSSQL Server repository on your Debian server. You will be setting up the MSSQL Server repository for the MSSQL Server 2019.
To begin, log in to your server as your user and execute the 'sudo su' command to get the root privileges. Then, enter the apt command to update and refresh your package index.
sudo su
sudo apt update
Now install some basic dependencies using the following 'apt install' command. Input y when prompted and press ENTER to proceed.
sudo apt install gnupg2 apt-transport-https wget curl
Next, use the following 'wget' command to download the GPG key of the Microsoft SQL Server repository. Then, convert the GPG file from .asc to .gpg using the 'gpg --dearmor' command below.
wget -q -O- https://packages.microsoft.com/keys/microsoft.asc | \
gpg --dearmor | sudo tee /usr/share/keyrings/microsoft.gpg > /dev/null 2>&1
Now enter the command below to add the MSSQL Server repository to your Debian system. Because Microsoft did not provide the MSSQL Server package for Debian distribution, you will be using the MSSQL Server package for Ubuntu.
echo "deb [signed-by=/usr/share/keyrings/microsoft.gpg arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/20.04/mssql-server-2019 focal main" | \
sudo tee /etc/apt/sources.list.d/mssql-server-2019.list
Lastly, enter the following 'apt' command to update and refresh your Debian package index and apply the changes.
sudo apt update
Now that you have added the MSSQL Server repository, you're ready to install and configure the MSSQL Server.
Installing MSSQL Server 2019
In this step, you will install the MSSQL Server 2019 package to your Debian system via the official MSSQL Server repository. Then, you will set up the installation by selecting the MSSQL Server type that you want to install, and also setting up the default password for the MSSQL Server authentication.
To install the MSSQL Server package, enter the following 'apt install' command. When prompted, input y to confirm and press ENTER to proceed.
sudo apt install mssql-server
After MSSQL Server is installed, you should receive an output like this - An instruction to complete the MSSQL Server installation.
Enter the following command to set up and configure your MSSQL Server installation.
sudo /opt/mssql/bin/mssql-conf setup
During the process, you will be asked about the following configurations:
- Select the MSSQL Server edition that you want to install. Input number 3 to install the Express MSSQL Server to your Debian system.
- Now input 'Yes' to confirm and accept the license terms.
- Lastly, input the new password for your MSSQL Server installation and repeat.
When the installation is successful, you should get a message such as 'Setup has been completed successfully. SQL Server is now running'.
Enter the following systemctl command utility to verify the MSSQL Server status and ensure that it's running.
sudo systemctl is-enabled mssql-server
sudo systemctl status mssql-server
An output 'enabled' confirms that the MSSQL Server will start automatically at system startup. And the status of the MSSQL Server is 'active (running)'.
In this step, you installed and configured the MSSQL Server. You have also verified that the MSSQL Server is running and enabled. In the next step, you will set up the UFW firewall.
Setting Up UFW
After installing and configuring MSSQL Server, you will now install UFW, open the OpenSSH application, then open the default port 1443 that is used by the MSSQL Server.
Install UFW using the following 'apt install' command. Input y when prompted and press ENTER to proceed.
sudo apt install ufw
Once ufw is installed, enter the following command to add the OpenSSH service and MSSQL Server port 1433/tcp to the ufw.
sudo ufw allow OpenSSH
sudo ufw allow 1433/tcp
Next, start and enable ufw using the following command. When prompted for confirmation, input y and press ENTER to proceed. With this, the ufw firewall should be running and enabled.
sudo ufw enable
To ensure that the ufw is running and the OpenSSH service and MSSQL Server port '1433/tcp' is added, enter the following command to check the status of ufw.
sudo ufw status
An output 'Status: active' confirms that the ufw is running. Also, you will see the OpenSSH service and the port 1433/tcp added and available on ufw.
Now that you've installed ufw and added OpenSSH service and MSSQL Server port '1433/tcp' to ufw. In the next step, you will install MSSQL Tools on your Debian system.
Installing MSSQL Tools
At this point, you have installed and secured the MSSQL Server on the Debian system. As for now, you will be installing the MSSQL Tools that will be used to connect and manage your MSSQL Server. The MSSQL Tools is available on different repositories, so you need to add the MSSQL Tools repository, then install it via APT.
Enter the following command to add the repository for the MSSQL Tools package.
echo "deb [signed-by=/usr/share/keyrings/microsoft.gpg arch=amd64,armhf,arm64] https://packages.microsoft.com/ubuntu/20.04/prod focal main" | \
sudo tee /etc/apt/sources.list.d/prod.list
After that, update and refresh your package index via the 'apt update' command below.
sudo apt update
Now enter the following 'apt install' command to install the MSSQL Tools package. Input y when prompted and press ENTER to proceed.
sudo apt install mssql-tools unixodbc-dev
When prompted for the confirmation of license terms, select Yes to accept and press ENTER to proceed.
Once the installation is finished, MSSQL Tools binary files will be available in the '/opt/mssql-tools/bin' directory. Enter the following command to verify the list of files on the '/opt/mssql-tools/bin' directory, and you should see two binary files 'bcp' and 'sqlcmd'.
ls /opt/mssql-tools/bin
With the default configuration, the MSSQL Tools can be executed only within the '/opt/mssql-tools/bin' directory. To solve this, you must add the '/opt/mssql-tools/bin' directory to the system PATH environment via the '~/.bashrc' configuration.
Enter the following command to add the '/opt/mssql-tools/bin' to the binary PATH environment variable.
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
Now reload the '~/.bashrc' config file and verify the PATH environment variable. When successful, you should see the '/opt/mssql-tools/bin' directory available on the list of binary PATH.
source ~/.bashrc
echo $PATH
Lastly, enter the following command to ensure that you can execute MSSQL Tools command utilities. And you should see the full path of both binary files 'sqlcmd' and 'bcp'.
which sqlcmd
which bcp
At this point, you have installed and configured the MSSQL Server. You have also secured your Debian server with UFW and installed MSSQL Tools. In the next step, you will learn how to connect to MSSQL Server using MSSQL Tools.
Connecting to MSSQL Server
In this section, you will be connecting to the MSSQL Server via the MSSQL Tools that you have installed. You will connect to MSSQL Server via the 'sqlcmd' utility to the default port 1443 of the MSSQL Server.
Log in to MSSQL Server using the following 'sqlcmd' command below. When prompted, input your MSSQL Server password, then press ENTER.
sqlcmd -S localhost -U SA -p
Once logged in, enter the following query to check the current version of the MSSQL Server. You should see the MSSQL Server 2019 Express Edition installed on the Linux system Debian 11.
select @@version
go
Lastly, enter the following query to check the list of default and available databases on the MSSQL Server. You should see databases master, tempdb, model, and msdb available as default databases on MSSQL Server.
select name from sys.databases;
go
Basic Operations of MSSQL Server
After connecting to the MSSQL Server, you will now learn the basic operation of the MSSQL Server. You will create a new user, and database, and insert and retrieve data, on the MSSQL Server via the 'sqlcmd' utility.
Enter the following query to create a new login on your MSSQL Server. In this example, you will create a new login called 'Bob', and be sure to change the default password in this query.
CREATE LOGIN Bob WITH PASSWORD='p4ssw0rdBob';
GO
Now create a new database 'TestDB' using the SQL query below.
CREATE DATABASE TestDB
GO
Next, enter the following queries to switch to the database 'TestDB' and create a new table called 'users'. Within the table 'users', you will create 5 different fields - id, first_name, last_name, email, and the last_login.
Use TestDB
GO
CREATE TABLE users (
id INT PRIMARY KEY IDENTITY (1, 1),
first_name VARCHAR (50) NOT NULL,
last_name varchar(50) NOT NULL,
email varchar(50),
last_login DATE NOT NULL
);
GO
Within the database 'TestDB', run the following query to create a new user called 'Bob' and specify the login for 'Bob'.
CREATE USER Bob FOR LOGIN Bob;
GO
When the user is created, enter the query below to grant basic CRUD operation (Create, Read, Update, and Delete) of the table 'users' to the user called 'Bob'.
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO Bob;
GO
Now type 'quit' to exit from the MSSQL Server shell.
quit
At this point, you have now created login, database, user, and table on the MSSQL Server.
To ensure that the new user is working, you will be logging into the MSSQL Server as the user 'Bob' to the specific database 'TestDB'. Then, you will insert new data to the table 'users'.
Enter the following 'sqlcmd' command to log in to the MSSQL Server with the user 'Bob' to the database 'TestDB'. When prompted for the password, input the password that you have created for the login called 'Bob'.
sqlcmd -S localhost -U Bob -d TestDB -p
Once logged in, run the following query to insert new data to the table 'users'. When the operation is successful, you should receive an output such as '(1 row affected)'.
INSERT INTO users (first_name, last_name, email, last_login) VALUES ('Bob', 'Marlyn', '[email protected]', '20220901');
GO
Now enter the following query to retrieve your data from the table 'users'. When successful, you should get your data printed out on your screen.
SELECT * FROM users
GO
SELECT Name from sys.Databases
GO
Conclusion
In this guide, you installed and configured MSSQL Server on the Debian 11 system. You also learned how to install and configure SQL Tools on Debian for managing MSSQL Server.
In the end, you also learned the basic usage of SQL Tools 'sqlcmd' for connecting to the SQL Server and learned the basic operation of SQL Server for creating MSSQL user, setup the database and table, and also basic CRUD (Create, Read Update, Delete) operations on MSSQL Server.
