How to Install Microsoft SQL on Linux

In this article, we will be discussing how to install Microsoft SQL or MSSQL on Linux. Microsoft SQL, colloquially referred to as MSSQL, is a relational database management system created by Microsoft. Open-source MySQL and PostgreSQL are typically synonymous with Linux distributions, but working with MSSQL on Linux is also supported. MSSQL offers some features that its open-source counterparts don’t, and depending on application requirements, it might be the right choice for an RDBMS. In this tutorial, we are going to walk through how to install MSSQL on CentOS 7 and Ubuntu 16.04.

Pre-flight Check

• You will need to verify your server has at least 2GB of memory
• These instructions are being performed on CentOS 7 and Ubuntu 16.04 LTS servers respectively as the root user

CentOS 7

Step 1: Add MSSQL 2019 Preview Repo

First, as a best practice, ensure all packages are up to date:

root@centos ~]# yum update -y

Next, we need to tell the package manager yum where to look for the mssql-server package by adding the appropriate repo:

root@centos ~]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-preview.repo

Step 2: Install SQL Server

Now that yum is aware of the MSSQL repo, we can use yum to install the package:

root@centos ~]# yum install -y mssql-server

Step 3: Configure MSSQL Server

Next, we need to configure SQL with a system administrator password and confirm the edition we want to use. This tutorial will use the Developer edition, choice 2, as it is free:

root@centos ~]# /opt/mssql/bin/mssql-conf setup
usermod: no changes
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.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&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.

Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409

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...

This is an evaluation version.  There are [116] days left in the evaluation period.
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

After that, we need to verify that the mssql service is running:

root@centos ~]# systemctl status mssql-server

The output should look something like this:

mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2019-10-23 20:18:03 EDT; 2min 45s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 61529 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           ├─61529 /opt/mssql/bin/sqlservr
           └─61549 /opt/mssql/bin/sqlservr

Step 4 (Optional): Allow Remote Connections

If you want your SQL server to be accessible remotely, you will have to open up the SQL Server port:

Note: Proceed with caution. Firewalls are in place to keep your server safe by limiting access to it. Unless you plan to access SQL Server remotely, it is unnecessary to open this port.

root@centos ~]# firewall-cmd --zone=public --add-port=1433/tcp --permanent

After adding the rule, we need to reload our firewall rules and verify the port is open:

[root@centos ~]# firewall-cmd --reload
success
root@centos ~]# firewall-cmd --list-ports
1433/tcp

Step 5: Add Microsoft Red Hat repository

Now, we need a way to interact with our SQL server. First, let’s add another repo so we can use yum to install SQL Server command-line tools

root@centos ~]# curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

Step 6: Install and setup MSSQL Server command-line tools

Now that yum is aware of the packages we want to install, we need to install them. It’s important to note, during the installation of these packages, there will be a couple of interactive prompts to accept the license terms:

root@centos ~]# yum install -y mssql-tools unixODBC-devel

For ease of use we can add the path

/opt/mssql-tools/bin/ 

to the PATH variable on the server so that we can execute sql commands from any location on the server:

root@centos ~]# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

root@centos ~]# source ~/.bashrc

The final step is to verify that we can make a connection to SQL Server:

root@centos ~]# sqlcmd -S localhost -U SA
Password:
1>

Ubuntu 18.04 LTS

Step 1: Add MSSQL Server Ubuntu 2019 preview repo

First, let’s update the server packages:

root@ubuntu1604:~# apt-get update -y

Once the server packages are updated, we need to add the GPG keys for the repository we want to add. GPG keys are a way for Linux users to verify the validity of files and confirm they come from trusted sources:

t@ubuntu1604:~# wget -qO- https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

Now that the GPG keys are in place, we can add the repository:

root@ubuntu1604:~# add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-preview.list)"

The repository we just added requires an HTTPS connection. To ensure that apt can connect to the repo, we need to be certain it can connect over https:

root@ubuntu1604:~# apt-get install -y apt-transport-https

Step 2: Install MSSQL Server

Now that the repo containing the MSSQL Server packages is available, all that’s left to do is make sure apt knows about the new repo and install MSSQL Server:

apt-get update -y
apt-get install -y mssql-server

Step 3: Configure MSSQL Server

The configuration step is the same on both CentOS 7 and Ubuntu 16.04. During the configuration process, there will be interactive prompts to pick the SQL Server edition, accept license terms, and enter in a SQL Admin password:

root@ubuntu1604:~# /opt/mssql/bin/mssql-conf setup
usermod: no changes
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.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&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.

Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409

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...

This is an evaluation version.  There are [116] days left in the evaluation period.
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

MSSQL Server should now be running and enabled. In order to verify that this is, in fact the case, we can run this command:

root@ubuntu1604:~# systemctl status mssql-server --no-pager
* mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2019-10-24 00:24:23 EDT; 3min 45s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 19446 (sqlservr)
    Tasks: 135
   Memory: 548.5M
      CPU: 12.499s
   CGroup: /system.slice/mssql-server.service
           |-19446 /opt/mssql/bin/sqlservr
           `-19485 /opt/mssql/bin/sqlservr

Step 4 (Optional): Allow Remote Connections

If you intend to utilize a remote connection to your new SQL Server, it will be necessary to open up the SQL Server port:

Note: Again proceed with caution. Firewalls are in place to keep your server safe by limiting access to it. Unless you plan to access SQL Server remotely, it is unnecessary to open this port.

To keep our firewall interactions succinct, install ufw, otherwise known as Uncomplicated Firewall:

root@ubuntu1604:~# apt-get install -y ufw

Once installed, ufw has to be enabled. You will see a warning indicating that your SSH connection might be interrupted. If your SSH session is disconnected, log back in and continue:

root@ubuntu1604:~# ufw enable
Command may disrupt existing ssh connections. Proceed with operation (y|n)? y
firewall is active and enabled on system startup

Now that ufw is in place and enabled, it’s time to allow traffic through to port 1433:

root@ubuntu1604:~# ufw allow 1433
Rule added
Rule added (v6)

Step 5: Install and setup MSSQL Server command-line tools

First, as we did before, we need to add some new GPG keys for the repo that contains the MSSQL command line tools:

root@ubuntu1604:~# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

Now we can add the repository:

root@ubuntu1604:~# curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | tee /etc/apt/sources.list.d/msprod.list

After that, update apt and install the command line tools:

root@ubuntu1604:~# apt-get update -y
t@ubuntu1604:~# apt-get install -y mssql-tools unixodbc-dev

There should be one or two interactive prompts to accept licenses during installation that look something like this:

Let’s make it easy to execute sqlcmd anywhere on the server:

root@ubuntu1604:~# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
root@ubuntu1604:~# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
root@ubuntu1604:~# source ~/.bashrc

Finally, it’s time to verify that we can connect to MSSQL Server locally:

root@ubuntu1604:~# sqlcmd -S localhost -U SA
Password:
1>

Get Started Today!

Do you need to set up an alternate database system like MSSQL on Linux? Need assistance in configuring an existing database or troubleshooting a related got you frustrated? We have some of the best minds in the industry working for Liquid Web and we are standing by 24 hours a day, 365 days a year just waiting to prove it! We can step in at any point to provide the assistance you need to move the issues along.