none
SQL Server Authentication account for backup purpose only.

    Question

  • Hi,

    In SQL Server 2005 SP3 server, I created a Maintenance Plan for backing up database to NAS. The job is working well through SQL Server agent.

    We want to let CA-TNG to schedule the job. So, I created a SQL Server Authentication account "backup" and grant the role "db_backupoperator" from each databases and plus the role "db_dtsoperator" from database MSDB. The "backup" account does not have sysadmin server-wide fix-role.

    The TNG will run the following DOS command to do the backup

    dtexec /U backup /P xxxxxxx /SERVER MyServer /SQL "Maintenance Plans\Full_Backup_to_station" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\Package\Subplan_1.Disable";false /REPORTING E

    I ran the command without problem but the TNG guy had permission problem.
    After I check the evet log on the server "MyServer", I found out it has to logon to database with Windows Authentication account (domain\userid) first (it alway failed), then use the SQL Server authentication ("backup") later.

    Then later, I add the domain userid into SQL Server Security login list without any permission just "public".
    It still does not work. After I grant sysadmin role to this domain\userid on SQL Server, it works.

    It is not my want. The role "db_backupoperator" looks meaningless because it has to logon as sysadmin first.

    Any suggestion? Or what's wrong on my setting?

    Thanks,
    Xiaogang

    Friday, November 20, 2009 4:39 PM

All replies

  • The error message is


    Microsoft (R) SQL Server Execute Package Utility
    Version 9.00.4035.00 for 32-bit
    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    Started:  3:34:22 PM
    Error: 2009-11-24 15:34:25.12
       Code: 0xC002F210
       Source: {F626E129-E346-4157-9F00-58BBB5D5F719} Execute SQL Task
       Description: Executing the query "DECLARE @Guid UNIQUEIDENTIFIER

     EXECUTE msdb..sp_maintplan_open_logentry '{EBAE13B5-A546-4D8E-9B0B-A2953F06C6F3}', '{D7EED3F7-65A0-499D-BC66-F0D38184AADB}',NULL, @Guid OUTPUT

     Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The EXECUTE permission was denied on the object 'sp_maintplan_open_logentry', database 'msdb', schema 'dbo'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
    End Error
    Error: 2009-11-24 15:34:25.90
       Code: 0xC0024104
       Source: {AA94F544-570E-4D5A-90E0-5BB9A91D51B1}
       Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).
    Started:  3:34:22 PM
    Finished: 3:34:25 PM
    Elapsed:  3.078 seconds

     

    Tuesday, November 24, 2009 9:16 PM
  • Hi,

    This sort of error can have different reasons. In order to approach this I suggest that you have a look at the SQL Server errorlogs and SQL Agent Logs to see whether there are entries at the times when the problems were experienced. From there you can then go on looking at those errors. 

    Cheers,
    Ulrike - MSFT

    „This posting is provided "AS IS" with no warranties, and confers no rights.”

    Monday, November 30, 2009 7:54 AM
  • The errorlog just states "BACKUP failed to complete the command BACKUP DATABASE master. Check the backup application log for detailed messages."

    The agent log has nothing because it was not run through agent.

    I know it is related to permissions setting, but I don't know which permission I am missing.

    My scenario is that a domain userid (can connect to database engine without any permission, just belong to "pulic" role) run the following dtexec.exe command using SQL Server authentication account "backup" to run a Maintenance plan. The "backup" account is the member of "db_backupoperator" on each database, and is the member of "db_dtsoperator" on msdb as well.

    The DOS command and error message is like:

    E:\>dtexec /U backup /P xxxxxxxx /SERVER <servername>  /SQL "Maintenance Plans\Backup_to_NAS" /MAXCONCURRENT " -1 " /CHECK
    POINTING OFF /SET "\Package\Subplan_1.Disable";false /REPORTING E
    Microsoft (R) SQL Server Execute Package Utility
    Version 9.00.4035.00 for 32-bit
    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    Started:  3:18:15 PM
    Error: 2009-12-01 15:18:18.31
       Code: 0xC002F210
       Source: {F626E129-E346-4157-9F00-58BBB5D5F719} Execute SQL Task
       Description: Executing the query "DECLARE @Guid UNIQUEIDENTIFIER

     EXECUTE msdb..sp_maintplan_open_logentry '{EBAE13B5-A546-4D8E-9B0B-A2953F06C6F3}', '{D7EED3F7-65A0-499D-BC66-F0D38184AA
    DB}',NULL, @Guid OUTPUT

     Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The EXECUTE permission was denied on the
     object 'sp_maintplan_open_logentry', database 'msdb', schema 'dbo'.". Possible failure reasons: Problems with the query
    , "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
    End Error
    Error: 2009-12-01 15:18:19.10
       Code: 0xC0024104
       Source: {AA94F544-570E-4D5A-90E0-5BB9A91D51B1}
       Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a T
    ransact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).
    Started:  3:18:15 PM
    Finished: 3:18:19 PM
    Elapsed:  3.937 seconds


    Please help to solve the permission problem. Is there any proxy needs to setup?

    Thanks
    Xiaogang

    Tuesday, December 01, 2009 8:45 PM