SQL Server on Linux, an issue with filesystem privileges for a backup creation

Question

I'm trying to backup SQL Server to a directory that different users should have access to. To do this, I create a user group, and include the mssql user there - but when I try to create a backup, an "access denied" error appears.

Here is what I do:

1. Create a user group:

sudo groupadd sample_group`

2. Add mssql user to the new group:

sudo usermod -a -G sample_group mssql

3. The result of cat /etc/group | grep mssql command is:

mssql:x:999:
sample_group:x:1006:mssql

4. Create a directory:

sudo mkdir /tmp/backup_dir/

5. Add sample_group user group to the directory and provide previligious to this directory:

sudo chgrp sample_group /tmp/backup_dir/
sudo chmod 771 /tmp/backup_dir/

6. The result of sudo ls -l /tmp/ | grep sample_group command is:

drwxrwx--x 2 root sample_group 4096 Feb 18 12:11 backup_dir

7. Try to backup in this directory:

sqlcmd -U sa -P ********* -Q "backup database AdventureWorks TO disk='/tmp/backup_dir/1.bak'"

->

Msg 3201, Level 16, State 1, Server ubuntuAutoTest, Line 1
Cannot open backup device '/tmp/backup_dir/1.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Server ubuntuAutoTest, Line 1

Why is there no access? SQL Server runs as mssql user. mssql user is in sample_group group, which owns /tmp/backup_dir/ directory

Notes:

If I use chmod 777, then the backups are created on behalf of the mssql user, the created file is owned in the mssql user group.

If I specify mssql user group as the owners of the directory, then the backups are created.

If I go to the /tmp/backup_dir/ directory on behalf of the mssql user (sudo su - mssql), then I can create files, without any issues, there are all permissions.

For a PostgreSQL database, following the steps above works without problems. Only the postgres user is used instead of the mssql user.

Answer 1

mssql will need to login again before getting the new group. Easiest would be to restart that server.

Alternate solution:

Add the users that need to read the backup, to the sample_group, then

chown mssql:sample_group /tmp/backup_dir
chmod 2750 /tmp/backup_dir

sqlcmd -U sa -P ********* -Q "backup database AdventureWorks TO disk='/tmp/backup_dir/1.bak'"

Test if the users can read the backup (will depend on mssql's umask), if not:

chmod a+r /tmp/backup_dir/1.bak