February 27, 2009 at 7:36 am
We have an SSIS package that runs nightly and typically gives us no problem. Once in a while, one of the analysts gives the DBA's a call and asks us to run it during the day (the package syncs up some data between two servers). The other day, all the DBA's were not available: a couple of us were in a meeting, the rest were out of the office. So of course they were panicking and e-mails flying. To make a long story short, we're considering having this package run by an external scheduler we use, Control-M, and if they need to run it separately in the afternoon, they can call the support desk which manages the Control-M job.
My question is, what are some of the security issues to be aware of in terms of setting the minimally appropriate security levels to run this package?
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
February 27, 2009 at 11:02 am
First question is where is the package stored - in SQL Server or in the file system? If it's in SQL Server, you can take advantage of the SSIS database roles in msdb. See "Integration Services Roles" in BOL. I'm not familiar with Control-M, but I suspect you could use teh dtexec utility to execute the package.
Greg
February 27, 2009 at 11:18 am
Greg Charles (2/27/2009)
First question is where is the package stored - in SQL Server or in the file system? If it's in SQL Server, you can take advantage of the SSIS database roles in msdb. See "Integration Services Roles" in BOL. I'm not familiar with Control-M, but I suspect you could use teh dtexec utility to execute the package.
Thanks Greg. The package source is SSIS Package Store, which points to a .dtsx file in C:\SSIS. I was thinking a wrapper stored proc that runs dtsexec, and then grant the user(s) and/or appropriate service account, permission to execute that wrapper proc. Will keep digging in BOL as well.
Cheers.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
February 28, 2009 at 9:39 pm
If you already have an external scheduler, is there a reason to run it from within SQL Server? You could set up a batch job to call the package. Most schedulers allow you to set the credentials under which the job executes. I'm sure the same is true of yours. If that's the case, you just have to make sure it's an account that has the appropriate rights to do in SQL Server whatever the package is going to do as well as has rights to get to and execute the package itself.
K. Brian Kelley
@kbriankelley
March 2, 2009 at 6:09 am
K. Brian Kelley (2/28/2009)
If you already have an external scheduler, is there a reason to run it from within SQL Server? You could set up a batch job to call the package. Most schedulers allow you to set the credentials under which the job executes. I'm sure the same is true of yours. If that's the case, you just have to make sure it's an account that has the appropriate rights to do in SQL Server whatever the package is going to do as well as has rights to get to and execute the package itself.
Ahhh...in the words of old Bill Shakespeare, "There's the rub". This is probably the most likely route we'll go through, so just have to test it in a QA environment first to make sure. It's those damn permissions, LOL.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply