Run a DTS package on-demand by non sysadmin & no proxy user?
-
Got a bit of a problem that I hope there's a good solution to. I have a DTS package that has to be able to be run on-demand by a "normal" user. It can't be scheduled - has to happen on-demand. I don't want to put the user that calls this in the sysadmin role. We also are not running using that proxy setting, and I'm not sure if our network admin is going to let that happen - doesn't seem very keen on it.
I'm not sure how to give a user the ability to run a DTS package on-demand without making them a sysadmin. I tried making a stored proc that creates a job with a CmdExec step calling dtsrun.exe, but after sp3, they can't do that unless they're in the sysadmin role. I tried creating a job owned by an admin, and creating a stored proc to call sp_start_job on it, but I then found out a non-admin can't call a job owned by another user.
Is there any way to set permissions to allow a non-admin user to start a job using a trusted connection from an application on our Intranet? Having a hard time figuring this one out! I wish there was a way within transact-SQL to just say "start this package", rather than having to hop out to the OS and therefore require admin privileges to do it. I've searched around, and it seems like everybody suggests setting up that proxy user, but that seems like a little much to have to do for this one simple project!
Thanks in advance for your help!!!
-
I think you could create a very small app that call the said package(s). Then you can give access to the sysadmin group to the application. If the application doesn't use parameters (hear dynamic sql) to call the packages, then there's no security risk and they can call the package whenever they need to.
-
That's a pretty good idea. I guess you're talking about using SQL security for it and hardcoding a sql name & password in the application. Not a huge fan of having a sysadmin name & password in plaintext laying around in some source code, but I could probably get away with it.
One other thing I just thought of is I can create a table with some basic audit fields in it like "dtLastRun", "userLastRun", etc, as well as a "run" bit field. I can have a job owned by a member of sysadmin run every few minutes and check that table. If the run flag is set, it'll kick off the dts package, probably using a CmdExec step. Otherwise it'll just exit. I'll have a stored proc that will set that run flag and will only need to give the end user permission to that proc. After the job completes, I can have it send an email notification, so the user will know it ran. I'm sure I can make something like that work. Not a big fan of having a job running & polling a table every few minutes, and it seems like kind of a round-about way to do it, but I'm sure I could get it to work. Maybe I could have a trigger on that table do a "sp_start_job" to get the package running so I wouldn't have to do the polling. Though I might be right back to my original problem if I do that - what security context does a trigger run under, any idea?
-
I'd go with the best practice that says you shouldn't start an external process in a trigger (mail/dts ...).
You're idea is not bad, but do the users need to run the job as soon as they hit the button, or can it wait a few minutes??.
If they can wait, then definitly go with that. Else maybe there's a way to have the application group have a little less permissions and still do what it needs to. Then you could grant permission on 1 proc and deny the rest of the db to 'em (not sure it's possible).
-
I had a situation where I needed to have certain users be able to kick off a DTS package frm a web application. I wanted the DTS package to run on the SQL Server, not the web server, so I couldn't instanciate the DTS package like a lot of the guides (http://www.sqldts.com) have you do.
Here's how I did it. I created a job to run the DTS package. Then I created a new error (has to be numbered over something like 50,000, I believe). Then I created an alert based on the error number. The Alert Action was to run the job that executed the DTS package. It runs under the SQL Server Agent context and works great.
-
Nice trick... I assume that this is well documented because it's not the first place I'd be searching if a bug was found in the application
. -
Yep. It was at a former place of employment, and I left them with documentation about the process (and there were comments everywhere in the code and job stepsm,e etc.). No one has called me yet (crossing fingers)!
-
Why would they call you if you don't work there anymore??
They'd have to keep me on payroll to keep me on call
. -
That would be when I would quote my hourly rate (including travel time).
No really, if it was someone who was cool while I was there, I'd just answer them. If not, out comes the Contract/Rate discussion.
Occasionally I have someone call from a company that I was at several years ago (two jobs back). I've always been a permanent employee, and I left this job April 2003. I've always been nice unless the person asking the question wasn't.
-
As always, I guess it depends
. -
Thanks for all the help, by the way. I like the alert idea, but I think I'm going to just make a table for pending package runs, and have a job looking at it every few min's. It doesn't have to be run immediately, so that should be good enough. My only fear with the alert idea is that the logic gets buried in a somewhat obscure location. But, if it's determined that the end users can't wait a minute or two for their package to get run, an alert I'll be makin'!
-
swayzack,
That's exactly how I set up an application recently where users wanted to export data on demand. My "run request checker" job runs every 10 minutes between 5:00 AM and 5:00 PM when the the users are working. It's been working for over a month with no problems.
Greg
Greg
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply