1. Overview
For many database administrators, Microsoft SQL Server (MSSQL) has been the go-to solution for managing relational databases in Microsoft Windows environments. However, MSSQL has opened its doors for Linux as well, enabling us to harness the power of this application within a Linux distribution.
In this tutorial, we’ll go through the step-by-step guide that we can follow to connect to MSSQL on Linux. Let’s note that we’ll perform this operation on Ubuntu. However, the same process applies to other distributions as well.
2. Installing MSSQL
To begin with, let’s navigate the process of installing MSSQL on Linux.
First, we use the wget command to fetch the public repository GPG keys:
$ wget https://packages.microsoft.com/keys/microsoft.asc
Consequently, a file containing the keys named microsoft.asc is downloaded to the working directory.
Next, let’s move the file to the /etc/apt/trusted.gpg.d/ directory, so we can add the new keys to the list of trusted keys:
$ sudo mv ./microsoft.asc /etc/apt/trusted.gpg.d/
Then, we make use of the add-apt-repository command to register the MSSQL Ubuntu repository:
$ sudo add-apt-repository "$(wget https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list)"
If we get the missing add-apt-repository error, we just install the software-properties-common package and rerun the last command:
$ sudo apt install software-properties-common
Once the repository registration is complete, let’s move on to the next step, where we install MSSQL on the machine:
$ sudo apt install -y mssql-server
The -y option, short for –yes, automatically inserts yes as an answer to all the prompts during the installation, ensuring a smooth MSSQL installation process.
After the installation is complete, let’s run another command in the terminal to finish setting up MSSQL on the system:
$ sudo /opt/mssql/bin/mssql-conf setup
We’re prompted to choose the MSSQL edition that we want to use. Here, we type 3 (Express) and hit Return to proceed:
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
Next, let’s type yes to accept the license terms:
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
Lastly, we enter a password to create an SA (System Administrator) account:
Enter the SQL Server system administrator password:
Upon hitting Return, the SA account is created and the setup is completed.
After that, let’s verify if the MSSQL service is running on the device:
$ systemctl status mssql-server --no-pager
If the MSSQL service is active on the machine, we get an output confirming that:
Active: active (running)
Now that MSSQL is available and running on the system, let’s move on to the next section, where we install some necessary MSSQL command-line tools on Linux.
3. Installing MSSQL Command-Line Tools
To create a database, we install some MSSQL command-line tools on the system for running T-SQL (Transact-SQL) statements in MSSQL. T-SQL is a query language that extends SQL, which Microsoft has specifically designed for MSSQL.
There are two tools that we need on the machine:
- mssql-tools
- unixodbc-dev
Before installing them, let’s run a couple of commands to register the Microsoft Ubuntu repository:
$ wget https://packages.microsoft.com/config/ubuntu/20.04/prod.list
$ sudo mv ./prod.list /etc/apt/sources.list.d/
Next, we install mssql-tools on the device:
$ sudo apt install mssql-tools
After that, we perform the installation of unixodbc-dev on the system:
$ sudo apt install unixodbc-dev
Then, let’s add /opt/mssql-tools/bin to the PATH environment variable:
$ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
At this point, we have the necessary MSSQL command-line tools available on the device. So, let’s move on to the final section, where we connect to MSSQL on Linux.
4. Connecting to MSSQL
In this section, we establish a connection to the new MSSQL deployment using the sqlcmd utility. This utility enables us to enter T-SQL statements, script files, and system procedures in MSSQL.
So, let’s use sqlcmd to connect with MSSQL:
$ sqlcmd -S localhost -U sa -P baeldung
There are three options in the command:
- -S: specifies the server name, i.e., localhost (since we’re connecting to MSSQL locally)
- -U: indicates the username of the SA account to be used, i.e., sa (provided automatically)
- -P: specifies the password of the SA account, i.e., baeldung (entered during the MSSQL setup process)
After running the command, a sqlcmd command prompt window shows up. This represents a new MSSQL instance, where we can start writing T-SQL statements to perform various database operations.
5. Conclusion
In this article, we had a detailed discussion about the process of connecting to MSSQL in Linux. We learned how to install and set up MSSQL, install necessary MSSQL command-line tools, and connect to a new MSSQL instance on the machine.
Whether we’re seasoned Linux administrators or simply exploring the capabilities of the system, we can follow the steps shown in this article to utilize the potential of MSSQL and unlock a world of possibilities for managing databases on Linux.