How to install Microsoft SQL server in Ubuntu

Its surprising but Microsoft is playing nice with the open source community now, they are making some of their software available on Linux. They started with .NET and now SQL, I guess their mantra now is “if you can’t beat them, join them. Anyway, in this tutorial I will show you step by step how you can install Microsoft SQL server in Ubuntu for testing purposes.

Prerequisite

  • You need at least 4GB of memory on the machine you want install SQL on
  • You need Ubuntu 16.04 or 16.10 ( haven’t tested it in any other distro )

Installing SQL

Make sure your Ubuntu machine is up to date:

sudo apt-get update && sudo apt-get upgrade && sudo apt-get dist-upgrade

then add the SQL repo:

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

Register the Microsoft Ubuntu repo:

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

Update the repo:

sudo apt-get update

Install SQL:

sudo apt-get install -y mssql-server

That should start the SQL installation:

image

As indicated by the installer, run the setup to finish the installation:

sudo /opt/mssql/bin/mssql-conf setup

Accept the license term ( hey! is Microsoft )

image

Enter your SA password:

image

That should finish the installation.

Open port 1433 in Ubuntu  firewall:

sudo ufw allow 1433

image

Connecting to the server using the command line

To connect to the server using the command line you need the mssql-tools package. To install the mssql-tools package in Ubuntu do the following:

In the command line type:

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

Register the repo with Microsoft:

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

Update the source, and install the package:

sudo apt-get update && sudo apt-get install mssql-tools unixodbc-dev

Add mssql-tools to bash path:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

 

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc

To connect to the database, type:

 sqlcmd -S localhost ( or the IP address of the server )  -U SA

It will prompt you for the SA password.

To create a database type:

CREATE DATABASE testdb;
 GO

To see the databases on the server type:

SELECT Name from sys.Databases;
GO

To end the session just type QUIT . As you can see the syntax is very similar to MYSQL.

Connecting to the server using the management studio console

To manage the server from a Windows 10 computer using the Microsoft Management Studio console, download the software from here and install it:

Microsoft SQL Server Management Studio Installation

The installation will take awhile depending on your computer speed:

Microsoft SQL Server Management Studio

Once the installation has finished, search for the Microsoft SQL Server Management Studio and open it, type the IP address  of the Ubuntu server where you installed the SQL server, and choose  SQL Server Authentication from the Authentication drop down. Type your SA login name and enter your password:

SQL Server console

You should be able to connect:

database list