Re: xp_cmdshell and dtsrun

From: Chris Szabo (ChrisSzabo_at_discussions.microsoft.com)
Date: 09/30/04 

Date: Thu, 30 Sep 2004 08:49:04 -0700

AT,

Nope... it was a good try though. The Sql Server Service account and Server
Agent Service account are both the same... a domain account specifically
created for Sql Server. The account was only a member of Domain Users, so I
added it to Domain Admins as well, but that did not correct the problem.

I'm not having a problem executing the the DTS Package, it's accessing the
message queue from the DTS Package when I use xp_cmdshell. I've checked all
the permissions on the queue and even went as far as granting full control to
everyone. No dice though.

Here is the actual error I get when I run xp_cmdshell from Query Analyzer to
try an execute the DTS Package:

output
                                          
-------------------------------------------------------------------------------------------------DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSMessageQueueTask_1
DTSRun OnError: DTSStep_DTSMessageQueueTask_1, Error = -2147220490 (800403F6)
Error string: Can not open MSMQ. Please make sure you have sufficient
rights to access them.
Error source: Message Queue Task
Help file:
Help context: 0
Error Detail Records:
Error: 0 (0); Provider Error: 0 (0)
Error string: Can not open MSMQ. Please make sure you have sufficient
rights to access them.
Error source: Message Queue Task
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSMessageQueueTask_1
DTSRun: Package execution complete.
NULL

As always, any help is very much appreciated. Thanks!

"Al Tenhundfeld" wrote:

>
> What startup account are you using for the SQLServer Service?
>
> I'm not sure, but I would bet that this problem revolves around security.
> Maybe your SQLServer startup account doesn't have sufficient rights to
> execute DTSRun. Manually executing the package via DTSRun or Enterprise
> Manager (I think) both use your user context. Try changing the SQLServer
> startup account to your user account (or any local admin account). You can
> do this through E.M. If the package executes succcessfully from xp_cmdshell,
> you know the problem is related to the DTS execution context.
>
> Let me know how it turned out.
> AT
>
> "Chris Szabo" <ChrisSzabo@discussions.microsoft.com> wrote in message
> news:B67EF0CD-C2FA-4D42-AE20-A7ABABD956EA@microsoft.com...
> > Good afternoon everyone. I have a very frustrating problem that I've been
> > trying to solve all day with no luck. I have a DTS Package that performs
> > a
> > message queue task... simply dumps two global variables to a message
> > queue.
> >
> > If I run the DTS Package through Enterprise manager it works.
> > Furthermore,
> > if I manually type the DTSRun command from the command line, specifying an
> > SQL Server user and password, it works.
> >
> > The problem is with xp_cmdshell. I have a trigger on a table that
> > executes
> > xp_cmdshell, which in turn is suppose to execute the DTS Package. When
> > this
> > happens, I get an error message telling me that I don't have access the
> > MSMQ.
> >
> > What's more interesting is that I enabled auditing on the server hosting
> > MSMQ. Then I enabled auditing on the queue itself. There is not a
> > failure
> > or success record when xp_cmdshell runs the DTS package. I do see success
> > if
> > I execute the DTS package manually though.
> >
> > The problem isn't with executing the DTS package, or with executing
> > xp_cmdshell. I know I can do both of those things. The problem is with
> > xp_cmdshell executing the DTS package via DTSrun.
> >
> > Does anyone have any ideas? I'm really stuck on this one.
> >
> > Thanks a ton.
> >
>
>
>

Relevant Pages

  • Re: Change sa pasword affect current DTS packages
    ... If you use the ~Z option of DTSRUN and you specified sa as the username to ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... > I have quite a number of DTS package that were created> with the current sa account with blank password. ...
    (microsoft.public.sqlserver.dts)
  • Re: Error running DTS package on a schedule (only!)
    ... I use Remote Desktop to access the server where the DTS package was ... > Right Click on the SQL Server Agent icon in EM and choose properties. ... > page here will tell you the account. ...
    (microsoft.public.sqlserver.dts)
  • Re: Bypass step when source file empty
    ... >I have a DTS package where one of the step is to load a flat file into a sql ... The source file can sometimes be empty. ... >and continue executing the following steps that have work flow dependency on ... global community for SQL Server professionals ...
    (microsoft.public.sqlserver.dts)
  • Re: Access while running DTS package using stored procedure
    ... Note that our SQL server is automatically started when the Win 2003 server ... > The permissions when executing xp_cmdshell can be different depending on who ... >> I wrote a DTS Package that can take the data from a .txt file residing ...
    (microsoft.public.sqlserver.dts)
  • Re: Creating a DTS ActiveX Task
    ... (this is the service that runs jobs in SQL Server) ... the 'logon as' tab, you'll see what account this service runs as, this is ... from the network from your DTS package you'll be fine, ... > I'm not very familiar with SQL Server, I only know some VB and TSQL. ...
    (microsoft.public.sqlserver.programming)