SQL Backup 4.0 ONLY - Check service user account

HJoyce

If you encounter errors with regards to access rights, you should first check if the SQL Backup Agent service startup user has been granted the necessary rights.

By default, the SQL Backup Agent connects to the SQL Server instance using Windows authentication. The 'SQL Backup Agent' service is started using 'Local System' account. This will cause problems for example if the Builtin\Administrators group has been removed.

You can choose to have the SQL Backup Agent connect using SQL authentication, by using the sqbsetlogin extended stored procedure to provide the login name and password.

You need to add this extended stored procedure first before using it:

sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'

Use the sqbsetlogin extended stored procedure to set the SQL login and password e.g.

sqbsetlogin 'sa', 'sqbrocks'

To revert to Windows authentication, provide blank values for the parameters e.g.

sqbsetlogin '', ''

It is recommended that you remove this extended stored procedure when you have completed your modifications.

sp_dropextendedproc sqbsetlogin

The service application's service startup user needs to be an account that has the SQL Server sysadmin fixed server role, in order to process backups and restores successfully. If you are also working with network files e.g. copying to network shares, this user needs to have access to those network shares too.