Post reply

Is there a better way to run SSIS packages on-demand?

  • March 16, 2016 at 7:47 am

    #309124

    Currently, we have several applications which use SSIS packages that need to be called on-demand. The way we are currently accomplishing this is, we create a dedicated account, grant it access to the SSIS proxy account and SQLAgentUser privileges in MSDB and ownership of the relevant job(s). The application then, when needed, calls a stored procedure that executes as this dedicated account and Bobs your uncle.

    My question is, is there a more "elegant" way to accomplish the same thing, without granting the application login to many privileges? This feels somewhat "kludgy" and "fragile" to me, although we've not had a problem so far with the customers who are utilizing it (separate proxy accounts, etc for the different applications.)

    Thanks all,

    Jason A.

  • March 16, 2016 at 8:59 am

    #1865631

    Hi Jason

    I'm doing it in other way, there is a "queue" table where users (applications) can insert a task to do.

    I created a Sql Server Agent job, it fires every minute during the business hours and

    checks if there any new task appeared.

    When true then call appropriate SSIS package (depending on parameters).

    I don't have to grant many privileges and it also prevents running to many packages in the same time.

    Br,

    Mike

  • March 16, 2016 at 9:22 pm

    #1865787

    My "more elegant" method is to remove all but PUBLIC and CONNECT privs from the users/applications and force them to call stored procedures (which they've been given execute privs on) not only for this type of thing, but for everything. But, I digress into wishful thinking. 😀

    On the serious side, I wouldn't give a user or application the privs to run a job directly. Neither would I give them the ability to run an SSIS package directly. I would, in fact, use a stored procedure for both scenarios.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 17, 2016 at 7:13 pm

    #1866044

    Jeff Moden (3/16/2016)

    On the serious side, I wouldn't give a user or application the privs to run a job directly. Neither would I give them the ability to run an SSIS package directly. I would, in fact, use a stored procedure for both scenarios.

    So really, it sounds like about the same setup (correct me if I'm misunderstanding.)

    In my case, the users have zero access to SQL (the applications' account establishes the connection, the application determines the users privs within the app,) the application account has zero access to SQL Agent, instead calling a stored procedure which uses EXECUTE AS to run as an account which *does* have the requisite privileges to fire an Agent job which uses a proxy to run the SSIS package.

    So, yeah, there's 3 accounts involved here:

    1. The applications login to SQL (public role only)

    2. The EXECUTE AS account (public server role, SQLAgentUser role in MSDB, and access to the appropriate proxy credential)

    3. The proxy account for the credential (again, minimal privileges)

    Yes, the majority of the applications are using SQL Logins, not Windows auth, that's due to limitations of the front end software (ColdFusion)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply