Operating system error 5 (Access is denied.) when using MSSQL

[WilliamRockett]

Summary

I am trying to set up db-backup for MSSQL using the default configuration in compose.yml. I have manually created the test1 database as instructed. However, I am encountering a permission error from mssql when the backup performs.

Steps to reproduce

• Clone repo
• Run docker-compose up from folder examples/mssql

What is the expected correct behavior?

It should backup the test1 database from mssql without any permission error and copy the backup file to the backup folder.

Relevant logs and/or screenshots

example-mssql-db-backup  | Image:  tiredofit/db-backup | Version  4.0.35 Type 'image_changelog' for details
example-mssql-db-backup  | Repository/Documentation: https://github.com/tiredofit/docker-db-backup/
example-mssql-db-backup  |
example-mssql-db-backup  | If this image provides you value  - Consider sponsoring my work for continued
example-mssql-db-backup  | development, timely updates, and feature requests. Commercial support available.
example-mssql-db-backup  |
example-mssql-db-backup  |                     More Info:  https://www.tiredofit.ca
example-mssql-db-backup  |
example-mssql-db-backup  | 2024-02-21.09:53:44 [NOTICE] ** [timezone] Timezone: Setting to 'America/Vancouver' from 'Etc/GMT'
example-mssql-db-backup  | 2024-02-21.01:53:44 [NOTICE] ** [scheduling] Container configured for scheduled tasks with 'cron'
example-mssql-db-backup  | 2024-02-21.01:53:45 [NOTICE] ** [messaging] Container configured to route mail via SMTP to 'postfix-relay'
example-mssql-db-backup  | 2024-02-21.01:53:45 [STARTING] ** [scheduling] [1] Starting cron
example-mssql-db-backup  | 2024-02-21.01:53:46 [INFO] ** [01-example-db-host__test1] Next Backup at 2024-02-21 01:53:46 PST
example-mssql-db-backup  | 2024-02-21.01:53:46 [NOTICE] ** [01-example-db-host__test1] Dumping MSSQL database: 'test1'
example-mssql-db         | 2024-02-21 09:53:46.77 spid53      [5]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2024-02-21 09:53:46.77 spid53      Starting up database 'test1'.
2024-02-21 09:53:46.80 spid53      Parallel redo is started for database 'test1' with worker pool size [4].
2024-02-21 09:53:46.82 spid53      Parallel redo is shutdown for database 'test1' with worker pool size [4].
2024-02-21 09:53:46.92 spid53      Error: 18204, Severity: 16, State: 1.
2024-02-21 09:53:46.92 spid53      BackupDiskFile::CreateMedia: Backup device '/tmp/backups/01_dbbackup.e44L15/mssql_test1_example-db-host_20240221-015346.bak' failed to create. Operating system error 5(Access is denied.).
2024-02-21 09:53:46.92 Backup      Error: 3041, Severity: 16, State: 1.
2024-02-21 09:53:46.92 Backup      BACKUP failed to complete the command BACKUP DATABASE test1. Check the backup application log for detailed messages.
example-mssql-db-backup  | cat: /tmp/backups/01_dbbackup.e44L15/mssql_test1_example-db-host_20240221-015346.bak: No such file or directory
example-mssql-db-backup  | 2024-02-21.01:53:46 [INFO] ** [01-example-db-host__test1] DB Backup of 'mssql_test1_example-db-host_20240221-015346.bak' completed successfully
example-mssql-db-backup  | stat: cannot stat '/tmp/backups/01_dbbackup.e44L15/mssql_test1_example-db-host_20240221-015346.bak': No such file or directory
example-mssql-db-backup  | date: /tmp/backups/01_dbbackup.e44L15/mssql_test1_example-db-host_20240221-015346.bak: No such file or directory
example-mssql-db-backup  | stat: cannot stat '/tmp/backups/01_dbbackup.e44L15/mssql_test1_example-db-host_20240221-015346.bak': No such file or directory
example-mssql-db-backup  | 2024-02-21.01:53:46 [NOTICE] ** [01-example-db-host__test1] Backup of 'mssql_test1_example-db-host_20240221-015346.bak' created with the size of  bytes
example-mssql-db-backup  | chmod: cannot access '/tmp/backups/01_dbbackup.e44L15/mssql_test1_example-db-host_20240221-015346.bak': No such file or directory
example-mssql-db-backup  | mv: cannot stat '/tmp/backups/01_dbbackup.e44L15/mssql_test1_example-db-host_20240221-015346.bak': No such file or directory
example-mssql-db-backup  | 2024-02-21.01:53:47 [ERROR] ** [01-example-db-host__test1] Moving of backup 'mssql_test1_example-db-host_20240221-015346.bak' reported errors
example-mssql-db-backup  | 2024-02-21.01:53:47 [NOTICE] ** [01-example-db-host__test1] DB Backup for 'test1' time taken: Hours: 0 Minutes: 00 Seconds: 01
example-mssql-db-backup  | 2024-02-21.01:53:47 [ERROR] ** [01-example-db-host__test1] Skipping Cleaning up old backups because there were errors in backing up
example-mssql-db-backup  | 2024-02-21.01:53:47 [INFO] ** [01-example-db-host__test1] Backup 01 routines finish time: 2024-02-21 01:53:47 PST with exit code 0
example-mssql-db-backup  | 2024-02-21.01:53:47 [NOTICE] ** [01-example-db-host__test1] Backup 01 routines time taken: Hours: 0 Minutes: 00 Seconds: 01
example-mssql-db-backup  | 2024-02-21.01:53:47 [NOTICE] ** [01-example-db-host__test1] Sleeping for another 59 seconds

Logs from SQL Server

2024-02-21 09:52:44.63 spid53      [5]. Feature Status: PVS: 0. CTR: 0. ConcurrentPFSUpdate: 1.
2024-02-21 09:52:44.63 spid53      Starting up database 'test1'.
2024-02-21 09:52:44.67 spid53      Parallel redo is started for database 'test1' with worker pool size [4].
2024-02-21 09:52:44.69 spid53      Parallel redo is shutdown for database 'test1' with worker pool size [4].
2024-02-21 09:52:44.79 spid53      Error: 18204, Severity: 16, State: 1.
2024-02-21 09:52:44.79 spid53      BackupDiskFile::CreateMedia: Backup device '/tmp/backups/01_dbbackup.ZE1bsk/mssql_test1_example-db-host_20240221-015244.bak' failed to create. Operating system error 5(Access is denied.).
2024-02-21 09:52:44.79 Backup      Error: 3041, Severity: 16, State: 1.
2024-02-21 09:52:44.79 Backup      BACKUP failed to complete the command BACKUP DATABASE test1. Check the backup application log for detailed messages.

Logs using DEBUG_MODE=TRUE

https://pastebin.com/yKj26ZfQ

Environment

• Image version / tag: 4.0.35
• Host OS: Win 11 Pro 23H2 Build 22631
• docker-compose.yml from examples/mssql folder (mcr.microsoft.com/mssql/server:2019-latest)

[Remmert-PBE]

The problem is that the unique temporary folder created by the script via mktemp is owned by the user docker-db-backup uses (normally 10000) and has a default permission of 700.

The current MSSQL container >= 2019 are run under the user "mssql" with a user id of 10001, so the backup function inside the sql server cannot access that temporary folder.

So a workaround is to set the dbbackup_user/dbbackup_group to 10001.

But maybe the prepare_dbbackup function should also run chmod with the value of DEFAULT_FILESYSTEM_PATH_PERMISSION