DTS Through ASP Page permissions

[Tim]

I'm running a DTS through a stored procedure called from an ASP page
on Server B.

Server B is also the SQL Server.

The DTS package is supposed to access a file on Server A.

I'm having permission issues in running this through the web. I've
tried giving IUSER_ServerB and IWAM_ServerB full permission on the
file but that didn't work.

What account needs permissions on the file? Do I need to add it
differently that I would the IUser/IWAM accounts?

Tim

[Russell Fields]

Tim,

What version of SQL Server are you running? When you say "running a DTS
through a stored procedure", do you mean that you are starting a SQL Agent
job, using xp_cmdshell to run DTSRUN.exe, or something else?

If it is a SQL Agent job running as a sysadmin account, then the DTS would
be running as the SQL Server service account. If a SQL Agent job is running
as a non-sysadmin, then the job will run as a proxy account. (SQL Server
2000 only has one, but 2005 allows you to define many SQL Agent proxy
accounts.)

If you are running it via xp_cmdshell, the SQL Agent Proxy Account (2000) or
(2005) the ##xp_cmdshell_proxy_account## credential's domain login identity.

RLF

Post by Tim
I'm running a DTS through a stored procedure called from an ASP page
on Server B.
Server B is also the SQL Server.
The DTS package is supposed to access a file on Server A.
I'm having permission issues in running this through the web. I've
tried giving IUSER_ServerB and IWAM_ServerB full permission on the
file but that didn't work.
What account needs permissions on the file? Do I need to add it
differently that I would the IUser/IWAM accounts?
Tim

[JM]

Russell,

Thanks for the response and sorry for the late response. My priorities
got shifted for awhile and now I'm back on this.

I am using SQL Server 2000. I currently have a stored procedure that
is using xp_cmdshell to run DTSRUN.

I was trying to get the procedure to start a SQL Agent job but was
unsuccessful that way.

Also, do you know of a way to return an error if the DTS fails? I have
no way of knowing except to compare the number of rows in the table
before and after the job runs.

Thanks,

Tim

Tim,
What version of SQL Server are you running?  When you say "running a DTS
through a stored procedure", do you mean that you are starting a SQL Agent
job, using xp_cmdshell to run DTSRUN.exe, or something else?
If it is a SQL Agent job running as a sysadmin account, then the DTS would
be running as the SQL Server service account.  If a SQL Agent job is running
as a non-sysadmin, then the job will run as a proxy account.  (SQL Server
2000 only has one, but 2005 allows you to define many SQL Agent proxy
accounts.)
If you are running it via xp_cmdshell, the SQL Agent Proxy Account (2000) or
(2005) the ##xp_cmdshell_proxy_account## credential's domain login identity.
RLF

Post by Tim
I'm running a DTS through a stored procedure called from an ASP page
on Server B.
Server B is also the SQL Server.
The DTS package is supposed to access a file on Server A.
I'm having permission issues in running this through the web. I've
tried giving IUSER_ServerB and IWAM_ServerB full permission on the
file but that didn't work.
What account needs permissions on the file? Do I need to add it
differently that I would the IUser/IWAM accounts?
Tim- Hide quoted text -

- Show quoted text -

[tbradshaw via SQLMonster.com]

Hello Tim,

Russell has good advice. You also asked about returning an error. To where?

I have never tried to execute a package from within a stored procedure, but
when I execute a package from a SQL Agent job using DTSRUN, the job will
report failure when there is a failure within DTS. So, perhaps on the next
line following your DTSRUN, you could check @@ERROR for success/failure.
Likewise you could throw an exception up the call chain back to your ASP page.


In addition, you can capture your DTS execution in a log file. In the
designer window, right-click on an empty space & choose "package properties".
In the logging tab, you can log to either SQL server or to an error file.
For simplicity, I log to Error File by providing a path to a text file.

Fair Warning: Logging appends data to the file for each run, so you'll need
some outside process to empty it from time to time.

Let us know how you make out.

Regards, Tom

Thomas Bradshaw
Data Integration Services
MyWebGrocer

[JM]

Thanks for the ideas.

I will give them a try this week and let you know how it turns out.

I am running an asp page calling the stored proc. I was hoping there
was a way to have the asp page receive the error if it happened. I may
have to capture the log file and display it or a portion somehow on
the page.

Thanks,

Tim

Post by tbradshaw via SQLMonster.com
Hello Tim,
Russell has good advice.  You also asked about returning an error.  To where?
I have never tried to execute a package from within a stored procedure, but
when I execute a package from a SQL Agent job using DTSRUN, the job will
report failure when there is a failure within DTS.  So, perhaps on the next
Likewise you could throw an exception up the call chain back to your ASP page.
In addition, you can capture your DTS execution in a log file.  In the
designer window, right-click on an empty space & choose "package properties".
In the logging tab, you can log to either SQL server or to an error file.
For simplicity, I log to Error File by providing a path to a text file.
Fair Warning:  Logging appends data to the file for each run, so you'll need
some outside process to empty it from time to time.
Let us know how you make out.
Regards, Tom
Thomas Bradshaw
Data Integration Services
MyWebGrocer
--
Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-dts/200901/1