Search code examples
c#sqlsql-serveraccess-rights

Why can't I backup to a directory in SQL Server?


I am debugging an issue that deals with SQL Server backing up the database to a directory.

The application I am running launches with Administrator rights, which is set in the app.manifest file:

<requestedExecutionLevel level="requireAdministrator" uiAccess="false" />

The application sets the initial directory to the default SQL Server backup directory:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup

The user is allowed to change the backup directory, and they can type it in, so something like this is typed:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup 12

Then the program then instruct SQL to back up to the directory using the stored procedure:

BACKUP DATABASE @DatabaseName TO DISK = @FilePath WITH NAME = @BackupName

Where:

  • @FilePath is the directory the user selects
  • @BackupName is YYYYMMDDHHMMSS.bak

The call fails with:

Operating system error 5 (access is denied).

So I did some investigation on the directory rights using icacls on the command line for the default SQL Server backup directory:

icacls .\backup 

CREATOR OWNER:(OI)(CI)(IO)(F) 
NT AUTHORITY\SYSTEM:(OI)(CI)(F) 
BUILTIN\Administrators:(OI)(CI)(F) 
MIKE-2K8-1\SQLServerMSSQLUser$mike-2k8-1$MSSQLSERVER:(OI)(CI)(F) 

Then again for the directory the user creates:

icacls .\backup 12

BUILTIN\Administrators:(I)(F) 
CREATOR OWNER:(I)(OI)(CI)(IO)(F) 
NT AUTHORITY\SYSTEM:(I)(OI)(CI)(F) 
BUILTIN\Administrators:(I)(OI)(CI)(IO)(F) 
BUILTIN\Users:(I)(OI)(CI)(RX) 
MIKE-2K8-1\SQLServerMSSQLUser$mike-2k8-1$MSSQLSERVER:(I)(OI)(CI)(RX)

I can see the permissions are different for the SQLServerMSSQLUser depending upon the directory... how can I change the permissions?


Solution

  • Is the user account you're running the BACKUP DATABASE query under part of the db_backupoperator group?