Custom SQL container

# Exmple of creating a SQL Server 2019 container image that will run as a user 'mssql' instead of root

# This is example is based on the official image from Microsoft and effectively changes the user that SQL Server runs as

# and allows for dumps to generate as a non-root user

FROM mcr.microsoft.com/mssql/server:2019-latest

# Create non-root user and update permissions

# adds a new user named mssql to the existing SQL image. -M parameter will skip creating a home directory for the new user, -s specifies using the /bin/bash as the login shell for user, -u specifies the users UID value -1001, -g specifies the GID value - 0 , user is created inside the container and not in the host OS

# This statement can cause the image build process to fail with SQL 2019/2017 on LINUX/UBUNTU. Part of the SQL on LINUX installation process is the creation of a user and group named mssql. The UID and the username values must be unique. Since the mssql user already exists, even though with a different UID, the command fails. Adding a user that already exists will throw an error and cause image build process to fail.

# You can exclude this line when creating custom SQL 2019 on LINUX or SQL 2017 on UBUNTU. However, doing so would mean accepting the mssql user with a different UID value (999) that gets created during the installation process. This UID value may already exist on your LINUX docker host and can cause confusion as to who is really running the container.

# Another option is to name it something else but use the 10001 UID value

RUN useradd -M -s /bin/bash -u 10001 -g 0 mssql

# Creates a new directory named /var/opt/mssql with -m parameter assigning 770 permission - RWX for owner, RWX for group and none for others

# && appends the chgrp command

# Here it's the root user that implicitly creates the /var/opt/mssql directory. This means running the chgrp command is simply assigning the same group permissions from root. That's not going to work if we assign the msssql user to run the sqlservr process. Either we let the mssql user create the directory or explicitly change the ownership to the mssql user using the command chown -R mssql:0 /var/opt/mssql

# The below command might work because the image is built from an existing SQL Server on Linux and not from a OS - FROM mcr.microsoft.com/mssql/server:2019-latest

#It is not confirmed whether the script used to build the base image has already included making the mssql user as the owner of SQL server directories or not

RUN mkdir -p -m 770 /var/opt/mssql && chown -R mssql:0 /var/opt/mssql && chgrp -R 0 /var/opt/mssql

# Grant sql the permissions to connect to ports <1024 as a non-root user

# setcap is used to set Linux capabilities on a file, in this case the sqlservr executable. The capability cap_net_bind_service binds the sqlservr executable to a low-numbered port number (<1024) without running as root

# +ep means we are assigning the +operator Explicit and Permitted capabilities

# Linux capabilities are a security concept. They provide a process a subset of the available root privileges.

RUN setcap 'cap_net_bind_service+ep' /opt/mssql/bin/sqlservr

# Allow dumps from the non-root process

# set the cap_sys_ptrace capability to paldumper file (it's a SQL utility to generate core dumps, mainly for troubleshooting purpose. This has to be explicitly defined as we are no longer running the container as root, yet we need root privileges to perform troubleshooting

RUN setcap 'cap_sys_ptrace+ep' /opt/mssql/bin/paldumper

# gdb file, short for GNU debugger which is most common debugging utility for Linux

RUN setcap 'cap_sys_ptrace+ep' /usr/bin/gdb

# Add an ldconfig file because setcap causes the os to remove LD_LIBRARY_PATH

# and other env variables that control dynamic linking

# This instruction creates a ldconfig file using the touch command that SQL server will use - mssql.conf. The ldconfig command is used to create necessary links and cache for the shared libraries ( libraries that are loaded by programs when they start). The location on these shared libraries is stored in the env variable LD_LIBRARY_PATH.

RUN mkdir -p /etc/ld.so.conf.d && touch /etc/ld.so.conf.d/mssql.conf

# This instruction adds a new line- # mssql libs\n/opt/mssql/lib - to the file - /etc/ld.so.conf.d/mssql.conf - The mssql.conf is read to to search for the location of shared libraries, in this case- libs - and - /opt/mssql/lib - (\n is to introduce a new line) .

# The reason to use this statement is because the setcap commands used in earlier instructions remove the LD_LIBRARY_PATH and other environment variables that control dynamic linking. This is for security reasons. Since you will run the container with a non-root user, all unsecured environment variables will be removed, rendering running paldumper and gdbduring troubleshooting useless

RUN echo -e "# mssql libs\n/opt/mssql/lib" >> /etc/ld.so.conf.d/mssql.conf

# This instruction runs the ldconfig command to apply the config settings created in previous two instructions

RUN ldconfig

# This instruction sets the mssql as the user when running the container

USER mssql

CMD ["/opt/mssql/bin/sqlservr"]

=======================================================================================

Save the above file as dockerfile

Run the following command in the context of the dockerfile directory to build the non-root SQL Server container:

cd <path to dockerfile>

docker build -t 2017-latest-non-root .

Start the container.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<password>" --cap-add SYS_PTRACE --name sql1 -p 1433:1433 -d 2017-latest-non-root

Note:

The --cap-add SYS_PTRACE flag is required for non-root SQL Server containers to generate dumps for troubleshooting purposes.

Check that the container is running as non-root user:

docker exec -it sql1 bash

whoami

======================================================================================

To check the owner of the docker file system:

sudo ls -ld "$(docker container inspect [sqlcontainername] --format '{{.GraphDriver.Data.UpperDir}}')"

To check the process that runs sqlserver from Linux Docker Host:

ps aux | grep sqlservr

docker exec -it [sqlcontainername] ps aux | grep sqlservr

docker container inspect [sqlcontainername] --format '{{.Config.User}} {{.Name}}'