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

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 xp_cmdshell 'DTSrun ...' or sp_start_job

Author  Topic 

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-27 : 10:15:38
Hi,
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
anywhere.

Tenacious O

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-27 : 21:46:03
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.



Damian
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-11-28 : 07:58:29
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
   

- Advertisement -