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:
- Create a user group:
sudo groupadd sample_group`
mssql user to the new group:
sudo usermod -a -G sample_group mssql
- The result of
cat /etc/group | grep mssql command is:
- Create a directory:
sudo mkdir /tmp/backup_dir/
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/
- 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
- 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 is in
sample_group group, which owns
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