Configuring permissions for the SQL Backup Agent service

Category: Security & data protection
Date: 10 Aug 2009
Product: SQL Backup
In some cases, you may want SQL Backup to write backups directly to a network share, or copy them from local disk to a network share. This may require some extra configuration parameters in regards to the SQL Backup Agent service.

The SQL Backup process is controlled by a dedicated Windows Service (SQL Backup Agent) for each instance of SQL Server installed.

The startup account of the Agent service needs access to the backup file target (local or network) as well as having BACKUP DATABASE rights in SQL Server. If you do not want to grant database permissions to the Agent's startup account, you may configure a SQL security login to connect to SQL Server if you wish to have additional security or your security policy requires this (information for this can be found in the Security Model section of the help file).

An example of using a different security model for the SQL Backup setup would be when you wish to backup simultaneously to local disk and a locked-down network share. Assuming that you are in a single domain:

   - Create a Domain user account with minimal permissions (x).
   - Create a SQL Server login that has the ability to backup (and restore) databases (y) by adding it to the SYSADMIN server role. (NB SQL Backup versions 4-5.1 allowed db_backupoperator fixed role or using SQL Server 2005's GRANT BACKUP... commands.)
   - Add the domain account (x) to a security group on the SQL Server computer that has sufficient privileges to log on as a service, etc.
   - Install SQL Backup and set the service account to the domain account (x) then set the connection to the database to use the SQL Login (y).
   - Create a folder on the local machine and a share on a remote server to hold your backups.
   - Confirm that the permissions on these folders are set to allow your domain user (x) to access and write to them, also ensure that the network share has sufficient permissions for the user (x).
   - Now create a backup job that backs up to the local folder and copies to the network share.

The reason for checking the filesystem and share permissions is that the SQL Backup process uses the Agent's startup user for all file I/O functions like writing to a folder or network share.

In a situation where the SQL Server and the network share server do not participate in the same Windows domain, you can use matching Windows local usernames and passwords on each server to perform the same task. This practice is commonly known as 'matching accounts'.