Unable to get Backup to execute as correct user

gswartwood

WHen I installed the trial of backup 6 on our SQL Server, I mistakenly did not change the services execute as <domain>/SQL-SA. Since we don't have an account by that domain, scheduled backups don't work.

I searched the forums and saw that there was an extended procedure we could "install" and execute to change the login used to run scheduled backups. I used the XP to change the login to a SQL Server account that has the permissions to do backups. That didn't work, the next night's backup still ran as <domain>\SQL-SA.

I un-installed the server side component and the software from my sql server and reinstalled it. THis time I chose to have the backup software service run as local system. I also told it to connect to the software to connect to our sql server as the SQL Server account.

I schedule a new scheduled backup task and this morning, I see that it failed to run and it still tried to run it as <domain>\SQL-SA. I don't know where that came from. How can I change that?

petey

If you were to run an ad-hoc backup using SQL Backup, and not via SQL Server Agent, does it succeed?

Who is the owner of the backup job as defined in SQL Server Agent?

Thanks.

gswartwood

I have not executed the backup manually. I will have to attempt that tonight for I do not want to affect our production DB during the day.

As for the owner of the backup job defined in SQL Server Agent, it is set to the SQL Server account that has permissions to perform backups and not set to <domain>\sql-sa.

petey

You could create a dummy database with the same security settings as your production database, and try manually backing up that database instead.

gswartwood

I was able to run it manually (exact sql generated by Redgate) using the SQL Server account that I told Redgate to use (during server component installation). I also have made sure that owner of SQL Agent Job was the same SQL account. I was able to get the SQL agent job to work if I set the "Run As User" advanced option for the 'execute master..sqlbackup' step within the SQL Agent job to the SQL Account.

I tried using sqbsetlogin again to set the user/password. It doesn't fail, but doesn't give anything for a result either. I then set up another backup job and the new job didn't work even after setting sqbsetlogin. It kept trying to execute the backup as <domain>\sql-sa.

The end result: I can a Red Gate created scheduled job to work if I set the Run As User advanced option within the job. Otherwise, Red Gate keeps using sql-sa.

petey

When you run a SQL Backup backup job via SSMS, SQL Server first checks if the logged-on user has rights to run the sqlbackup extended stored procedure. SQL Backup then checks if the logged-on user has rights to back up the database. If it does, SQL Backup then performs the backup, using the rights of the SQL Backup Agent service startup account (see here for details). Those elements seem to be working fine for you.

When you schedule a job using SQL Server Agent, things are slightly different. The account which the SQL Server Agent uses to log on to SQL Server depends on a few things, which are explained here. The same checks are then performed by SQL Server and SQL Backup as explained above.

In both cases, the SQL Backup Agent will always use its service startup account to log on to SQL Server to perform the backup. However, if you want the SQL Backup Agent to use a SQL Server account (SQL authentication) to log on to SQL Server, you need to use the sqbsetlogin extended stored procedure.

E.g. if instead of the Windows account used to start the SQL Backup Agent service (YOHZ\DBAAdmin), you want to use the SQL account sa, you would need to run the following:

EXEC master..sqbsetlogin 'sa', 'sapassword'

To revert back to using Windows authentication, run the same extended stored procedure with empty parameters e.g.

EXEC master..sqbsetlogin '', ''

gswartwood

Petey, as I wrote in a response above, I have already used sqbsetlogin to specify a SQL account for the backup to use. When I ran the command, I get a result set with a single column named 'result' and a null value in the column. Since the few forum posts that explain how to run the command do not specify what the successful exeecution result value looks like, I assume that the command worked. Running the command did not change the behavior of my problem.

petey

The sqbsetlogin extended stored procedure instructs SQL Backup to use a SQL login to run the backup internally.

E.g.
- SQL Backup Agent service starts as RedDomain\ObiWan
- SQL Backup Agent set up to use SQL login 'sa' to run backups
- user logs on to SQL Server as RedDomain\LukeS
- user runs a backup using the command

EXEC master..sqlbackup '-sql "BACKUP DATABASE model TO DISK = [<AUTO>]"'

What happens next:
- SQL Server checks that RedDomain\LukeS has rights to run the sqlbackup extended stored procedure
- SQL Backup checks that RedDomain\LukeS has rights to back up the model database
- SQL Backup connects to the SQL Server instance as 'sa' to run the backup

To confirm that this is working as described above, start a Profiler trace and run a SQL Backup backup from SSMS. You should see that the initial backup command (master..sqlbackup) should be ran in the context of the logged on user. The actual backup command (BACKUP DATABASE ... TO VIRTUAL DEVICE = ...) will be ran in the context of your SQL Server user.