Hi

I have a DTS that send mails through a DTSSendMailTask, so I need to
execute the Package as a Domain User.

The same package, before sends the email, executes a series of steps
that ussualy takes 4-5 hours to completion (the mail has the "job
successfully completed" message)

The server's configuration is:
* IIS 5.0 & SQL 2000 (running on the same server)
* The web server accepts only NTLM autentification.


Well, all ok, but...

1. The package must be executed through IIS (there is no other
interface available between the final user and the server).

2. The SQLServer has strong security policies, so I can't execute
xp_cmdshell (so goodbye DTSRun in a job)

3. I could run the DTSRun command in ASP via WScript.Shell but is
executed in the IWAM user account context, so I loose the email
capabilities because IWAM cannot connects to the Exchange Server.

4. sp_start_job cannot be used because 2.

5. DTS.Package fails because it runs synchronously

Any ideas?

PS: forget to use any method that involves a security/admin accound, I
don't have one and I cannot use any but my own (public role)

Use COM+ to abstract away from IWAM, and user a defined account perhaps-

http://www.sqldts.com/default.aspx?207

If you want async, then sp_start_job is good, and don't see why a hard
server will no allow sp_start_job. Seems a bit too restrictive. Can you
create a job and set a schedule to execute in now+1. The account can be set
for SQL Agent, and again for non-sysadmin users.

If none of that works, you will have to look at some code method, threads or
MSMQ to get async.

Can you forget Exchange as use an smtp mail send instead. Avoid all those
nasty MAPI context issues. If so checkout Gert's xp and dts task for smtp
mail http://www.sqldev.net.

I am unclear if all your security issues are just run-time restrictions, or
because you do not own the server. This will obviously effect what can and
cannot be done.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com


"Rodrigo" <rodiaz (AT) gmail (DOT) com> wrote

Hi Darren
sp_start_job is good, but execute jobs. Jobs execute packages via
DTSRun. DTSRun is called via a xp_cmdshell...

The server is fully locked for any purpose other than exec procedures
and query user tables. The accounts I use (SQLServer account and a
Domain user account) doesn't have any privileges beyond the database I
store my work.

I'm not the owner neither sysadmin of the server, so I can't install
any component or execute them. And even I can't connect to the server
via TS :-(

Am I humilliatedly lost?