Error 880 permission problem with scheduled backup

nmalhotnmalhot Posts: 3
edited February 17, 2013 8:17PM in SQL Backup Previous Versions
Hi. Been a long time SQL Backup user ... maybe too long because having a brain fart with just basic setup:

New Windows Web Server 2008 64-Bit Server and new SQLBackup 6.1 installed.

Just want to test my installs and doing a backup on the master db from within SQL Backup works fine. Trying to make a scheduled backup within SQL Backup gives me the following error:

7/30/2009 4:20:25 PM: Backing up master (full database) to:
7/30/2009 4:20:25 PM: D:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Backup\FULL_(local)_master_20090730_162025.sqb

7/30/2009 4:20:26 PM: Error 880: BACKUP DATABASE permission denied in database: (master)
7/30/2009 4:20:26 PM: SQL error 15157: Setuser failed because of one of the following reasons: the database principal '28455-DB2\MSSQL_USER' 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.

The SQL Backup Agent service is running under Administrator and when I connect to MS SQL Manager I use windows authentication and as administrator, that works fine also.

The Activity History in SQL Backup also shows Successful backups on the master db from the MS Backup plan for the same 28455-DB2\MSSQL_USER User Name that is showing as a failure with the SQL Backup scheduled backup.

Any thoughts on what I'm doing wrong?
«1

Comments

  • Anu DAnu D Posts: 876 Silver 3
    Thanks for your post and sorry that this problem has occured.

    If this is a scheduled job, SQL Backup relies upon the SQL Server Agent to trigger the job. The SQL Backup Agent Server connects to the SQL Server and checks to see if the user account for the SQL Server Agent has permissions to perform the backup using the setuser command.

    SQL Backup can be configured not to perform these additional security checks by adding the SkipChecks registry key, as follows:

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

    2. Create a new DWORD type registry key called SkipChecks and give this new key a data value of 1.

    Once the registry key has been created, SQL Backup will skip the user right checks for backup and restore tasks.

    Kindly let us know if this resolves the issue.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • I am running Windows 2008 Server x64, SQL Server 2008, and SQL Backup v6.1

    I Added the suggested key:

    [HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\(LOCAL)]
    "SkipChecks"=dword:00000001

    I am still unable to execute scheduled backups with the following error message appearing for every attempted database backup in the redgate logs:
    8/3/2009 7:06:39 PM: Backing up model (transaction log) to:
    8/3/2009 7:06:39 PM: E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\LOG_(local)_model_20090803_190639.sqb

    8/3/2009 7:06:39 PM: Error 880: BACKUP DATABASE permission denied in database: (model)
    8/3/2009 7:06:39 PM: SQL error 15157: Setuser failed because of one of the following reasons: the database principal 'SERVER\MSSQLAGENT' 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.

    As I am sure you are aware, MSDN suggests of SQL 2008 (http://msdn.microsoft.com/en-us/library/ms186297.aspx):
    SETUSER is included for backward compatibility only. SETUSER may not be supported in a future release of SQL Server. We recommend that you use EXECUTE AS instead.

    The article also states:
    Only use SETUSER with SQL Server users. SETUSER is not supported with Windows users.

    Do I need to change my SQL Agent account to run under a SQL Server User? Do you have any other suggestions?

    Thank you.
  • Anu DAnu D Posts: 876 Silver 3
    I am sorry I skipped the point that it is 64-bit server, you will then need to use the following registry node:

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\(local) or SQL Instance Name.

    Create a new DWORD type key called SkipChecks and give the key a data value of 1.

    Kindly let us know if this fixes the issue.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • The registry key fixed my problem. Thanks!
  • Fixed me, too. Thank you for the quick response!
  • Anu DAnu D Posts: 876 Silver 3
    Many thanks for the confirmation.
    We are pleased that your issue is resolved :)
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • If this doens't work, I find this solves 80% of all such problems:

    Run the Red Gate SQL Backup agent as Local System. Job done.
  • Hi guys,

    Got exactly this problem but my server doesn't have the registry key you mentioned! So, what?

    Mi SQL Server machine is running on Windows Server Web Service Pack 2 and is a 64 Bit Operating System.

    Please help!

    Thanks in advance.

    Cheers, AJ
  • peteypetey Posts: 2,358 New member
    If you're using version 6.4, the 'SkipChecks' registry entry should be created in the 'HKEY_LOCAL MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\(LOCAL) or <SQL Instance Name>' node.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • That worked fine!

    Thank you very much.

    Best regards,

    AJ
  • We are using the 6.4 version and this solution works great for us.

    Cheers.
  • That's working great!

    I tried to find this fix for a long time , ha ha.

    Thanks.
  • +1 on this solution working on Server 2008 x64 / SQL 2008R2.

    I would suggest that Red Gate make this the default--as I don't understand the value of the broken functionality that this registry setting disables.

    At a minimum, Red Gate should move the setting from the registry to a configuration setting accessible from the application, and should provide a link or a reference from the error message to that configuration setting.
  • I've been trying to solve this problem for a day or so now!

    Come on Redgate this needs to be addressed! Wasted a lot of time thinking it was a permission issue when actually it isn't.

    This registry fix resolved my problem Windows 2008 R2 Standard x64 & SQL 2008 R2 SP1.
  • The problem is getting information from the Windows Active Directory Server to the SQL Server. The SETUSER function is provided by SQL Server. So it's either a network/firewall issue or a broken SQL Server as far as I know. Not one line of this is Red Gate's code.
  • Brian,

    It is not a firewall issue nor a broken SQL Server. This only affects SQL 2008 and higher instances running on Server 2008 and higher.

    My opinion is that the problem is with Red Gate's reliance on SETUSER and SQL 2008 and higher on Windows Server 2008 and higher usage of Service SIDs.

    From MSDN http://msdn.microsoft.com/en-us/library/ms143504.aspx:
    SQL Server enables per-service SID for each of its services on Windows Server 2008 or Windows Vista operating systems in SQL Server 2008 R2 to provide service isolation and defense in depth. The per-service SID is derived from the service name and is unique to that service. For example, a service SID name for SQL Server service might be NT Service\MSSQL$<InstanceName>. Service isolation enables access to specific objects without the need to run a high-privilege account or weaken the security protection of the object. By using an access control entry that contains a service SID, a SQL Server service can restrict access to its resources.

    In SQL 2005, if you used domain accounts for the SQL services the local groups COMPUTER\SQLServer2005MSSQLUser$ComputerName$MSSQLSERVER and COMPUTER\SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER
    would get created and they would contain those domain service accounts. Those groups were granted Sysadmin rights on the SQL 2005 instance.

    In SQL 2008 and higher, when running on Windows 2008 and higher, the situation changes. Those local groups are still created and granted Sysadmin rights, but if you look at the members of those local groups, you won't find the domain accounts listed as members. Instead you will find the Service SIDs created during setup, listed as 'NT SERVICE\MSSQLSERVER (SID GUID)'

    This is actually a good thing! The SQL Agent account still has Sysadmin rights so there should not be a permissions issue... except if you use a deprecated function such as SETUSER which is not able to handle this scenario.

    So IMHO it is a problem that Red Gate needs to address with their product. As other users here, I wasted a lot of time trying to figure out a permissions issue that didn't exist. For you to put the onus back on your customers comes across as arrogant and insulting. We know what we're doing.
  • Sorry, did not mean to come across as arrogant. But for the record, we do know about this and are looking into it. Unfortunately my mandate as support engineer only stretches as far as upchanneling these design shortcomings and trying to propose a workaround.
  • Don't know if it's in this thread anywhere, but you can try setting the skipchecks registry key as described here:
    http://www.red-gate.com/MessageBoard/vi ... skipchecks
  • I am receiving this error. However, I do not have the registry path referenced. I installed SQL Backup 6.5.1.9 via SQL Toolkit. I tried to create the path in my registry anyway, but it didn't work. I have 64-bit SQL Server 2008 R2 on Windows Server 2008 R2.

    Can you tell me how to fix this? Thanks.
  • peteypetey Posts: 2,358 New member
    Please add the SkipChecks registry entry to the following node:

    HKEY_LOCAL MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<SQL Instance Name>

    regardless of whether it's a 32-bit or 64-bit SQL Server instance.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Thanks, Petey, for your advice. I created that path with the name of my server as the SQL Instance Name since I have a named default instance. However, I still get the same error.
  • peteypetey Posts: 2,358 New member
    I created that path with the name of my server as the SQL Instance Name since I have a named default instance.

    You need to use the name of the instance, not that of the server. E.g. if your instance name is 'PRODINST', your registry node would be

    HKEY_LOCAL MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\PRODINST

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • SELECT SERVERPROPERTY('instancename') results in NULL.

    I substituted the name that shows in Object Explorer for the Server Name.

    I also tried the value that shows in the registry under Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL, which has a DWORD of MSSQLSERVER with a value of MSSQL10_50.MSSQLSERVER. I tried this last value of the DWORD. It did not work.

    I still get the same error when I run the Job.
  • peteypetey Posts: 2,358 New member
    If it's the default instance, use

    HKEY_LOCAL MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\(local)

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I thought I read that I needed to use the Wow6432Node key since I have 64-bit Windows and 64-bit SQL Backup.

    The key I'm filling is this.

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\BackupSettingsGlobal\<Instance Name> with DWORD SkipChecks with a value of 1. I've also tried QWORD SkipChecks with a value of 1.

    Thanks for your help.
  • I found the registry key you mentioned without the Wow in the path, and it worked!!!

    Thanks, Petey!!!
  • peteypetey Posts: 2,358 New member
    Glad to hear it's working for you now.

    From version 6.4 onward, SQL Backup stores its settings in the HKEY_LOCAL MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\ node, even on 64-bit operating systems.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Good to know and thanks again!!
  • petey thank you for that information.

    Is this fixed in releases later than 6.5.1.9?
    I spent a day on this issue and I am sure there are a lot more out there that have done the same thing.

    I have a lot of servers that I will be migrating to SS 2012 and I would rather not make it a common practice to modify registry settings for all of my installs.
    Please advise of an ETA for the solution.
    Thank You
  • So ...

    On an active / passive 2008R2 Windows Cluster, RedGate 7 ... do we have to change registry settings on both nodes or just the active one?

    I actually went to the passive one (the one who does not own the SAN) and while I can see the registry keys, everything is empty there.

    Any ideas?

    I am not a big fan or messing around with registry, especially on a Win Cluster, so I did not alter the passive node.

    Thanks,
Sign In or Register to comment.