Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 xp_cmdshell 'DTSrun ...' or sp_start_job
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aged Yak Warrior

571 Posts

Posted - 11/27/2003 :  10:15:38  Show Profile  Reply with Quote
I need to run a DTS package from an administrtive web page. But what is the security context need here. IIS 5.1 on one box. SQL on a different one.

If I create a login for 'job running' then establish the connection string through that login should I not be able to call msdb..sp_start_job from a proc. In general I would like to keep the anonymous accounts away from any kind of permissions on SQL.
The other ways are...
run a shell on the iis box then use DTSRUN.exe but I gather that means generally install client tools on the IIS machine or just the DTSRUN distributable. ALong the same lines once the DTSrun registration has occured on the IIS box you would have the DTS objects available. Both these ways seem wrong. I would just like to work out the permissions situation to call sp_start_job correctly from a proc. I understand there are some proxy account situation going on here or something.

What is the lowest impact way to do this.

wrong quote was here

Interesting though is I can't find xp_sqlagent_proxy_account on SQL 7

Tenacious O

Edited by - Sitka on 11/27/2003 13:08:31

Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 11/27/2003 :  21:46:03  Show Profile  Visit Merkin's Homepage  Reply with Quote
I would suggest that the login your IIS is using has no direct access to MSDB, but have one stored proc that will call msdb..sp_start_job. Keep the DTS stuff on the SQL box.

Go to Top of Page

Aged Yak Warrior

571 Posts

Posted - 11/28/2003 :  07:58:29  Show Profile  Reply with Quote
Yep, that was what I wanted to do. It's not working for some reason. Getting sp_start_job @job_name or @jod_id
not found as the ASP/QA error. msdb..sp_start_job has public execute permissions but as I read about the passing of
security to the proxy account I think that is where I have some problems. So working from high access level permisiions I get the same thing. There were some extended user rights that needed to be assigned to the SQL_CMDEXEC local account and the Domain account that MSSQLSERVER service runs under. The solutions (KB) appear a little different SQL7 vs SQL2000. One of the ones I tried requires a reboot, which might not happen for somme time. There is also documentation about Service Pack 3 patching this potential service pack hole but that is SQL2000 as well I suppose.
One of the errors I recieved had only 3 google hits... 2 in Russian!!! nothing like being mainstream. Thanks though, you have confirmed that sp_start_job is the way to handle this, I agree. Like I want to rebuild the DTSrun stack on the IIS box when I change servers etc. No way. There are enough maintenance setup considerations in this world and it takes a whole lot of convincing or desperation to add another one.

Tenacious O
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000