Solution for SQL Server Permission Issues with Docker Compose

I have been busy with exercise, weight loss, and job hunting lately, so I haven't been researching programming much. I once thought about organizing some materials for others, but I got lazy later and decided not to bother.

Seeing my friends studying programming-related materials in cafes to enrich themselves these past few days, I decided to pull myself together to avoid poor performance 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
    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 # Command timeout 10 seconds
      retries: 3 # Unhealthy after 3 failures
      start_period: 60s # Start checking 60 seconds after container starts

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.

Issues Encountered

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

Previously, to avoid permission issues, I would set the Volume as:

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

This approach was used because the default SQL Server folders (/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 folder that doesn't have these issues. When using this method, you need 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 of this method is that I often forget to manually specify the file location when creating a database, causing files to be stored in the container's default location instead, which prevents persistent storage. Therefore, I later 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 permissions for the volumes folder.

1. Create folder structure:

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

2. Set folder 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 was testing initially, I could successfully start the container by running the permission setting commands directly on the volumes folder, but this actually had a hidden prerequisite: I had already tried to start the container and it failed.

When the container fails to start for the first time, Docker automatically creates the folders corresponding to the mount points volumes/data, volumes/log, and volumes/backup (even if the container fails to start, the folders are still created). Therefore, when I subsequently executed 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 operating in a brand-new environment, there are no subdirectories inside the volumes folder.
• Setting permissions on volumes directly 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 automatically create folders."

Detailed Explanation of Permission Commands

Since I am not very familiar with Linux commands, here is the explanation I asked Claude for 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: 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 (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 inside the SQL Server container (UID 10001).

Through this permission configuration, the SQL Server container can normally access the mounted volumes folder, resolving the "Permission Denied" issue.

Changelog

1. 2025-08-24

   • Initial document creation.
2. 2025-11-04

   • Added complete operation steps, explaining the need to create subdirectories before setting permissions.
3. 2026-02-05

   • Added a tip for a simplified permission setting scheme, noting that in most cases, only the third command needs to be executed.
4. 2026-03-16

   • Added information about the mssql-tools path change in SQL Server 2025.