none
Credential can't see dtsx package in SSISDB RRS feed

  • Question

  • Hello,

    I have the error message bellow when the SQL Agent JOB executes an SSIS package.

    the step run under a proxy that holds a service account as credential.

    Executed as user: XXXX\svc_internalbi. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  12:06:18  Failed to execute IS server package because of error 0x80131904. Server: XXXXXXX1, Package path: \SSISDB\INTERNALBI\SRMBD\SRMBD - load targets.dtsx, Environment reference Id: 1.  Description: Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.  Source: .Net SqlClient Data Provider  Started:  12:06:18  Finished: 12:06:18  Elapsed:  0.063 seconds.  The package execution failed.  The step failed.

    => when the login (credential) is sysadmin : the job run successfuly

    => I found roles in SSISDB, when the login is ssis_admin : the job also run succesfuly

    => db_datareader & db_datewriter : it fails with the same error message

    I already set permissions on folder / environnement & project of the SSIS catalog.

    could you please advise of what permissions must be set for the login to be used as proxy account please ?

    Thank you

    Stéphane

    Tuesday, December 4, 2012 11:12 AM

Answers

  • Hi Stéphane Guilleminot,

    To allow a login to be able to read/execute only one project, but not able to access other objects (projects or environments) in a folder where the project is in: 
    a.Map it to a member of the SSISDB database role -- public. (This leverages SQL security mechanism.)
    b.Grant it Read to the folder, and grant it Read/Execute to the project. (This uses SSIS Catalog security mechanism.)

    For more information about it, please refer to:
    http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx

    Thanks,
    Eileen

    If you have any feedback on our support, please click here



    Eileen Zhao
    TechNet Community Support


    • Edited by Eileen Zhao Friday, December 7, 2012 1:03 AM
    • Marked as answer by Eileen Zhao Wednesday, December 12, 2012 1:03 AM
    Thursday, December 6, 2012 2:41 PM

All replies

  • Hi,

    have you tryed setup permissions on Integration services Catalog on project folder (not on DB but on SSIS catalog)?

    Zdenek


    Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu

    Tuesday, December 4, 2012 12:05 PM
  • Yes, as I said : Ialready set permissions on folder / environnement & project of the SSIS catalog

    Tuesday, December 4, 2012 12:10 PM
  • Hi Stéphane Guilleminot,

    To allow a login to be able to read/execute only one project, but not able to access other objects (projects or environments) in a folder where the project is in: 
    a.Map it to a member of the SSISDB database role -- public. (This leverages SQL security mechanism.)
    b.Grant it Read to the folder, and grant it Read/Execute to the project. (This uses SSIS Catalog security mechanism.)

    For more information about it, please refer to:
    http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx

    Thanks,
    Eileen

    If you have any feedback on our support, please click here



    Eileen Zhao
    TechNet Community Support


    • Edited by Eileen Zhao Friday, December 7, 2012 1:03 AM
    • Marked as answer by Eileen Zhao Wednesday, December 12, 2012 1:03 AM
    Thursday, December 6, 2012 2:41 PM