Upgrading SQL Server 2017 Containers to 2019 non-root Containers with Data Volumes

Recently Microsoft released a Non-Root SQL Server 2019 container and that’s the default if you’re pulling a new container image. But what if you’re using a 2017 container running as root and to upgrade your system the SQL Server 2019 Container…we’ll something’s going to break. As you can see here, my friend Grant Fritchey came across this issue recently and asked for some help on Twitter’s #sqlhelp.

What’s the issue?

When you start up the 2017 container, the SQL Server (sqlservr) process is running as root (uid 0). Any files created by this process will have the user and group ownership of the root user. Now when we come along later and start up a 2019 container, the sqlservr process is running as the user msssql (uid 10001 by default). This new user doesn’t have permission to open the database files and other files used by SQL Server.

How do we fix this?

The way I fixed this issue is by stopping the SQL Server 2017 container and using another container, attaching the data volumes used by the 2017 container into this container then recursively adjusting the permissions to allow a user with the uid 10001 access to the files in the instance directory /var/opt/mssql. If you’re databases and log files are in other paths you’ll have to take that into account if using this process. Once we adjust the permissions, stop that ubuntu container and start up SQL Server’s 2019 non-root container and everything should be happy happy. Let’s do it together…

Start Up a Container with a Data Volume

Start up a container with a Data Volume (sqldata1) using the 2017 image. This will create the files with root as the owner and group.

docker run 
    --name 'sql1' 
    -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD 
    -p 1433:1433 
    -v sqldata1:/var/opt/mssql 
    -d mcr.microsoft.com/mssql/server:2017-latest
597652b61b22b27ff6d765b48196621a79dd2ffd7798328868d2296c7e953950

Create a Database

Let’s create a database and confirm it’s there.

sqlcmd -S localhost,1433 -U sa -Q 'CREATE DATABASE TestDB1' -P $PASSWORD
sqlcmd -S localhost,1433 -U sa -Q 'SELECT name from sys.databases' -P $PASSWORD -W
name
----
master
tempdb
model
msdb
TestDB1
(5 rows affected)

Stop our Container

Now to start the process of upgrading from 2017 to 2019, we’ll stop and remove the existing container.

docker stop sql1
docker rm sql1
sql1

Start a 2019 non-root Container

Create a new container pointing to that existing Data Volume (sqldata1), this time I’m not using -d so we can attach to stdout and see the error messages on the terminal. Here you can see that the sqlservr process is unable to open a file instance_id.

docker run 
    --name 'sql1' 
    -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD 
    -p 1433:1433 
    -v sqldata1:/var/opt/mssql 
     mcr.microsoft.com/mssql/server:2019-GDR1-ubuntu-16.04
SQL Server 2019 will run as non-root by default.
This container is running as user mssql.
Your master database file is owned by root.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
sqlservr: Unable to open /var/opt/mssql/.system/instance_id: Permission denied (13)
/opt/mssql/bin/sqlservr: Unable to open /var/opt/mssql/.system//instance_id: Permission denied (13)

Since that was a bust, let’s go ahead and delete that container since it’s not usable.

docker rm sql1
sql1

Changing Permissions on the Files

Let’s create an intermediate container, in this case using an Ubuntu image, and mount that data volume (sqldata1), and then change the permissions on the files SQL Server needs to work with.

docker run 
    --name 'permissionsarehard' 
    -v sqldata1:/var/opt/mssql 
    -it ubuntu:latest

If we look at the permissions of the instance directory (/var/opt/mssql/) we can see the files user and group owner are root. This is just a peek at the instance directory, we’ll need to adjust permissions on all of the file SQL Server needs to work with and recursively within this directory.

ls -la /var/opt/mssql
/var/opt/mssql:
total 24
drwxr-xr-x 6 root root 4096 Nov 20 13:43 .
drwxr-xr-x 1 root root 4096 Nov 20 13:46 ..
drwxr-xr-x 5 root root 4096 Nov 20 13:43 .system
drwxr-xr-x 2 root root 4096 Nov 20 13:43 data
drwxr-xr-x 2 root root 4096 Nov 20 13:43 log
drwxr-xr-x 2 root root 4096 Nov 20 13:43 secrets

Let’s adjust the permissions on the directories and files sqlservr needs access to…again I want to point out, that this is against the default instance directory which is /var/opt/mssql…if you have files in other locations they will need their permissions updated too. Check out the Microsoft Docs article here for more information on this.

ls -laR /var/opt/mssql
chgrp -R 0 /var/opt/mssql
chmod -R g=u /var/opt/mssql
chown -R 10001:0 /var/opt/mssql
ls -laR /var/opt/mssql
exit

Here’s some output from a directory listing of our instance directory after we’ve made the permissions changed…now they have the owner of 10001 and a group owner of root.

ls -la /var/opt/mssql
/var/opt/mssql:
total 24
drwxrwxr-x 6 10001 root 4096 Nov 20 13:43 .
drwxr-xr-x 1 root  root 4096 Nov 20 13:46 ..
drwxrwxr-x 5 10001 root 4096 Nov 20 13:43 .system
drwxrwxr-x 2 10001 root 4096 Nov 20 13:43 data
drwxrwxr-x 2 10001 root 4096 Nov 20 13:43 log
drwxrwxr-x 2 10001 root 4096 Nov 20 13:43 secrets

Let’s start up a 2019 non-root container now

Start up our 2019 container now…should work eh? Woot!

docker run 
    --name 'sql1' 
    -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD='$PASSWORD 
    -p 1433:1433 
    -v sqldata1:/var/opt/mssql 
    -d mcr.microsoft.com/mssql/server:2019-GDR1-ubuntu-16.04

Why UID 10001?

Let’s hop into the container now that it’s up and running…and we’ll see sqlservr is running as mssql which has a uid of 10001. This is the default uid used inside non-root container. If you’re using a system that doesn’t have this user defined, like the intermediate ubuntu container, you’ll need to adjust permissions using the uid directly. That permission information is written into the directory and files and when we start up the 2019 container again the correct permissions are in place since the uid of the mssql user matches the uid of the permissions on the files and directories.

docker exec -it sql1 /bin/bash
ps -aux
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mssql        1  8.4  0.3 148820 22768 ?        Ssl  13:49   0:00 /opt/mssql/bin/
mssql        9 96.5  9.3 7470104 570680 ?      Sl   13:49   0:03 /opt/mssql/bin/
mssql      140  2.0  0.0  18220  3060 pts/0    Ss   13:49   0:00 /bin/bash
mssql      148  0.0  0.0  34420  2792 pts/0    R+   13:49   0:00 ps -aux
id mssql
uid=10001(mssql) gid=0(root) groups=0(root)
exit

Is Everything OK?

Are our database there? Yep!

sqlcmd -S localhost,1433 -U sa -Q 'SELECT name from sys.databases' -P $PASSWORD
name
----
master
tempdb
model
msdb
TestDB1
(5 rows affected)

The post Upgrading SQL Server 2017 Containers to 2019 non-root Containers with Data Volumes appeared first on Centino Systems Blog.