Automating SQL Server 2019 Docker Deployments

In a previous article, I walked through deploying to SQL Server 2017 containers and provided an overview of deploying DACPACs and BACPACs in containers. Since that time, SQL Server 2019 has been released; this requires a few changes to the approach. In this article, we’ll look at what changes you need to make to deploy to a 2019 container.

Let’s start with the code, then examine what’s changed.

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

# Elevate to root to install required packages
USER root
RUN apt-get update 
    && apt-get install unzip libunwind8 libicu55 -y

# Install SQLPackage for Linux and make it executable
RUN wget -progress=bar:force -q -O sqlpackage.zip https://go.microsoft.com/fwlink/?linkid=2113331 
    && unzip -qq sqlpackage.zip -d /opt/sqlpackage 
    && chmod +x /opt/sqlpackage/sqlpackage 
    && chown -R mssql /opt/sqlpackage 
    && mkdir /tmp/db 
    && chown -R mssql /tmp/db

# Lower the privilege
USER mssql

# Add the DACPAC to the image
COPY site.bacpac /tmp/db/db.bacpac

# Configure external build arguments to allow configurability.
ARG DBNAME=Database
ARG PASSWORD

# Configure the required environmental variables
ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=$PASSWORD

# Launch SQL Server, confirm startup is complete, deploy the DACPAC, then terminate SQL Server.
# See https://stackoverflow.com/a/51589787/488695
RUN ( /opt/mssql/bin/sqlservr & ) | grep -q "Service Broker manager has started" 
    && /opt/sqlpackage/sqlpackage /a:Import /tsn:. /tdn:${DBNAME} /tu:sa /tp:$SA_PASSWORD /sf:/tmp/db/db.bacpac 
    && rm -r /tmp/db 
    && pkill sqlservr 
    && rm -r /opt/sqlpackage

To begin with, you will need to use a SQL Server 2019 image, such as 2019-latest. There is a complete list of images, which includes specific releases of SQL Server that you can use. Microsoft documents installing and connecting to SQL Server 2019 on Docker if you want to learn more about working with the containerized version of SQL Server. The Microsoft docs site also provides a guide to configuring Docker containers. The default Linux image is based on Ubuntu, but official RHEL images are also available.

Next, it’s important to know that the images are substantially more secure than the 2017 release. Instead of the images being built as root, a less privileged user called mssql is utilized. Because of this change, you need to explicitly switch users to install packages into the container via apt-get. This is done using Docker’s USER command. Switching to root is required when running apt-get to install packages, adjusting the ownership of folders, or performing other operations which required elevated privileges. In this case, the Dockerfile is installing dependencies that are required for sqlpackage.

Finally, the Dockerfile in this version changes the ownership of folders to enable automatically removing sqlpackage and the deployed BACPAC/DACPAC at the end of the process. To do that, we have to ensure the mssql user account is the owner of those folders. A natural question is why we can’t simply wait until after the last RUN to switch the user from root to mssql? Commands are executed in the context of the current user account. If we wait to switch back, we execute the deployment commands as the root user. As a result, any files created (such as the database files) are owned by that account. This would prevent the mssql account from being able to read and write those files. As a best practice, it’s important to observe least privileges. The code execute the commands in the lower-privileged mssql account, ensuring the proper permissions are automatically applied.

And that’s it. With these changes in place, we can use SQL Server 2019 containers and automatically deploy our database packages.