0

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`
  1. Add mssql user to the new group:
sudo usermod -a -G sample_group mssql
  1. The result of cat /etc/group | grep mssql command is:
mssql:x:999:
sample_group:x:1006:mssql
  1. Create a directory:
sudo mkdir /tmp/backup_dir/
  1. 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/
  1. 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
  1. 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.

1 Answer 1

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
6
  • Thanks, I restarted the server and it really helped. However, this option is not suitable for me. I can't even restart sql server in prod. Can I somehow force the inclusion of mssql in the group? And if it's not difficult, you can tell me how you found out about this, I did not find anything about this in the sql server for linux documentation.
    – Ivan
    Feb 18 at 19:17
  • 1
    I'm sure it's documented somewhere, but I must have picked it up somewhere during the thirty odd years working with unix and linux. It has nothing to do with sql server, apart from the fact that a server is a long running process and you can't change a running process's environment. But there are a number of other solutions for your problem, if you're interested. Feb 18 at 21:49
  • 1
    I forgot to mention: the backup is created by the server process, not by the sqlcmd process. Else you wouldn't have this problem. If you compare with PostgreSQL, you are probably referring to pg_dump, which is not a server process. If you were to ask the PostgreSQL server to do a "COPY" to a file, mind, not a "\COPY", you would have the same problem. Feb 18 at 22:04
  • I've added another solution to my answer. If you're not allowed to restart the server, it's possible you won't be able to take a backup to a directory of your choice either. What is preventing the restart? Feb 19 at 10:46
  • Thanks for the explanation. I understand, the problem is that the list of groups that the user belongs to is read at the start of the process. My main development experience has been on Windows, and it works differently. unix.stackexchange.com/questions/208617/… is a recipe for how you can update a user group through the debugger using the 'setgroups' system call, but this is clearly not a standard solution, and most likely with my own pitfalls, I will not use it.
    – Ivan
    Feb 19 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.