I am new to sql backup. Just installed it over the weekend. I scheduled a sql backup job and I do not know where to change the username that the job runs as.
First off, the 'sqbsetlogin' will not clear out your original domain account. It is used to set or clear a SQL Authenticated account for or in use by the SQL Backup Agent service.
To set the account for the SQL Backup Agent service, on the machine where the SQL Backup Server Components were installed open the the services.msc and scroll down to locate the SQL Backup Agent service. If required check the properties of the service and ensure the correct domain account is configured.
SQL Backup also performs something called a 'User Rights Check', this is to ensure that the requestor of the SQL Backup, backup or restore job has permissions to do so.
For ad-hoc tasks recreated using the backup or restore wizard in the GUI, the SQL Backup Agent impersonates the logged-in user of the SQL Backup GUI.
For scheduled backup and restore tasks, the SQL Backup Agent impersonates the scheduled job owner or the account of the SQL Agent service, which SQL Backup uses to trigger the scheduled job. I suspect this is where the job is failing due to the error message:
the database principal 'domain\XXXX' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.
Can you please check to ensure that the job owner and / or SQL Agent is the account that SQL Backup cannot impersonate?
After ensuring that the job owner is correct and you still cannot perform the scheduled task. You can try setting a registry key called 'SkipChecks' using the steps below. The 'SkipChecks' registry key when configured will prevent the 'User Rights Check' from occurring. However, this means that all users with permission to run the sqlbackup extended stored procedure will be able to back up and restore any database on that instance.
To configure the 'SkipChecks' registry key (creating a DWORD registry entry, SkipChecks, with the value 1), please follow the steps below:
1. On the server where the SQL Backup Server Components reside, using the Registry Editor, navigate to the following registry node:
HKEY_LOCAL_MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal<instance name>
where <instance name> is the name of the SQL Instance, local if a default instance name or the name given to a named instance.
2. Creating a DWORD registry key called, SkipChecks, with the value 1
3. Stop and restart the SQL Backup Agent Service.
4. Test to ensure that your scheduled jobs run and complete successfully.
I hope the above helps.
Tips and how-to guides for Redgate products
Ask, discuss, and solve questions about Redgate's tools
Meet us at an event, get sponsored, and join our Friends of Redgate
In-depth articles and opinion from Redgate's technical journal