Security model - SQL Backup

The SQL Backup process is controlled by a dedicated SQL Backup Agent Service application for each SQL Server instance.

To use the SQL Backup graphical user interface or extended stored procedure to perform backup and restore operations, the startup user for the SQL Backup Agent Service application must be a member of the SQL Server sysadmin fixed server role. If you are working with network files (for example, for log shipping), the user must have access to those network shares; for more information, see the article Using SQL Backup to back up to a network share. In addition, the SQL Backup user must have:

• the usual SQL Server backup permissions
• execute permissions on the SQL Backup extended stored procedure sqlbackup (for example, must be a member of the public database role in the master database)
• for the graphical user interface, membership of the SQL Server sysadmin fixed server role.
• for the compression analyzer, execute permissions on the following extended stored procedures:

  sqbtest	sqbtestcancel
  sqbteststatus

When you install the server components, you are asked to provide 'log on' details for the SQL Backup Agent Service to connect to the SQL Server. By default, the SQL Backup Agent Service connects to the SQL Server instance using Windows authentication, but if required, you can specify SQL Server Authentication in the installation wizard. If you want to change the credentials at a later date, use the sqbsetlogin extended stored procedure to provide the login name and password. You will need to add the stored procedure before you use it, and you are recommended to remove it when you have finished using it. For example:

EXEC sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'

sqbsetlogin 'sa', 'sqbpassword'

EXEC sp_dropextendedproc sqbsetlogin

To revert to Windows authentication, call sqbsetlogin with blank values:

sqbsetlogin '', ''

If you encounter errors related to permissions and access rights, ensure that the startup user for the SQL Backup Agent Service application has been granted the necessary permissions.

The SQL Backup command line program communicates with SQL Server directly; it does not use the service application. To run SQLBackupC.exe, the user must have the SQL Server sysadmin fixed role.

Using a different security model

You may want to use a different security model, for example if you want to back up locally but copy the backup to a locked down network share. The following procedure assumes that you are working in a single domain.

1. Create domain account with minimal permissions.
2. Create a SQL Server logon account that has the ability to back up and restore databases.

   To do this, add the account to the sysadmin or db_backupoperator fixed role, or if you are using SQL Server 2005 or 2008, you can use the GRANT BACKUP command.

3. Add the domain account that you created in step 1 to a security group on the Windows server on which the SQL Server is installed; the security group must have sufficient permissions to run as a service.
4. When you install the SQL Backup server components on the SQL Server:
   • For the SQL Backup Agent Service credentials, select This account and enter the domain account you created in step 1.
   • For the SQL Server credentials, select SQL Server Authentication, and specify the credentials for the SQL Server logon account you created.
5. Create the folder on the local server in which you want to create the backups, and a folder on a network share to which you want to copy the backup files.
6. Confirm that the permissions on both folders are set such that the domain user you created in step 1 can access and write to them.

To check that all the accounts have the appropriate permission, use the graphical user interface to create a backup job that backs up to a local folder and copies the backup to a network share.

You could also run the following query to ensure that the domain account has sufficient permissions on the network share:

EXECUTE master..sqbutility 999, 'RWE', '\\testsrv\backup'

If this is successful and the agent as read (R), write (W), and execute (E) permissions, the query will return:

<SQBUTILITYRESULT>:1:

If there is a problem, the query will return a value of 0, followed by a message, for example:

<SQBUTILITYRESULT>:0:Folder does not exist : \\testsrv\backup

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