Microsoft SQL Server: How Install Microsoft SQL Server on Ubuntu / RHEL / Centos / Docker

How Install Microsoft SQL Server on Ubuntu / RHEL / Centos / Docker

So that day have come now, MSFT has to port their software to Linux in order to stay relevant. MS SQL Server preview have come out and it is supported on Ubuntu, CentOS, RHEL  and Docker container. In this article, we are going to walk through how to install Microsoft SQL Server on linux platforms versions (CentOS / RHEL 7 and Ubuntu 16.04 LTS). The installation of the software is straightforward so let's start.

Installing MS SQL Server on CentOS or RHEL

As root, issue this command to add Microsoft repository

curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

After the repository have been added, log in as non-root user with sudo rights. I will do it like this, you change for your username:

su miki

Next we will install the MS SQL Server

sudo yum install mssql-server

After the yum have finished installing the package, we need to run the script which is similar to mysql_secure_install script.

sudo /opt/mssql/bin/sqlservr-setup

Complete the prompt like this:


If firewalld is not installed and enabled by default (in minimal install sometimes isn't) lets enable it:

sudo yum install firewalld

Enable it to start at boot

sudo systemctl enable firewalld

Start it for this session

sudo systemctl start firewalld

And add rules so SQL server can work

sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload

Check if SQL server is running

systemctl status mssql-server

Installing on Ubuntu

If you are using Ubuntu, here is how to install it. First lets enter superuser mode

sudo su

Lets add the key for the repository

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

Then add repository

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list > /etc/apt/sources.list.d/mssql-server.list

And update sources list

apt update

After this we can switch to non-root user either with su username or by exiting

exit

Next we install the MS SQL server by following command

miki@ubuntu-1:~$ sudo apt-get install -y mssql-server

Same as on centos, we need to run the script

sudo /opt/mssql/bin/sqlservr-setup

And check if it is running

systemctl status mssql-server

Docker image

A third way to use Microsoft SQL Server is with docker image. If you have working docker installation, you can run this on any Linux distribution. For instruction how to install docker on your distribution, you can visit official docker site.
When you have docker installed we can proceed to pulling the docker image

sudo docker pull microsoft/mssql-server-linux

We will need directory for persistent volume for the database

mkdir ~/mssql

This command will start the docker container with this image and with /home/miki/mssql as data dir. You change this path for your data dir

sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' -p 1433:1433 -v /home/miki/mssql:/var/opt/mssql -d microsoft/mssql-server-linux

Connecting to the MS SQL Server

In order to connect to the server, you need mssql tools which are not part of mssql server install. Here is how to install them

On Ubuntu

As super user add key for new repository (yes, it is another repo, not same as mssql)

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

Add repository

curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/msprod.list

Update sources list

sudo apt-get update

Log in as non-root

su miki

Install the tools

sudo apt-get install mssql-tools

On CentOS

As root run run this command to add repo:

sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

Switch to normal user

su miki

Install the tools

sudo yum install mssql-tools

On both CentOS and Ubuntu, you would need to accept EULA while install is in the process.
To connect the DB server, you run following command

sqlcmd -S localhost -U SA -P 'YourPassword'

Where YourPassword is the password you entered when you ran sqlservr-setup script. This should give you the mssql prompt

Working with SQL server

Now that we done with the install and have accessed the server, lets use it. For example, this commands will create database linoxide and change usage to it.

1> CREATE DATABASE linoxide;
2> GO
1> USE linoxide;
2> GO
Changed database context to 'linoxide'.
1>

In order to execute the command, after command you need to type GO as next line.  Showing all databases is done with following command

SELECT Name from sys.Databases;

Conclusion

We have successfully installed Microsoft SQL server on Ubuntu 16.04, RHEL and CentOS 7 and Docker container. In my opinion, MariaDB and PostgreSQL are still a better choices for Linux server, but if you just have to use MSFT software, now it is possible on Linux too.

Restrict Access To Linux Servers Using TCP Wrappers

TCP Wrapper is an open source host-based ACL (Access Control List) system, which is used to restrict the TCP network services based on the hostname, IP address, network address, and so on. It decides which host should be allowed to access a specific network service. TCP Wrapper was developed by a Dutch programmer and physicist Wietse Zweitze Venema in 1990 at the Eindhoven University of Technology. He maintained it until 1995, and then released it under BSD License in 2001. In this brief guide, I will explain how to restrict access to Linux servers using TCP Wrappers.

Please be aware that TCP Wrapper is not a complete replacement for properly configured firewall. It is just a valuable addition to enhance your Linux server’s security. It is recommended to use it in conjunction with a fully configured firewall and other security mechanisms and tools.

Install TCP Wrappers

TCP Wrappers is available in the official repositories of most Linux operating systems.

Depending upon the Linux distribution you use, It can be installed as shown below.

On YUM-based systems:

sudo yum install tcp_wrappers

On APT-based systems:

sudo apt-get install tcp_wrappers

On SUSE/openSUSE systems:

sudo zypper in tcp_wrappers

Restrict Access To Linux Servers Using TCP Wrappers

Configuration

TCP Wrappers implements the access control with the help of two configuration files: /etc/hosts.allow and /etc/hosts.deny. These two access control list files decides whether or not the specific clients are allowed to access your Linux server.

The /etc/hosts.allow file

This file contains the list of allowed or non-allowed hosts or networks. It means that we can both allow or deny connections to network services by defining access rules in this file.

The /etc/hosts.deny file

This file contains the list of hosts or networks that are not allowed to access your Linux server. The access rules in this file can also be set up in /etc/hosts.allow with a ‘deny’ option instead.

The typical syntax to define an access rule is:

daemon_list : client_list : option : option ...

Where,

• daemon_list – The name of a network service such as SSH, FTP, Portmap etc.
• clients_list – The comma separated list of valid hostnames, IP addresses or network addresses.
• options – An optional action that specifies something to be done whenever a rule is matched.

The syntax is same for both files.

Rules to remember

Before using TCP Wrappers, you need to know the following important rules. Please be mindful that the TCP Wrapper consults only these two files (hosts.allow and hosts.deny).

• The access rules in the /etc/hosts.allow file are applied first. They takes precedence over rules in /etc/hosts.deny file. Therefore, if access to a service is allowed in /etc/hosts.allow file, and a rule denying access to that same service in /etc/hosts.deny is ignored.
• Only one rule per service is allowed in both files (hosts.allow and hosts.deny).
• The order of the rules is very important. Only the first matching rule for a given service will be taken into account. This is same for both files.
• If there are no matching rules for a service in either files or if neither file exist, then access to the service will be granted to all remote hosts.
• Any changes in either files will come to effect immediately without restarting the network services.

The recommended approach to secure your server

Generally, the best practice to secure a Linux server is to block all incoming connections, and allow only a few specific hosts or networks. To do so, edit /etc/hosts.deny file:

sudo vi /etc/hosts.deny

Add the following line. This line refuses connections to ALL services and ALL networks.

ALL: ALL

Then, edit /etc/hosts.allow file:

sudo vi /etc/hosts.allow

and allow the specific hosts or networks of your choice.

sshd: 192.168.43.192 192.168.43.193

Also, you can specify valid hostnames instead of IP address as shown below.

sshd: server1.ostechnix.lan server2.ostechnx.lan

Alternatively, you can do the same by defining all rules (both allow and deny) in /etc/hosts.allow file itself.

Edit /etc/hosts.allow file and add the following lines.

sshd: 192.168.43.192 192.168.43.193
sshd: ALL: DENY

You don’t need to specify any rule in /etc/hosts.deny file.

As per above rule, all incoming connections will be denied for all hosts except the two hosts 192.168.43.192, 192.168.43.193.

Now, try to SSH to your Linux server from any hosts except the above hosts, you will get the following error.

ssh_exchange_identification: read: Connection reset by peer

You can verify this from your Linux server’s log files as shown below.

cat /var/log/secure

Sample output:

Jun 16 19:40:17 server sshd[15782]: refused connect from 192.168.43.150 (192.168.43.150)

Similarly, you can define rules for other services, say for example vsftpd, in /etc/hosts.allow file as shown below.

vsftpd: 192.168.43.192 
vsftpd: ALL: DENY

Again, you don’t need to define any rules in /etc/hosts.deny file. As per the above rule, a remote host with IP address 192.168.43.192 is allowed to access the Linux server via FTP. All other hosts will be denied.

Also, you can define the access rules in different formats in /etc/hosts.allow file as shown below.

sshd: 192.168.43.192    #Allow a single host for SSH service
sshd: 192.168.43.0/255.255.255.0  #Allow a /24 prefix for SSH
vsftpd: 192.168.43.192    #Allow a single host for FTP
vsftpd: 192.168.43.0/255.255.255.0         #Allow a /24 prefix for FTP
vsftpd: server1.ostechnix.lan                   #Allow a single host for FTP

Allow all hosts except a specific host

You can allow incoming connections from all hosts, but not from a specific host. Say for example, to allow incoming connections from all hosts in the 192.168.43 subnet, but not from the host 192.168.43.192, add the following line in /etc/hosts.allow file.

ALL: 192.168.43. EXCEPT 192.168.43.192

In the above case, you don’t need to add any rules in /etc/hosts.deny file.

Or you can specify the hostname instead of IP address as shown below.

ALL: .ostechnix.lan EXCEPT badhost.ostechnix.lan

For more details, refer the man pages.

man tcpd