Setting Permissions on Files Inside a Container for SQL Server

SQL, Containers
Page content

This post will walk you through setting file permissions on database files copied into a container. The SQL Server process sqlservr running in containers runs as the non-privileged user mssql. The appropriate permissions on files are needed, so the SQL Server process has the proper access to any database files, log files, and backup files.

Start up a container

First up, let’s start up a container. Here’s we’re starting up SQL Server 2019 CU11 and attaching a Docker data volume for our persistent data.

docker run \
    --env 'ACCEPT_EULA=Y' \
    --env 'MSSQL_SA_PASSWORD=S0methingS@Str0ng!' \
    --name 'sql1' \
    --publish 1433:1433 \
    --volume sqldata1:/var/opt/mssql \
    --detach mcr.microsoft.com/mssql/server:2019-CU11-ubuntu-18.04

With the container up and running, let’s check out a few things. First, using ps -aux, in the output below, you can see the sqlservr processes are running as the user mssql.

docker exec sql1 bash -c 'ps -aux'         
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mssql        1  0.0  0.2 152092  4968 ?        Ssl  06:38   0:00 /opt/mssql/bin/sqlservr
mssql       10  5.9 45.4 8916492 924852 ?      Sl   06:38  18:43 /opt/mssql/bin/sqlservr
mssql     2858  0.0  0.1  34416  2736 ?        Rs   11:53   0:00 ps -aux

We can use the id command to look at the UID and GID information for a user. The UID for mssql is 10001 and the user is a member of the root group, GID 0.

docker exec sql1 bash -c 'id mssql'                           
uid=10001(mssql) gid=0(root) groups=0(root)

Copy the database files into the container

Next, let’s use docker cp to copy an existing database, both the data and log files, into the container.

docker cp tpcc100.mdf sql1:/var/opt/mssql/data/
docker cp tpcc100_log.ldf sql1:/var/opt/mssql/data/

Examine the ownership and permissions after copying the file into the container

When using docker cp to copy files into a container, the UID and GID of the user executing the copy are used as the default permission set on the files copied into the container.

The user accounts and groups on the base OS likely don’t sync up with the user accounts and groups inside the container. So we need to look at things using the UID and GID. In Linux, UID and GID are used for permissions. The human-readable names are just for us humans. So let’s look at things file ownership and permissions using the -n parameter on the ls command.

In the directory listing below, for the files that we copied in, you can see the UID is 501 and the GID is 20. I’m on a Mac and my current user’s UID is 501 and GID is 20. So those are the permissions on the files copied into the container. These files are not accessible by SQL Server since the UID of user mssql is 10001.

docker exec sql1 bash -c 'ls -lan /var/opt/mssql/data/tpcc*'
-rw-r----- 1   501 20 21474836480 Sep 24 14:23 tpcc100.mdf
-rw-r----- 1   501 20  1073741824 Sep 24 14:23 tpcc_log100.ldf

Set the appropriate owners and permissions on the database files in the container

So let’s fix things up and set the appropriate owners and permissions on the files. We’ll use docker exec to run the appropriate commands inside the container to set the ownership and permissions. Since the container is running as mssql we need to specify the -u 0 parameter to run these commands as root.

The first command run is chown, which sets the user and group owner as mssql, UID 10001 inside the container, and GID 0, which is the root user.

docker exec -u 0 sql1 bash -c 'chown 10001:0 /var/opt/mssql/data/tpcc*'

The second command we’ll need is chmod to set the permissions on the files. Here we’re going to set the permissions to rw using the octal notation of 660 for both the user and group owner.

docker exec -u 0 sql1 bash -c 'chmod 660 /var/opt/mssql/data/tpcc*'

Check out the new permissions

The user and group owners of the files are now mssql and root and the permissions are rw for each file. These permissions enable the mssql user to read and write these files.

docker exec sql1 bash -c 'ls -laR /var/opt/mssql/data/tpcc*'

-rw-rw---- 1 mssql root 21474836480 Sep 24 14:23 /var/opt/mssql/data/tpcc100.mdf
-rw-rw---- 1 mssql root  1073741824 Sep 24 14:23 /var/opt/mssql/data/tpcc_log100.ldf

Attach the database

With everything set properly, let’s attach the database.

QUERY="CREATE DATABASE tpcc100 ON (FILENAME = '/var/opt/mssql/data/tpcc100.mdf'), (FILENAME = '/var/opt/mssql/data/tpcc_log100.ldf') FOR ATTACH;"
sqlcmd -S localhost,1433 -U sa -Q $QUERY -P 'S0methingS@Str0ng!'

Finally, let’s check to see if our database is there and tpcc100 is in there!

sqlcmd -S localhost,1433 -U sa -Q 'SELECT name from sys.databases' -P 'S0methingS@Str0ng!'

name                                                 
----------------
master         
tempdb                    
model                     
msdb                      
tpcc100

PS: If you’re copying a backup into a container to restore you can use this same technique to set permissions on the backup file so SQL Server has access to read the file.