How to change the accouunt sql backup uses to perform a backup

Answers

• Mac_F Redgate › Posts: 35 Silver 1

  February 26, 2019 11:14PM

  Hi Doug,

  
  

  The sql backup jobs are ran as the account used for the SQL Backup Agent service.

  You can change the account used as well as the authentication mode for this service.

  
  To change the account used for this service:
  

  1. From the Windows Control Panel, select Administrative Tools > Services, or run services.msc.
  2. Select the SQL Backup Agent service for the SQL Server instance: SQL Backup Agent - <instance name>. 
     Note: The SQL Backup Agent service for the local instance is called just SQL Backup Agent.
  3. Right-click the service and select Properties.
  4. On the Log On tab, specify the account you want the service to log on as. The account must have the permissions listed above. 
  5. Click OK to close.
  6. Right-click the service and select Restart to restart the service and apply your changes. 

  note: You will need to restart the SQL Backup Agent service for the change to take effect.
  
  The rest of the documentation on SQL Backup permissions can be found here
  I hope this helps

  Mac Frierson | Product Support Engineer | Redgate Software
  

  Have you visited our Help Center?
  
  

  0 · Share on Twitter
• asr_tech Posts: 1 New member

  January 8, 2021 2:43PM

  I setup SQL backup with a domain user that I need to change. I cannot figure out where to change it. The SQL Backup Agent is running with the correct account - the Logon account for the service is correct.
  
   I have used the red gate stored proc to attempt to clear out original domain account I setup on install, but the scheduled backups continue to try to use the original domain account setup during installation. I ran this:
  

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

  EXEC master..sqbsetlogin '', ''
  
  the sqsetlogin returns no results/affects no records. 
  
  I tried to schedule a backup and it still tries to use the original domain account I setup during installation.
  
  Error with scheduled backups:
  

  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.

  
  
  
  
  
  
  

  0 · Share on Twitter
• Eddie D Redgate › Posts: 1,807 Rose Gold 5

  January 11, 2021 9:44AM

  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.

  Many Thanks

  Eddie

  Eddie Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter