Executing DTS from .net with users credentials

15 Nov 2005 4:01 PM

Roelf Zomerman

Hello,

We have a DTS package which we execute from an IIS application. The problem
is that the DTS fires off, but does not import anything in the SQL database,
unless we make the user a member of the local administrators group on the
webserver.

The package itself requests data from a local share of the server and users
have permission to access that share and the data.

The database permissions are correct since it works when the user is local
admin on the webserver only.

Does the execution of a DTS package through an IIS application require
permissions such as: Act as part of the OS, Logon Locally, Logon as Batch
Job???

Thanks in advance

16 Nov 2005 9:27 AM

Frans Bouma [C# MVP]

Roelf Zomerman wrote:

Show quote

> Hello,
>
> We have a DTS package which we execute from an IIS application. The
> problem is that the DTS fires off, but does not import anything in
> the SQL database, unless we make the user a member of the local
> administrators group on the webserver.
>
> The package itself requests data from a local share of the server and
> users have permission to access that share and the data.
>
> The database permissions are correct since it works when the user is
> local admin on the webserver only.
>
> Does the execution of a DTS package through an IIS application
> require permissions such as: Act as part of the OS, Logon Locally,
> Logon as Batch Job???

    you could save the DTS package in Sqlserver, and run it with a job, by
using sp_start_job system stored procedure. You can then start that
stored procedure by using an Sqlserver login, for example a user which
is part of the required groups inside sqlserver.

    Another way is to use impersonation in your asp.net application, to
impersonate to a user which more credentials and then start the job.

        Frans

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

16 Nov 2005 1:03 PM

Roelf Zomerman

Seems, the all problems rely on old service packs rule applies also here..

Service pack 3a on the application server for the SQL tools solved the
problem.

We think it has something to do with the TEMP folder storage. .

But it works..! Thanks for all answers

Show quote

"Frans Bouma [C# MVP]" wrote:

> Roelf Zomerman wrote:
>
> > Hello,
> >
> > We have a DTS package which we execute from an IIS application. The
> > problem is that the DTS fires off, but does not import anything in
> > the SQL database, unless we make the user a member of the local
> > administrators group on the webserver.
> >
> > The package itself requests data from a local share of the server and
> > users have permission to access that share and the data.
> >
> > The database permissions are correct since it works when the user is
> > local admin on the webserver only.
> >
> > Does the execution of a DTS package through an IIS application
> > require permissions such as: Act as part of the OS, Logon Locally,
> > Logon as Batch Job???
>
>     you could save the DTS package in Sqlserver, and run it with a job, by
> using sp_start_job system stored procedure. You can then start that
> stored procedure by using an Sqlserver login, for example a user which
> is part of the required groups inside sqlserver.
>
>     Another way is to use impersonation in your asp.net application, to
> impersonate to a user which more credentials and then start the job.
>
>         Frans
>
> --
> ------------------------------------------------------------------------
> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
> My .NET blog: http://weblogs.asp.net/fbouma
> Microsoft MVP (C#)
> ------------------------------------------------------------------------
>