Security model - SQL Backup

This topic explains the permissions required to use SQL Backup.

Using SQL Backup from the graphical user interface

The user connecting to the SQL Backup GUI requires:

• membership of the SQL Server sysadmin fixed server role
• execute permissions on the SQL Backup extended stored procedure, sqlbackup
• for the compression analyzer, execute permissions on sqbtest, sqbtestcancel and sqbteststatus extended stored procedures

The SQL Backup Agent service is a Windows service which is used to perform the SQL Backup backup and restore operations through the graphical user interface or the extended stored procedure. The user account used to log on to the SQL Backup Agent service (the startup account) and connect to the SQL Server requires:

• 'log on as a service' rights in order to start the service
• membership of the SQL Server sysadmin fixed server role
• access to any network locations that will be backed up or copied to, or restored from
• access to the following folders:
  • the Red Gate licensing registry folder HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\Licensing\SQL Backup or HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Red Gate\Licensing\SQL Backup (for 64-bit machines)
  • the Red Gate licenses folder C:\ProgramData\Red Gate\Licenses (for Windows Server 2008, Windows Server 2008 R2, Windows Vista and Windows 7) or C:\Documents and settings\All users\Application Data\Red Gate\Licenses (for Windows Server 2003 and Windows XP)
  • the SQL Backup local data store C:\ProgramData\Red Gate\SQL Backup\Data (for Windows Server 2008, Windows Server 2008 R2, Windows Vista and Windows 7) or C:\Documents and settings\All users\Application Data\Red Gate\SQL Backup\Data (for Windows Server 2003 and Windows XP)
  • the SQL Backup logs folder C:\ProgramData\Red Gate\SQL Backup\Log (for Windows Server 2008, Windows Server 2008 R2, Windows Vista and Windows 7) or C:\Documents and settings\All users\Application Data\Red Gate\SQL Backup\Log (for Windows Server 2003 and Windows XP)

You specify the startup account for the SQL Backup Agent service (the account the service will run as) when you install the server components on a SQL Server instance. By default, the SQL Backup Agent service connects to the SQL Server instance using the Windows authentication of the startup account. If required, you can specify that the SQL Backup Agent service should use SQL Server authentication to connect to the SQL Server instance instead. The SQL Server authenticated account must also be a member of the SQL Server sysadmin fixed server role.

You can change the authentication mode the startup account uses to connect to the SQL Server instance at a later date by using the sqbsetlogin extended stored procedure:

1. Add the sqbsetlogin stored procedure from the SQL Backup extended stored procedure dynamic link library (xp_sqlbackup.dll).
2. Provide the user name and password to specify SQL Server authentication.
3. You are recommended to remove the sqbsetlogin stored procedure when you have finished using it.

For example:

EXECUTE master..sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'

EXECUTE master..sqbsetlogin 'sa', 'sqbpassword'

EXECUTE master..sp_dropextendedproc sqbsetlogin

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

EXECUTE master..sqbsetlogin '', ''

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

Using SQL Backup from the extended stored procedure

The user running the extended stored procedure requires:

• permission to back up, restore and drop databases
  • to back up databases, the user must be a member of the db_backupoperator fixed database role, or you can use the GRANT BACKUP DATABASE command to grant the permission
  • to restore or drop databases, the user must be a member of the db_owner fixed database role or the dbcreator fixed server role, or you can use the GRANT CREATE DATABASE command to grant the permission
• execute permissions on the SQL Backup extended stored procedure, sqlbackup
• for the compression analyzer, execute permissions on sqbtest, sqbtestcancel and sqbteststatus extended stored procedures

The SQL Backup Agent service is used to perform backup and restore operations through the graphical user interface or the extended stored procedure. The user account used to log on to the SQL Backup Agent service (the startup account) and connect to the SQL Server requires:

• 'log on as a service' rights in order to start the service
• membership of the SQL Server sysadmin fixed server role
• access to any network locations that will be backed up or copied to, or restored from
• access to the following folders:
  • the Red Gate licensing registry folder HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\Licensing\SQL Backup or HKEY_LOCAL_MACHINE\SOFTWARE\\WOW6432NodeRed Gate\Licensing\SQL Backup (for 64-bit machines)
  • the Red Gate licenses folder C:\ProgramData\Red Gate\Licenses (for Windows Server 2008, Windows Server 2008 R2, Windows Vista and Windows 7) or C:\Documents and settings\All users\Application Data\Red Gate\Licenses (for Windows Server 2003 and Windows XP)
  • the SQL Backup local data store C:\ProgramData\Red Gate\SQL Backup\Data (for Windows Server 2008, Windows Server 2008 R2, Windows Vista and Windows 7) or C:\Documents and settings\All users\Application Data\Red Gate\SQL Backup\Data (for Windows Server 2003 and Windows XP)
  • the SQL Backup logs folder C:\ProgramData\Red Gate\SQL Backup\Log (for Windows Server 2008, Windows Server 2008 R2, Windows Vista and Windows 7) or C:\Documents and settings\All users\Application Data\Red Gate\SQL Backup\Log (for Windows Server 2003 and Windows XP)

You specify the startup account for the SQL Backup Agent service (the account the service will run as) when you install the server components on a SQL Server instance. By default, the SQL Backup Agent service connects to the SQL Server instance using the Windows authentication of the startup account. If required, you can specify that the SQL Backup Agent service should use SQL Server authentication to connect to the SQL Server instance instead. The SQL Server authenticated account must also be a member of the SQL Server sysadmin fixed server role.

You can change the authentication mode the startup account uses to connect to the SQL Server instance at a later date by using the sqbsetlogin extended stored procedure:

1. Add the sqbsetlogin stored procedure from the SQL Backup extended stored procedure dynamic link library (xp_sqlbackup.dll).
2. Provide the user name and password to specify SQL Server authentication.
3. You are recommended to remove the sqbsetlogin stored procedure when you have finished using it.

For example:

EXECUTE master..sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'

EXECUTE master..sqbsetlogin 'sa', 'sqbpassword'

EXECUTE master..sp_dropextendedproc sqbsetlogin

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

EXECUTE master..sqbsetlogin '', ''

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

Using SQL Backup from the command line

The SQL Backup command line program communicates with SQL Server directly; it does not use the SQL Backup Agent 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.

   Add the domain account 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.

2. Create a SQL Server authenticated 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, 2008 or 2012, you can use the GRANT BACKUP command.

3. 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 authenticated account you created.
4. 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.
5. 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 SQL Backup Agent service has 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'.