Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: xp_cmdshell headache

    xp_cmdshell is giving me a headache. I do not know whether the problem is specific to SP3 or not.

    I have a DTS package that I need for selected users to be able to execute on demand. The DTS package is called from a stored procedure. The stored procedure simply calles "execute master.dbo.xp_cmdshell 'DTSRun /S MyServer /U sa /P MySAPassword /N "MyDTSPackage" ' "

    When I do this, I get an error returned that says:

    Msg 50001, Level 1, State 50001
    xpsql.cpp: Error 87 from GetProxyAccount on line 604

    I have set the SQL Agent Proxy account (new to SP3) with:
    exec master..xp_sqlagent_proxy_account N'SET' , N'MyDOMAIN', N'MySQLService', N'MyPassword'

    Can someone offer a suggestion? I'm open to alternatives besides executing the DTS package from a stored proc.

    Regards,

    Hugh Scott

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: xp_cmdshell headache

    Okay, nver mind. I figured out what the problem(s) were and found a solution. The two problems I was having were ownership (and cross-database chaining) and a setting on the SQL Agent properties page.

    To solve the permissions/ownership problem:

    1. I changed the database owner to sa (to match master)
    2. I enabled cross-database-ownership chaining on the database (set by default to disabled with the installation of SP3).

    To solve the other problem, which was generating the error message listed below, I checked a block on the SQL Agent properties page to allow users other than SysAdmin to execute CmdExc and Scripting tasks from job steps.

    If someone cares to provide additional commentary on the wisdom/security of this, I would be more than grateful to listen and learn more.

    Regards,

    Hugh Scott

    Originally posted by hmscott
    xp_cmdshell is giving me a headache. I do not know whether the problem is specific to SP3 or not.

    I have a DTS package that I need for selected users to be able to execute on demand. The DTS package is called from a stored procedure. The stored procedure simply calles "execute master.dbo.xp_cmdshell 'DTSRun /S MyServer /U sa /P MySAPassword /N "MyDTSPackage" ' "

    When I do this, I get an error returned that says:

    Msg 50001, Level 1, State 50001
    xpsql.cpp: Error 87 from GetProxyAccount on line 604

    I have set the SQL Agent Proxy account (new to SP3) with:
    exec master..xp_sqlagent_proxy_account N'SET' , N'MyDOMAIN', N'MySQLService', N'MyPassword'

    Can someone offer a suggestion? I'm open to alternatives besides executing the DTS package from a stored proc.

    Regards,

    Hugh Scott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •