Docker-izing SQL Server Express in a Windows Container

In this post, I will show you how to install the SQL Server Express database server inside of a Windows Docker container. In order to proceed with this tutorial, it is assumed that you have the following pre-requisites:

• You are either running Windows Containers on Windows 10 Anniversary Edition or Windows Containers on Windows Server 2016
• You have installed Docker for Windows
• You have enabled the Containers Windows feature
• You have installed SQL Server Management Studio (SSMS)

In this walkthrough, I am using Windows Containers on Windows 10 (Anniversary Edition).

Installing SQL Server Express Docker Container

Right-click on the whale docker icon in the tray and make sure it is in Windows Container mode. This is the case if it displays “Switch to Linux containers…” as shown below:




Open a command prompt and enter the following command:

docker run -d -p 1433:1433 --env sa_password=Sql!Expre55 --env ACCEPT_EULA=Y microsoft/mssql-server-windows-express

The switches used with the above docker command are explained below:

-p	maps the container port number 1433 to the host computer’s port number. Needless to say, port 1433 is default for SQL Server
-d	instructs docker to run in detached mode in the background
--env	allows you to pass an environment variables into the container. We have two environment variables – namely: sa_password and ACCEPT_EULA. The password entered above is Sql!Expre55. Feel free to change the password to whatever you want. Just make sure that the password complies to the Microsoft SQL Server Strong Password Requirements. Failure to do so will result in “Invalid Password” errors when you attempt to login.

The container that we are downloading from Docker Hub is named microsoft/mssql-server-windows-express and is substantial in size. Be patient as it may take some time to download.
You will know that it is completed when you are returned to the terminal prompt. At this point, you can access and use the database server in three ways:

1. You can use sqlcmd in windows authentication mode inside the container
2. You can use sqlcmd in SQL Server authentication mode inside the container
3. You can access the Docker-zed SQL Server database externally. We will use SQL Server Management Studio (SSMS) to access the database externally.

Using sqlcmd in windows authentication mode inside the container

Before we can access the database, we need to determine the container_id. This is done by typing the following docker command:

docker ps

This displays information about the running container:

CONTAINER ID        IMAGE                                    COMMAND                  CREATED             STATUS              PORTS                    NAMES
a66847266360        microsoft/mssql-server-windows-express   "cmd /S /C 'powers..."   8 minutes ago       Up 8 minutes        0.0.0.0:1433->1433/tcp   jolly_shaw

It suffices to grab the first three characters of the container_id. In the above example, this is a66. The next step is enter a sqlcmd prompt inside the container using windows authentication. This is done with the following docker command that uses the correct container_id:

docker exec -it a66 sqlcmd

The –i switch is for interactive and –t is for pseudo-TTY.

You will see a window that looks like this:









Enter the following pair of commands to display existing databases:

EXEC sp_databases
go

The output will look like this:



Type exit to close the sqlcmd terminal window.

Using sqlcmd in SQL Server authentication mode inside the container

Using the same container_id as before, type in the following docker command to get into a sqlcmd prompt:

docker exec -it a66 sqlcmd -S. –Usa

The –S switch points to the current server name and –U is for username.
When prompted for the password, enter the password that you entered when the container was first created. In the above example it is Sql!Expre55.



You will see a very similar prompt as in the previous example when Windows authentication was used.

Using SQL Server Management Studio to access the database externally

We will need the IP address of our SQL Server Docker container if we are to access it externally. You can obtain the IP address by using the “docker inspect” command with the appropriate container_id. In my case, I entered the following command:

docker inspect a66

This displays configuration data regarding the SQL Server Express container:



Grab the IP address from the Networks >> nat section as shown above. In my case, the container’s IP address is 172.27.237.190.



If all is well, you will be connected to the database.

Cleanup

To stop the container, you can enter the following docker command with the appropriate container_id:

docker rm -f a66

If you want to permanently delete the SQL Server Express image then you must determine the image_id first with this command:

docker images

Once you determine the image_id, the following docker command will permanently delete the image from your computer:

docker rmi d5c

References:
https://hub.docker.com/r/microsoft/mssql-server-windows-express/