DTS Package Problem

I have a stored procedure that calls a DTS package using xp_cmdshell dtsrun. The problem is strange, I can execute the stored procedure from Query Analyzer and it works fine. I call the stored procedure from inside of an ASP.NET application and it seems to execute but doesn't. The user account for the ASP.NET application has rights to execute the stored procedure and I have run a trace and so see any login failures. I'm stumped, any clues??

neel

xp_cmdshell can run an enterprise but it is a SQL Server Agent dependent service, you have to install SQL Server Agent with a service account to run all the dependent services. Hope this helps|||

Does the SQL Server agent have to be on the client computer or just the computer with SQL Server on it. I would think since I'm just sending the stored procedure name of the stored procedure on SQL Server that that server would be the only place I need the SQL Server Agent?

Thanks

Neel

|||The SQL Server Agent in the computer executing xp_cmdshell needs a service account because you can schedule xp_cmdshell with a job to transfer data from main frame to another SQL Server in the same network and it will run it. SQL Server Agent needs a service account if your application will use any of its dependent services like DTS Automation, Replication and jobs. Microsoft keep this from developers because there is a lot to know in development. Hope this help|||

Thanks it is working now. I also had to go in and setup a proxy account in the SQL Server Agent that had sysadmin rights because the users of the program and the program user account do not have sysadmin rights.

Thanks Again

Neel

dts or xp_cmdshell permissions

How do i give a user permissions to execute XP_CMDSHELL or a DTS package?
Without compromising the systems security.
Basically what im trying to do is use an asp page on another domain to
access mssql and create a database. Everything works fine except that i
cannot create a folder on the server.
I need to first create a folder by passing the domain name to xp_cmdshell on
the local drive. Then creating the database.
The account accessing the mssql server is dbm.something.com which is in the
master database. This sends arguments to a stores procedure that creates the
database. I added a line to the stored procedure to run xp_cmdshell and
create a database. Once it gets to this line it gives me an error
xpsql.c:Error 1314 from CreateProcessAsUser on line: 432.
Please help me
Thank you
The error 1314 you are getting is:
A required privilege is not held by the client.
Could be that your SQL service account doesn't have the
correct rights to allow it to change security context to the
proxy account. The SQL service account needs Act as part of
OS, Increase Quotas, replace process level token and Login
as batch job.
-Sue
On Wed, 21 Sep 2005 16:15:04 -0400, "robert" <rob@.ms.com>
wrote:

>How do i give a user permissions to execute XP_CMDSHELL or a DTS package?
>Without compromising the systems security.
>Basically what im trying to do is use an asp page on another domain to
>access mssql and create a database. Everything works fine except that i
>cannot create a folder on the server.
>I need to first create a folder by passing the domain name to xp_cmdshell on
>the local drive. Then creating the database.
>The account accessing the mssql server is dbm.something.com which is in the
>master database. This sends arguments to a stores procedure that creates the
>database. I added a line to the stored procedure to run xp_cmdshell and
>create a database. Once it gets to this line it gives me an error
>xpsql.c:Error 1314 from CreateProcessAsUser on line: 432.
>Please help me
>Thank you
>
|||Yeah i read that somewhere, but i dont know how to give it the permissions.
Where do i do that ? act as part of OS and logon as batch are windows
permissions. I dont have a proxy account for SQL under the local users or
domain users. What am i missing here?
Thank for helping
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:feijj1dvrbpvrjepce3jrvqbkj2pdi6l6e@.4ax.com...
> The error 1314 you are getting is:
> A required privilege is not held by the client.
> Could be that your SQL service account doesn't have the
> correct rights to allow it to change security context to the
> proxy account. The SQL service account needs Act as part of
> OS, Increase Quotas, replace process level token and Login
> as batch job.
> -Sue
> On Wed, 21 Sep 2005 16:15:04 -0400, "robert" <rob@.ms.com>
> wrote:
>
|||First, your missing the rights for the service account. The
permissions are outlined in the following article but if you
set the service accounts through Enterprise Manager, the
rights and permissions are handled for you:
HOW TO: Change the SQL Server or SQL Server Agent Service
Account Without Using SQL Enterprise Manager in SQL Server
2000
http://support.microsoft.com/?id=283811
You can use the Local Security Policy snap in to view the
permissions. From the run command from the start button,
type in secpol.msc
If accounts other than sysadmin accounts or the service
accounts are going to be used to execute xp_cmdshell, you
need to setup a proxy account. You can use
xp_sqlagent_proxy_account
You can find more information in books online under
xp_sqlagent_proxy_account and xp_cmdshell.
-Sue
On Wed, 28 Sep 2005 12:18:24 -0400, <rvegas@.rogers.com>
wrote:

>Yeah i read that somewhere, but i dont know how to give it the permissions.
>Where do i do that ? act as part of OS and logon as batch are windows
>permissions. I dont have a proxy account for SQL under the local users or
>domain users. What am i missing here?
>Thank for helping
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:feijj1dvrbpvrjepce3jrvqbkj2pdi6l6e@.4ax.com.. .
>
|||Thanx for your help Sue, still a bit confused will have to read up on this.
Under enterprise manager >>management>sqlserver agent>job system tab.
I have "only sysadmins can run active scripting jobs" checked. I dont want
to change this. Is there another way?
Sorry for all the stupid questions. just dont want to mess anything up.
Thanx
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:55jlj1p4m2s30gcolq6i9ok7gqcs16vjjj@.4ax.com...
> First, your missing the rights for the service account. The
> permissions are outlined in the following article but if you
> set the service accounts through Enterprise Manager, the
> rights and permissions are handled for you:
> HOW TO: Change the SQL Server or SQL Server Agent Service
> Account Without Using SQL Enterprise Manager in SQL Server
> 2000
> http://support.microsoft.com/?id=283811
> You can use the Local Security Policy snap in to view the
> permissions. From the run command from the start button,
> type in secpol.msc
> If accounts other than sysadmin accounts or the service
> accounts are going to be used to execute xp_cmdshell, you
> need to setup a proxy account. You can use
> xp_sqlagent_proxy_account
> You can find more information in books online under
> xp_sqlagent_proxy_account and xp_cmdshell.
> -Sue
> On Wed, 28 Sep 2005 12:18:24 -0400, <rvegas@.rogers.com>
> wrote:
>
|||No problem. You could have the users execute the package
using the DTS object model. There are a few different
options with that and you could use another application such
as ASP. The following have some examples:
Execute a package with OLE Automation -
http://www.databasejournal.com/featu...le.php/1459181
Execute a package from ASP -
http://www.sqldts.com/default.aspx?207
Execute a package from VB -
http://www.sqldts.com/default.aspx?208
-Sue
On Fri, 7 Oct 2005 16:56:33 -0400, <rvegas@.rogers.com>
wrote:

>Thanx for your help Sue, still a bit confused will have to read up on this.
>Under enterprise manager >>management>sqlserver agent>job system tab.
>I have "only sysadmins can run active scripting jobs" checked. I dont want
>to change this. Is there another way?
>Sorry for all the stupid questions. just dont want to mess anything up.
>Thanx
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:55jlj1p4m2s30gcolq6i9ok7gqcs16vjjj@.4ax.com.. .
>

DTS in SP Error

I have a DTS package already created on the server. I want to call it
in a sp using the xp_cmdshell 'dtsrun ...'
I'm getting "xpsql.cpp: Error 87 from GetProxyAccount on line 604"
Any ideas how to fix this?
Thanks
MikeI found the fix for this error but I have a new problem
This DTS package is supposed to drop the table if it exists then create
a new one and load data from an excel file. This works fine if I run it
manually from enterprise manager.
If I try to run it from query analyzer i get an error stating that the
directory is not a valid path. And it doesn't drop the table first.
Any ideas?|||I suppose that when you run that package from QA you are using a different
user than EM or something like that. And that user doesn't have permissions
over that folder.
--
Current location: Alicante (ES)
"Mike" wrote:

> I found the fix for this error but I have a new problem
> This DTS package is supposed to drop the table if it exists then create
> a new one and load data from an excel file. This works fine if I run it
> manually from enterprise manager.
> If I try to run it from query analyzer i get an error stating that the
> directory is not a valid path. And it doesn't drop the table first.
> Any ideas?
>