SQL error 15157: Setuser failed...
mto
Posts: 11
hi everyone!
i'm using SQL Backup 7.3.0.57 with SQL Server 2012. If i try to backup a database with local and network copy, it work but if i schedule a backup with the same options, i get an error message:
I tried the solution of adding the SkipChecks key to the BackupSettingsGlobal(local) registry node explained here http://www.red-gate.com/messageboard/viewtopic.php?t=9343&highlight=skipchecks but it didnt worked.
To make it work, i had to put the SkipChecks key to the namedInstance inside the BackupSettingsGlobal node.
So here's my questions:
1. Is there a reason why the SkipChecks in the root of BackupSettingsGlobal don't work here?
2. Why do we have to do that? I didnt find any documentation that tell us that scheduled backup use the SQL Agent Service account instead of the SQL Backup Agent service account. What is the purpose of this check and why do we need to skip it to solve this? Is it a bug? a feature?
thank you for the help!
i'm using SQL Backup 7.3.0.57 with SQL Server 2012. If i try to backup a database with local and network copy, it work but if i schedule a backup with the same options, i get an error message:
SQL error 15157: Setuser failed because of one of the following reasons: the database principal 'domain\backupserviceaccount' 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.
I tried the solution of adding the SkipChecks key to the BackupSettingsGlobal(local) registry node explained here http://www.red-gate.com/messageboard/viewtopic.php?t=9343&highlight=skipchecks but it didnt worked.
To make it work, i had to put the SkipChecks key to the namedInstance inside the BackupSettingsGlobal node.
So here's my questions:
1. Is there a reason why the SkipChecks in the root of BackupSettingsGlobal don't work here?
2. Why do we have to do that? I didnt find any documentation that tell us that scheduled backup use the SQL Agent Service account instead of the SQL Backup Agent service account. What is the purpose of this check and why do we need to skip it to solve this? Is it a bug? a feature?
thank you for the help!
Comments
1. Using Regedit navigate to the following Registry Folder:
HKEY_LOCAL MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\(LOCAL) or <SQL Instance Name>
The SQL Backup Agent service account has to have SQL Server sysadmin rights in order to perform backups and restores. However, we did not want to allow any account that has rights to run the SQL Backup extended stored procedures (sqlbackup) to be able to back up and restore any database they wanted. We wanted to follow the rights already set up in SQL Server for that account i.e. if user A has only backup rights to databases A and B, they should only be able to back up those databases, and not any other databases.
Thus, when you run the sqlbackup extended stored procedure, SQL Backup will first check the backup/restore rights of the logged on user account running the stored procedure. If that account is a SQL Server sysadmin, no further checks are made. Otherwise, it uses the SETUSER function to impersonate the logged on user account. Unfortunately, this procedure sometimes fails due to an issue known as 'double hop authentication', and has to do with how user security is set up on your Windows domain. You can google for more details on this.
You could set up your SQL Server Agent service to run using an account that's a SQL Server sysadmin to address this issue. However, as that's not always feasible from a security viewpoint, SkipChecks was introduced as another work around. Basically, it tells SQL Server to skip the part where it checks if the logged-on user account has rights to back up and restore the specified databases. You can do this if everyone that has rights to run the sqlbackup extended stored procedure is allowed to back up and restore any database on your SQL Server instance.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
would it be possible to add this option in the GUI? it would be more straightfoward to change the option there than edit the registry manually. Just a suggestion!
alex