Post reply

DTEXEC Failing with Xp_cmshell but runs in cmd line

  • November 16, 2012 at 11:12 am

    #284963

    Weird issue running the following command on the 2008 box runs fine. When I run this on the 2012 box it fails only using xm_cmdshell if I run this in command line it runs fine. I am not sure if this is a 2012 issue or just a setting.

    This errors with "DTExec: The package execution returned DTSER_FAILURE (1)."

    xp_cmdshell 'dtexec /Rep n /sq BWCCorpSync /ser DENSODB9 /set \Package.Connections[InboundCorpDBConnection].Properties[ServerName];DENSODB7 /set \Package.Connections[InboundCorpDBConnection].Properties[InitialCatalog];OspreyPacksInc /set \Package.Connections[MarketPlaceConnection].Properties[ServerName];DENSODB6 /set \Package.Connections[MarketPlaceConnection].Properties[InitialCatalog];BWConnectSystem /set \Package.Connections[MarketPlaceStagingConnection].Properties[InitialCatalog];BWConnectSystemLocal'

    This does not error from cmd line from the same 2012 box. What am I missing a setting or something?

    dtexec /Rep n /sq BWCCorpSync /ser DENSODB9 /set \Package.Connections[InboundCorpDBConnection].Properties[ServerName];DENSODB7 /set \Package.Connections[InboundCorpDBConnection].Properties[InitialCatalog];OspreyPacksInc /set \Package.Connections[MarketPlaceConnection].Properties[ServerName];DENSODB6 /set \Package.Connections[MarketPlaceConnection].Properties[InitialCatalog];BWConnectSystem /set \Package.Connections[MarketPlaceStagingConnection].Properties[InitialCatalog];BWConnectSystemLocal

  • November 17, 2012 at 7:55 am

    #1560006

    Could it be a simple privs problem? When you run via xp_CmdShell, you're running as the login that the SQL Server uses to start the service where when you're running from the command line, it's running as you. You may have privs to see/run things that the server login does not.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 20, 2012 at 10:01 am

    #1560903

    Thanks for the response, it actually was a security issue we were actually testing a new service account on that box which did not have rights. We switched it back to our normal security login and that resolved the issue. As the call was being made accross servers it was using the service authentication to run the package.

    Thanks

  • November 21, 2012 at 7:02 am

    #1561235

    Chad Vollmer (11/20/2012)

    Thanks for the response, it actually was a security issue we were actually testing a new service account on that box which did not have rights. We switched it back to our normal security login and that resolved the issue. As the call was being made accross servers it was using the service authentication to run the package.

    Thanks

    That solves one problem. The other problem (that you might not be currently aware of) might be how you've proxied for the use of xp_CmdShell. Have you given the "normal security login" privs to run xp_CmdShell directly? If so, that's a major problem that should be tackled next. No login should be allowed to execute xp_CmdShell directly. It should all be done using stored procedures where the "normal security login" has only PUBLIC privs on the SQL Server with only EXECUTE privs to call the stored that does the actual xp_CmdShell work.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 22, 2012 at 2:24 am

    #1561535

    The ideal situation would be to begin refactoring your way out of using xp_cmdshell so you can eventually disable it. You could start leveraging the SSISDB available as part of SQL Server Integration Services 2012 so you can execute packages using T-SQL stored procedures like SSISDB.catalog.create_execution, SSISDB.catalog.set_execution_parameter_value and SSISDB.catalog.start_execution.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • November 22, 2012 at 7:45 pm

    #1561733

    opc.three (11/22/2012)

    The ideal situation would be to begin refactoring your way out of using xp_cmdshell so you can eventually disable it. You could start leveraging the SSISDB available as part of SQL Server Integration Services 2012 so you can execute packages using T-SQL stored procedures like SSISDB.catalog.create_execution, SSISDB.catalog.set_execution_parameter_value and SSISDB.catalog.start_execution.

    Actually, the ideal situation would be to correctly lock down the system. Moving such a task to SSIS and disabling xp_CmdShell doesn't fix the underlying security problems which would allow an attacker to get in with elevated privs of such sufficiency as to be able to turn xp_CmdShell on and use it themselves.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 22, 2012 at 7:54 pm

    #1561735

    Jeff Moden (11/22/2012)

    opc.three (11/22/2012)

    The ideal situation would be to begin refactoring your way out of using xp_cmdshell so you can eventually disable it. You could start leveraging the SSISDB available as part of SQL Server Integration Services 2012 so you can execute packages using T-SQL stored procedures like SSISDB.catalog.create_execution, SSISDB.catalog.set_execution_parameter_value and SSISDB.catalog.start_execution.

    Actually, the ideal situation would be to correctly lock down the system. Moving such a task to SSIS and disabling xp_CmdShell doesn't fix the underlying security problems which would allow an attacker to get in with elevated privs of such sufficiency as to be able to turn xp_CmdShell on and use it themselves.

    The task is already in SSIS. I am only suggesting that the method to execute the package be changed so that xp_cmdshell could be disabled. Security aside, when running executables using xp_cmdshell you run the risk of the session running xp_cmdshell being killed off but having the executable it invoked persist in the OS. The really is no technical reason in this case to continue with xp_cmdshell. Non-technical, as in it will cost money to convert to using SSISDB, sure, that could easily be a blocker, but I am suggesting it is worth exploring in terms of adding the effort to the upgrade-project. It will tighten up the process in question and could arguably increase security because all SSIS packages run from the instance would not need to run as the SQL Server service account or the same proxy, they could all be run using individual Windows accounts (via proxies) so each package has the least privilege on the network required to do it's work and all the activity by those accounts could be audited and traced individually.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply