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 want to upgrade your system the SQL Server 2019 container…well 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. This article describe a solution to getting things sorted and running again. The scenario below is if you’re using a Linux based SQL Server container on Windows or Mac host where the container volumes are backed by a Docker Moby or HyperKit virtual machine. If you’re using Linux container on Linux, you’ll adjust the file system permissions directly.
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)
Another Method
If you like living on the edge you can correct the permissions logging into the running 2017 container prior to shutdown and not using an intermediate container, check out this post here.