Collation and Error 880: BACKUP DATABASE permission denied

Stephen LyallStephen Lyall Posts: 19
edited November 21, 2007 10:49PM in SQL Backup Previous Versions
We have SQL Backup 5.2.0.2825 installed and are experiencing an error that appears similar to ones I've seen before in other forum messages.

However the behaviour is a little different. We have a scheduled job that backs up two databases. If the backup of the two databases on this server are run via Query Analyzer (logged in as 'sa') they complete OK.

However if run via a SQL Agent scheduled job, one of the two databases completes OK, but the second errors as follows:

"SQL Backup log file
9/11/2007 12:00:17 a.m.: Backing up STB (differential database) to:
9/11/2007 12:00:17 a.m.: \\kbpdbackup02\SQLBackup\KBPDSQLSTB01\Backup\STB\DIFF_(local)_STB_20071109_000017.sqb

9/11/2007 12:00:17 a.m.: BACKUP DATABASE [STB] TO DISK = '\\kbpdbackup02\SQLBackup\KBPDSQLSTB01\Backup\<database>\<AUTO>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', DIFFERENTIAL, INIT, ERASEFILES = 12h, COMPRESSION = 1

9/11/2007 12:05:17 a.m.: Error 880: BACKUP DATABASE permission denied in database: (STB)
9/11/2007 12:05:17 a.m.: Timeout expired"

The scheduled job is owned by 'sa' (not the service account for the agent)and the job step is run as user 'Self'.

The failing database backup has a collation different from the server collation.

Comments

  • that I have not been able to resolve this, and wonder whether there is a configuration change I need to make?
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Stephen,

    I believe the SQL Backup Agent Service startup account (the Windows account that the SQL Backup Agent is configured to run as) also needs permission to back up the database.
  • Hi Brian,

    The account that is used by the SQL Backup service, the SQL Agent service, and the SQL Server service is the same one (in our case 'CORP\sqlsrv'). It has system administrator server role permissions to all databases by default.

    I have tried getting the SQL Backup Agent to log in using SQL Server authentication by using:

    sp_addextendedproc sqbsetlogin, 'xp_sqlbackup.dll'
    GO
    sqbsetlogin 'sa', 'sqbpassword'
    GO
    sp_dropextendedproc sqbsetlogin
    GO

    This did not resolve the issue.

    I tried this:

    I create two databases. One called SQLBackpTest, and another SQLBackpTest2.

    SQLBackpTest has a collation of SQL_Latin1_General_CP1_CI_AS
    SQLBackpTest2 has a collation of Latin1_General_CI_AS.

    The server collation is Latin1_General_CI_AS.

    A scheduled backup of SQLBackpTest fails with Error 880: BACKUP DATABASE permission denied. A scheduled backup of SQLBackpTest2 succeeds.

    Regards

    Stephen
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Stephen,

    In the SQL Backup activity log in the GUI, is the user who performed the failed backup for the database that generates the 880 error recorded? Who does it say is doing the backup?
  • Hi Brian,

    It has recorded the backup and the error message in the GUI. It shows the correct account attempting the backup 'CORP\sqlsrv', which is the service account that SQL Backup uses.

    Regards

    Stephen
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Stephen,

    After looking at a few similar forum posts, I think the problem may lie outside of SQL Server. First, there is a SQL Backup error code 880 permission denied, then a timeout. Some other issues relating to a failure to get information about a Windows account also exibit SQL Backup error code 880, followed by a 'could not get information about user...' error.

    So I think that the 'timeout' may relate to SQL Server attempting to contact the domain controller to get information about your SQL Server account.

    So what is your Active Directory topology like? Is the SQL Server in the CORP domain, or is it a member of a different domain? If so, what is the trust relationship like? Is CORP\sqlsrv denied query access to Active Directory?
  • Hi Brian,

    I did see those other posts and did wonder if my fault was related, however since it didn't contain any messages containing 'could not get information about user...' I wasn't sure.

    The server that SQlBackup is running is in the CORP domain.

    If it is timing out because of network topology am I wrong in pursuing the collation difference theory? I would have expected for an AD related the failure to be consistant for any backup, not just against any database with a different collation to the server. If I create other databases for testing this all that have a collation the same as the server succeed, all that have different collation fail.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    I don't see how the collation would affect permissions, unless SQL Backup does something with the user data in a temporary table (tempdb) and can't construct a join because the collation is different from the master database or something.
  • The only collation problem I could see was http://www.red-gate.com/support/kb/KB200707000071.htm, however we are running a version higher.

    Is there any further logging that I can do do provide more information?

    The backups do work when the same script is run from query analyzer.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    What is the database context for the job? Possibly changing it to master or some other database would help?
  • I changed the database to master, and fully qualified the stored procedure that calls SQLBackup. Works fine for one db, but not the other.
  • I ran profiler during to see what its doing. It executes this piece of code:

    "-- Testing for BACKUP rights.
    -- Work from server role to database role, to BACKUP DATABASE execution and restriction rights.
    -- # Aliases are not supported.
    DECLARE @hasrights integer

    SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')

    IF (@hasrights = 0) OR (@hasrights IS NULL)
    BEGIN
    SETUSER 'CORP\sqlsrv'

    -- As long as the user has sysadmin server role, he can backup the database.
    SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin')
    IF (@hasrights = 0) OR (@hasrights IS NULL)
    BEGIN
    -- This has to be done because IS_SRVROLEMEMBER does not check recursively if a login name is provided.
    USE [STB]
    DECLARE @login_type integer
    DECLARE @login_name sysname

    -- No sysadmin rights. Check for database db_owner role.
    SELECT @hasrights = IS_MEMBER('db_owner')

    -- Check for database db_backupoperator role.
    IF (@hasrights <> 1)
    BEGIN
    SELECT @hasrights = IS_MEMBER('db_backupoperator')
    END

    -- No db_backupoperator role. Check BACKUP DATABASE execute rights.
    -- Quick check for explicit rights.
    IF (@hasrights = 0) OR (@hasrights IS NULL)
    BEGIN
    SELECT @hasrights = 1 WHERE EXISTS
    (
    SELECT 1 FROM sysprotects a
    INNER JOIN sysusers b ON a.uid = b.uid AND b.name = 'CORP\sqlsrv'
    WHERE a.action = 228
    AND a.protecttype IN (204, 205)
    )

    IF (@hasrights = 0) OR (@hasrights IS NULL)
    BEGIN
    -- No direct rights. Now need to iterate and check all Windows groups and SQL Server roles.
    DECLARE cur_rights CURSOR FOR
    SELECT (CASE WHEN b.isntgroup = 1 THEN 1 ELSE CASE WHEN b.issqlrole = 1 THEN 2 ELSE 0 END END) type, b.name
    FROM sysprotects a
    INNER JOIN sysusers b ON a.uid = b.uid
    WHERE a.action = 228
    AND a.protecttype IN (204, 205)
    AND (b.isntgroup = 1 OR b.issqlrole = 1)

    OPEN cur_rights

    FETCH NEXT FROM cur_rights INTO @login_type, @login_name
    @FETCH_STATUS = 0
    BEGIN
    SELECT @hasrights = IS_MEMBER(@login_name)
    IF @hasrights = 1
    BEGIN
    BREAK
    END

    FETCH NEXT FROM cur_rights INTO @login_type, @login_name
    END

    CLOSE cur_rights
    DEALLOCATE cur_rights
    END
    END

    -- for database level rights, need to check if DENY restriction exists
    IF @hasrights = 1
    BEGIN
    SELECT @hasrights = 0 WHERE EXISTS
    (
    SELECT 1 FROM sysprotects a
    INNER JOIN sysusers b ON a.uid = b.uid AND b.name = 'CORP\sqlsrv'
    WHERE a.action = 228
    AND a.protecttype IN (206)
    )

    IF @hasrights = 1
    BEGIN
    -- No explicit DENY restrictions. Check via group / role membership.
    DECLARE cur_rights CURSOR FOR
    SELECT (CASE WHEN b.isntgroup = 1 THEN 1 ELSE CASE WHEN b.issqlrole = 1 THEN 2 ELSE 0 END END) type, b.name
    FROM sysprotects a
    INNER JOIN sysusers b ON a.uid = b.uid
    WHERE a.action = 228
    AND a.protecttype = 206
    AND (b.isntgroup = 1 OR b.issqlrole = 1)

    OPEN cur_rights
    FETCH NEXT FROM cur_rights INTO @login_type, @login_name
    @FETCH_STATUS = 0
    BEGIN
    SELECT @hasrights = IS_MEMBER(@login_name)
    IF @hasrights = 1
    BEGIN
    SET @hasrights = 0
    BREAK
    END

    FETCH NEXT FROM cur_rights INTO @login_type, @login_name
    END

    CLOSE cur_rights
    DEALLOCATE cur_rights
    END
    END
    END

    SETUSER
    END

    DECLARE @dbname sysname
    SELECT @dbname = name FROM master..sysdatabases WHERE name = N'STB'

    IF @hasrights = 1
    SELECT CAST(1 AS int) AS hasrights, @dbname AS name
    ELSE
    SELECT CAST(0 AS int) AS hasrights, @dbname AS name"


    I cut and pasted it into Query Analyzer. If you are in the database (in the example above its STB) itself it runs ok. If you are anywhere else it eventually times out.
  • peteypetey Posts: 2,358 New member
    Could you please try to identify which command(s) are causing the timeout? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • If the "USE [Database]" statement is removed (line 17?) the code will execute correctly.

    What is interesting is that the sode below the IF statement is being executed when it shouldn't.

    If you run "DECLARE @hasrights integer

    SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin', 'CORP\SQLSRV')

    PRINT @hasrights -- I added this to see what the result is"

    You get 1 as the result of the PRINT. On that basis the "IF (@hasrights = 0) OR (@hasrights IS NULL)" should not be exeuted. However it is.

    I am logged on to the server as 'CORP\sqlsrv'. So even though that account is a sysadmin it is not being recognised as such.
  • peteypetey Posts: 2,358 New member
    Do you know why the USE [Database] statement would cause a timeout? If you were to run that script so that it times out, and while it's running, run sp_who2 from another session, is that script blocked by anything (see the BlkBy column)?

    As for the rights issue, that script is ran in the context of the SQL Backup Agent service startup user, not the user that is running the backup command. The SQL Backup Agent service startup user may be receiving different results from the IS_SRVROLEMEMBER function. Try logging in to SQL Server using the same user as the SQL Backup Agent service startup user, run the script, and see if IS_SRVROLEMEMBER produces the same result.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • The account 'CORP\sqlsrv' runs the SQL Backup service, the SQL Server service, and the SQL Agent service.

    So I signed onto the server as 'CORP\sqlsrv'.

    And then execute the code posted previously from query analyzer.

    In each case I start in the master database.

    By changing the line 'USE [<database name>] ' to each of the databases on the server in turn the script runs ok, except for two databases.

    sp_who2 shows the process as runnable. There is no blocking at all on the server.
  • peteypetey Posts: 2,358 New member
    If you were to log on to the instance using Query Analyzer as COPR\sqlsrv, and run USE [<database name>] using a database name that causes the timeout, does it also run indefinitely? I would like to determine if the timeout happens only when the USE command is ran as part of the SQL Backup script.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Excellent thought.

    I did as you asked:

    use _dba
    use master
    use model
    use msdb
    use SQLBackpTest
    use SQLBackpTest2
    use STB

    Completes successfully.
  • peteypetey Posts: 2,358 New member
    And something like this would cause the timeout ?

    SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
    SETUSER 'CORP\sqlsrv'
    USE [STB]
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • No it didn't. I ran:

    SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
    SETUSER 'CORP\sqlsrv'
    use _dba

    SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
    SETUSER 'CORP\sqlsrv'
    use master

    SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
    SETUSER 'CORP\sqlsrv'
    use model

    SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
    SETUSER 'CORP\sqlsrv'
    use msdb

    SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
    SETUSER 'CORP\sqlsrv'
    use SQLBackpTest

    SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
    SETUSER 'CORP\sqlsrv'
    use SQLBackpTest2

    SELECT IS_SRVROLEMEMBER('sysadmin', 'CORP\sqlsrv')
    SETUSER 'CORP\sqlsrv'

    use STB

    And all gave a result of '1'.
  • peteypetey Posts: 2,358 New member
    Ok, so it wasn't the USE statement that was causing timeout. Instead, not switching to the STB database allowed the rest of the script to run successfully.

    Could you please try to identify which part of the script, after the USE statement, times out, when logged on as the COPR\sqlsrv user?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.