SQL Server 2019 on Docker no longer runs as root by default

In my home lab I have an Ubuntu virtual machine that runs both SQL Server 2017 and SQL Server 2019 in Docker containers.

After SQL Server 2019 Release Candidate 1 was released, when I performed my usual migration to get the latest version, I noticed that the command line for the SQL Server instance was different.

I used sudo docker ps -a --no-trunc to see the full command, which is emulated below (note: this output is heavily abbreviated).

CONTAINER ID   IMAGE                                        COMMAND
57e9c7ac2470   mcr.microsoft.com/mssql/server:2019-latest   "/opt/mssql/bin/permissions_check.sh /opt/mssql/bin/sqlservr"
deea050363b4   mcr.microsoft.com/mssql/server:2017-latest   "/opt/mssql/bin/sqlservr"

For reference, SQL Server 2017 on Docker ran as the root user (similar to Local Administrator on Windows Server). With SQL Server 2019, it no longer runs as root by default, but if you have performed an upgrade to 2019, your data files may have been created as the root user, so SQL Server has to run elevated to start correctly; this is performed by a script called permission_check.sh.

My friend Anthony Nocentino [ blog | Twitter ] reminded me of the command to view the full logs when a container starts up.

sudo docker logs SQL150 | head

Note how it says this container is running as root because the underlying master database file is owned by root.

SQL Server 2019 will run as non-root by default.
This container is running as user root.
Your master database file is owned by root.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
This is an evaluation version.  There are [167] days left in the evaluation period.
2019-09-02 21:34:25.59 Server      Microsoft SQL Server 2019 (RC1) - 15.0.1900.25 (X64)
    Aug 16 2019 14:20:53
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Linux (Ubuntu 16.04.6 LTS) 
2019-09-02 21:34:25.60 Server      UTC adjustment: 0:00

That “learn more” link will redirect us to https://docs.microsoft.com/sql/linux/quickstart-install-connect-docker.

You can view the contents of the permission_check.sh file below, or share your thoughts in the comments.

Photo by Sergi Kabrera on Unsplash.

#!/bin/bash

username=$(whoami)
message="SQL Server 2019 will run as non-root by default.\nThis container is running as user $username."

# Find the master database file
master_path=""
mssql_conf="/opt/mssql/bin/mssql-conf"

# Check for master.mdf using environment settings
if [ -n "$MSSQL_MASTER_DATA_FILE" ] && [ -f "$MSSQL_MASTER_DATA_FILE" ]
then
    master_path="$MSSQL_MASTER_DATA_FILE"
elif [ -n "$MSSQL_DATA_DIR" ] && [ -f "$MSSQL_DATA_DIR/master.mdf" ]
then
    # trim any trailing slashes from the path
    trimmed_dir=$(echo "$MSSQL_DATA_DIR" | sed 's:/*$::')
    if [ -f "$trimmed_dir/master.mdf" ]
    then
        master_path="$trimmed_dir/master.mdf"
    fi
fi

# If not found, check mssql.conf for location
if [ -z "$master_path" ] && [ -f /var/opt/mssql/mssql.conf ]
then
    # check for master data file
    master_data_file=$($mssql_conf get filelocation masterdatafile | cut -d ':' -f 2 | tr -d ' ')
    if [ -f "$master_data_file" ]
    then
        master_path="$master_data_file"
    else
        # check for default data dir
        default_data_dir=$($mssql_conf get filelocation defaultdatadir | cut -d ':' -f 2 | tr -d ' ')
        trimmed_dir=$(echo "$default_data_dir" | sed 's:/*$::')
        if [ -f "$trimmed_dir/master.mdf" ]
        then
            master_path="$trimmed_dir/master.mdf"
        fi
    fi
fi

# If not found, check /var/opt/mssql
if [ -f "/var/opt/mssql/data/master.mdf" ] && [ -z "$master_path" ]
then
    master_path="/var/opt/mssql/data/master.mdf"
fi

if [ -n "$master_path" ] && [ -f "$master_path" ]
then
    master_mdf_owner=$(stat -c '%U' "$master_path")
    message="$message\nYour master database file is owned by $master_mdf_owner."
fi

message="$message\nTo learn more visit https://go.microsoft.com/fwlink/?linkid=2099216."
echo -e "$message"

# Execute the cmd from the dockerfile