Installing MSSQL on Ubuntu running on Elastic Compute Service instance

This article covers the installation and configuration of a Microsoft SQL server instance running on Ubuntu 16.04 LTS. I will be using an Elastic Compute Service (ECS) from Alibaba Cloud service provider as an example.

1) Spin up an ECS server instance for MSSQL

To install MSSQL server on a Ubuntu 16.04 (Xenial Xerus) you will need a server instance that meets the minimum requirements. Below table shows the minimum requirements needed for an ECS server instance to host an MSSQL database server. (source)

Memory2 GB
CPU1 Core (x64 bit)
Disk Memory6 GB
Linux OSAt least Ubuntu 16.04

After provisioning your machine, remote into your ECS instance using an SSH compatible client (e.g.: Putty.exe). When logged in, check the details of your instance using the following command:

lsb_release -a

2) Install and configure MSSQL server

Download MSSQL server installation files using Ubuntu repository. You may need to install common Ubuntu tools before you can use add-apt-repository.

# additional tools might be needed
sudo apt-get install software-properties-common

# update repository and pull MSSQL from repository
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"
sudo apt-get update
If you encounter the error ‘sudo: unable to resolve host ‘ , check the file “/etc/hostname” for the host file DNS entries. (source)

After successfully installing the SQL server, configure the server using the following commands. Follow this up by running a few commands to check the configuration.

# This will launch a prompt to setup SA user.
sudo apt-get install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup

# Open port 1433 for remote access (optional)
sudo iptables -A INPUT -p tcp --dport 1433 -s xxx.xxx.xxx.xxx -j ACCEPT
# to delete iptable entry, replace -I with -D

# check that 1433 is opened and MSSQL is listening on that port
lsof -i -P | grep :1433
netstat -an | grep :1433

# Check that SQL server
systemctl status mssql-server

3) Install MSSQL command CLI

Install the MSSQL CLI so that we can proceed to creating a SQL user.

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)"
sudo apt-get update
sudo apt-get install -y mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

# Check that the CLI tools are installed, bcp and sqlcmd should exist in
ls /opt/mssql-tools/bin/

4) Create SQL user (Optional)

sqlcmd -S localhost -U SA -P 'password_here'
CREATE LOGIN login_name WITH PASSWORD = 'new_password_here';
GO
CREATE USER db_user_name FOR LOGIN login_name;
GO
GRANT ALTER TO db_user_name;
GO
GRANT CONTROL TO db_user_name;
GO

source – install MSSQL on Ubuntu