Using volumes in SQL Server 2019 non-root containers

I’ve seen a few people online asking how to use docker named volumes with the new SQL Server 2019 RTM images. Microsoft changed the way SQL runs within a container for the RTM versions, SQL no longer runs as the root user.

This is a good thing but does throw up some issues when mounting volumes to create databases on.

Let’s run through what the issue is and how to overcome it.

Run a container from the 2019 GDR1 ubuntu image: –

docker container run -d `
-p 15789:1433 `
--volume sqlserver:/var/opt/sqlserver `
--env MSSQL_SA_PASSWORD=Testing1122 `
--env ACCEPT_EULA=Y `
--env MSSQL_BACKUP_DIR="/var/opt/sqlserver" `
--env MSSQL_DATA_DIR="/var/opt/sqlserver" `
--env MSSQL_LOG_DIR="/var/opt/sqlserver" `
--name testcontainer `
mcr.microsoft.com/mssql/server:2019-GDR1-ubuntu-16.04

What we’re going here is mounting a named volume called sqlserver to /var/opt/sqlserver within the container. Then we’re setting the default data, log, and backup location to /var/opt/sqlserver/.

Now if we try and create a database using those defaults: –

CREATE DATABASE [TestDatabase];
GO

Msg 5123, Level 16, State 1, Line 1

CREATE FILE encountered operating system error 2(The system cannot find the file specified.) while attempting to open or create the physical file ‘/var/opt/sqlserver/testdatabase.mdf’.

Msg 1802, Level 16, State 4, Line 1

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

We get an error message as the SQL instance within the container does not have access to that location because it’s running as the mssql user.

We need to grant the mssql user access to that folder: –

docker exec -u 0 testcontainer bash -c "chown mssql /var/opt/sqlserver"

This will make the mssql user the owner of that folder, so we can now create the database: –

However, we would have to run that command every time we spin up a container with named volumes mounted. A better way would be to create a custom image from a Dockerfile that has created that folder within the container and granted the mssql user access: –

FROM mcr.microsoft.com/mssql/server:2019-GDR1-ubuntu-16.04
USER root
RUN mkdir /var/opt/sqlserver
RUN chown mssql /var/opt/sqlserver
ENV MSSQL_BACKUP_DIR="/var/opt/sqlserver"
ENV MSSQL_DATA_DIR="/var/opt/sqlserver"
ENV MSSQL_LOG_DIR="/var/opt/sqlserver"
USER mssql
CMD /opt/mssql/bin/sqlservr

We’re using the USER command to switch to the root user in order to grant access to the folder and then switching back to the mssql user to run SQL.

Create the custom image: –

docker build -t custom2019image .

Now we can run a container from that image: –

docker container run -d `
-p 15789:1433 `
--volume sqlserver:/var/opt/sqlserver `
--env MSSQL_SA_PASSWORD=Testing1122 `
--env ACCEPT_EULA=Y `
--name testcontainer `
custom2019image

And create the database without having to run anything else: –

CREATE DATABASE [TestDatabase];
GO

Hope that helps!