Installing and Using Microsoft SQL Server on Linux (Docker)

Pull the latest SQL Server 2017 image from Microsoft Container Registry:

 sudo docker pull mcr.microsoft.com/mssql/server:2017-latest

Run the container using the following command. Set the SA password to anything, being careful to follow the SQL Server default password policy. By default, this creates a container running Developer edition.

sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=AwesomePassword!' 
-p 1433:1433 --name sql1 
-d mcr.microsoft.com/mssql/server:2017-latest

Verify the container is running.

 sudo docker ps -a

Start an interactive bash shell inside the SQL Server container, using the name specified at creation.

sudo docker exec -it sql1 "bash"

Once inside, connect locally using SQLCMD. We must use the full path as SQLCMD is not in the path by default.

 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'AwesomePassword!'

Create a new database for our flagship application.

 CREATE DATABASE AwesomeCompany;
 go

Verify this database now exists.

 SELECT Name from sys.Databases;
 go

Create a login the application can use to connect.

 CREATE LOGIN AwesomeLogin WITH PASSWORD = 'AwesomePassword!';
 go

Create a user for the new database and associate it with the login.

 USE AwesomeCompany;
 go
 CREATE USER AwesomeUser FOR LOGIN AwesomeLogin;
 go

Give the new user permission to manage the database.

 exec sp_addrolemember 'db_owner', 'AwesomeUser';
 go

Exit out of SQLCMD. quit

Connect using the new login.

 /opt/mssql-tools/bin/sqlcmd -S localhost -U AwesomeLogin -P 'AwesomePassword!'

Create a table and insert values into the new database. USE AwesomeCompany; go CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT) INSERT INTO Inventory VALUES (1, 'widget', 100); go

Select data from the new table.

 SELECT * FROM Inventory;
 go