none
Error creating a maintenance plan RRS feed

  • Question

  • Hi,

    please help this is really urgend!

    we migrated a SQL 2005 with ADMT from one AD to another. After migration the database works fine, but now we found out that there is a problem with the maintenace plans. we are unabel to create or run a job. when we try to create one, we receive an eror "Apply to target server failed for Job...", when we try to run a job we get "NON-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account..."
    We checked the SQL Server Account and the SQL Agent Account.

    Hope somebody can assist here.

    best regards
    rené
    Friday, September 18, 2009 3:17 PM

Answers

  • Hi,

    This is most likely a known issue in SQL Server 2005, and a KB article has
    been released for this issue. There are five approaches available to fix
    this issue:
    Method 1: Use a SQL Server Agent proxy account
    Create a SQL Server Agent proxy account. This proxy account must use a
    credential that lets SQL Server Agent run the job as the account that
    created the package or as an account that has the required permissions.
    This method works to decrypt secrets and satisfies the key requirements by
    user. However, this method may have limited success because the SSIS
    package user keys involve the current user and the current computer.
    Therefore, if you move the package to another computer, this method may
    still fail, even if the job step uses the correct proxy account.


    Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
    Change the SSIS Package ProtectionLevel property to ServerStorage. This
    setting stores the package in a SQL Server database and allows access
    control through SQL Server database roles.


    Method 3: Set the SSIS Package ProtectionLevel property to
    EncryptSensitiveWithPassword
    Change the SSIS Package ProtectionLevel property to
    EncryptSensitiveWithPassword. This setting uses a password for encryption.
    You can then modify the SQL Server Agent job step command line to include
    this password.

    Method 4: Use SSIS Package configuration files
    Use SSIS Package configuration files to store sensitive information, and
    then store these configuration files in a secured folder. You can then
    change the ProtectionLevel property to DontSaveSensitive so that the
    package is not encrypted and does not try to save secrets to the package.
    When you run the SSIS package, the required information is loaded from the
    configuration file. Make sure that the configuration files are adequately
    protected if they contain sensitive information.

    Method 5: Create a package template
    For a long-term resolution, create a package template that uses a
    protection level that differs from the default setting. This problem will
    not occur in future packages.

    For more detailed information, please refer to this article for the
    resolution:
    An SSIS package does not run when you call the SSIS package from a SQL
    Server Agent job step
    http://support.microsoft.com/default...b;EN-US;918760

    Please let me know if you have any further questions on this.

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP
    Monday, September 21, 2009 3:00 PM