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
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
GID information for a user. The
10001 and the user is a member of the root group,
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
docker cp to copy files into a container, the
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
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
In the directory listing below, for the files that we copied in, you can see the
501 and the
20. I’m on a Mac and my current user’s
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
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
The first command run is
chown, which sets the user and group owner as
10001 inside the container, and
GID 0, which is the
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
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.