Solution for SQL Server Permission Issues with Docker Compose

I've been busy with exercise, weight loss, and job hunting lately, so I haven't been researching programming much. I had considered organizing some materials for others at one point, but I got lazy and decided not to overcomplicate things.

Seeing friends at cafes studying programming to improve themselves these days, I thought I should pull myself together to avoid performing poorly in interviews due to rusty technical skills.

Environment Setup

I placed the SQL Server Docker-Compose file I created earlier in a WSL environment, configured as follows:

services:
  SQL-Server:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: SQL-Server
    ports:
      - "1433:1433"
    volumes:
      - ./volumes/data:/var/opt/mssql/data
      - ./volumes/log:/var/opt/mssql/log
      - ./volumes/backup:/var/opt/mssql/backup
    restart: unless-stopped
    environment:
      ACCEPT_EULA: "Y"
      SA_PASSWORD: "YourStrongPassword123!"
      TZ: "Asia/Taipei" # Set timezone
    deploy:
      resources:
        limits:
          memory: 2G # Maximum memory usage
          cpus: '1.0' # Maximum CPU cores
        reservations:
          memory: 1G # Guaranteed memory allocation
    healthcheck:
      test: ["CMD-SHELL", "/opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U sa -P $$SA_PASSWORD -Q 'SELECT 1'"]
      interval: 30s # Check every 30 seconds
      timeout: 10s # 10-second timeout for check command
      retries: 3 # Mark as unhealthy after 3 failures
      start_period: 60s # Start checking 60 seconds after container startup

WARNING

In the SQL Server 2025 image, the path for sqlcmd has been changed from /opt/mssql-tools/bin/sqlcmd to /opt/mssql-tools18/bin/sqlcmd.

The Problem

When running Docker Compose, permission-related error messages appear:

SQL Server 2022 will run as non-root by default.
This container is running as user mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.⁠
2025-08-23 00:01:10.09 Server Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
ERROR: BootstrapSystemDataDirectories() failure (HRESULT 0x80070005)
00:00:07.43 Server ERROR: Setup FAILED copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf': 5(Access is denied.)
00:01:10.10 Server ERROR: Setup FAILED copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf': 5(Access is denied.)

Previous Workarounds

In the past, to avoid permission issues, I would set the volume as:

./volumes/:/var/opt/mssql/external

This approach was used because the default SQL Server directories (/var/opt/mssql/data, /var/opt/mssql/log, /var/opt/mssql/backup) have permission restrictions, so I mapped the volume to a custom /var/opt/mssql/external directory that doesn't have these issues. When using this method, you have to manually specify the file path to the external directory when creating a database to store .mdf and .bak files in the mounted volume. However, the downside is that I often forget to manually specify the file location during database creation, causing files to be stored in the default location inside the container, which prevents persistence. Therefore, I decided to look for a more standard solution.

Standard Solution

Referencing this article on Stack Overflow: Unable to run SQL Server 2019 docker with volumes and get ERROR: Setup FAILED copying system data file

Complete Steps

The solution is to create the necessary subdirectories in the directory containing docker-compose.yml first, and then set the permissions for the volumes directory.

1. Create the directory structure:

mkdir -p volumes/data volumes/log volumes/backup

2. Set directory permissions:

chgrp -R 0 volumes
chmod -R g=u volumes
chown -R 10001:0 volumes

TIP

Testing shows that in most cases, you only need to execute the third command; the first two can be omitted.

3. Start the container:

docker-compose up -d

Why the original direct permission setting worked

When I first tested this, I was able to start the container successfully by running the permission commands directly on the volumes folder, but there was a hidden prerequisite: I had already tried to start the container and failed.

When the container fails to start for the first time, Docker automatically creates the directories corresponding to the mount points (volumes/data, volumes/log, volumes/backup) even if the container fails to start. Therefore, when I subsequently ran the permission settings on volumes, these subdirectories already existed, allowing the permission commands to be correctly applied to all necessary directories.

The problem is:

• If you are working in a brand-new environment, there are no subdirectories inside the volumes folder.
• Setting permissions directly on volumes does not automatically create the data, log, and backup subdirectories.
• When the container starts and finds these subdirectories missing or the permissions incomplete, it fails.

Therefore, the correct approach is: Manually create all necessary subdirectories first to ensure the directory structure is complete, then set the permissions, and finally start the container. This avoids relying on the unstable method of "failing once to trigger automatic directory creation."

Detailed Explanation of Permission Commands

Since I am not very familiar with Linux commands, here is the explanation I got from Claude regarding these permission commands:

First Command

chgrp -R 0 volumes

• chgrp: Change group ownership.
• -R: Recursive, including all subdirectories and files.
• 0: Group ID 0, which is the root group.
• volumes: The target directory.

Effect: Changes the group ownership of the volumes directory and all its contents to the root group.

Second Command

chmod -R g=u volumes

• chmod: Change mode (file permissions).
• -R: Recursive.
• g=u: Sets the group permissions to be the same as the user permissions.

Effect: Gives the group the same permissions as the file owner.

Third Command

chown -R 10001:0 volumes

• chown: Change owner.
• -R: Recursive.
• 10001:0: User ID 10001, Group ID 0.
  • 10001 = The mssql user ID predefined inside the SQL Server Docker image.
  • 0 = root group.

Effect: Changes the owner of the volumes directory to UID 10001 (the mssql user in the SQL Server image) and sets the group to root.

Overall Effect

The purpose of this combination of three commands:

1. Ensures the group is set to root (Group ID 0).
2. Makes group permissions equivalent to owner permissions (read, write, execute).
3. Sets the owner to the mssql user (UID 10001) inside the SQL Server container.

With these permission settings, the SQL Server container can properly access the mounted volumes directory, resolving the "Permission Denied" issue.

Change Log

• 2025-08-24 Initial document creation.
• 2025-11-04 Added complete operation steps, explaining the need to create subdirectories before setting permissions.
• 2026-02-05 Added a tip for the simplified permission setting scheme, noting that in most cases, only the third command is needed.
• 2026-03-16 Added information about the mssql-tools path change in SQL Server 2025.