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



  • 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❌999:
    sample_group❌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.



  • 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
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2