Security Model |
See Also |
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 Using SQL Backup to back up to a network share which is available as a pdf download from the Red Gate Support Web site. In addition, the SQL Backup user must have:
sqbdata | sqbstatus |
sqbdir | sqbutility |
sqbmemory | sqlbackup |
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:
sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'
sqbsetlogin 'sa', 'sqbpassword'
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.
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.
To do this, add the account to the sysadmin or db_backupoperator fixed role, or if you are using SQL Server 2005, you can use the GRANT BACKUP command.
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'.
See Also |
About the Graphical User Interface | Log Shipping | Using the Command Line | Using the Extended Stored Procedure
© Red Gate Software Ltd 2008. All Rights Reserved.