Red Gate forums :: View topic - SQL error 15157: Setuser failed...
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

SQL error 15157: Setuser failed...

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
mto



Joined: 04 Apr 2013
Posts: 8

PostPosted: Fri Apr 05, 2013 6:09 pm    Post subject: SQL error 15157: Setuser failed... Reply with quote

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:

Quote:
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!
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Mon Apr 08, 2013 1:49 am    Post subject: Reply with quote

Quote:
1. Is there a reason why the SkipChecks in the root of BackupSettingsGlobal don't work here?

SkipChecks needs to be in an instance's node. The link you referred to mentions the same thing i.e.

1. Using Regedit navigate to the following Registry Folder:
HKEY_LOCAL MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\(LOCAL) or <SQL Instance Name>

Quote:
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?

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.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
mto



Joined: 04 Apr 2013
Posts: 8

PostPosted: Thu Apr 11, 2013 4:04 pm    Post subject: Reply with quote

thank you for the info!

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group