Post reply

The Danger of xp_cmdshell

  • September 16, 2016 at 8:08 am

    #1901359

    I've found that there are some app developers that misuse it when it gets enabled. It has lead to some silly surprises at times from developers who do not know how to work with a database.

    For jobs, I've started using a lot more PowerShell for things I used to use xp_cmdshell for... just a trade off.

  • September 16, 2016 at 8:12 am

    #1901360

    mike 62740 (9/16/2016)

    I've found that there are some app developers that misuse it when it gets enabled. It has lead to some silly surprises at times from developers who do not know how to work with a database.

    For jobs, I've started using a lot more PowerShell for things I used to use xp_cmdshell for... just a trade off.

    What's to keep the app developers from misusing Power Shell? And what, explicitly, do you mean by "some app developers that misuse it". How are they misusing it?

    --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)
    Intro to Tally Tables and Functions

  • September 16, 2016 at 8:14 am

    #1901363

    Indianrock (9/16/2016)

    So far I only recall trying to use powershell from a sql agent job once, but this might be a better route than xp_cmdshell.

    How so?

    --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)
    Intro to Tally Tables and Functions

  • September 16, 2016 at 8:41 am

    #1901370

    Indianrock (9/16/2016)

    I use xp_cmdshell in sql jobs, but turn it on before the action and turn it off after. The one that comes to mind is using DIR to check for the existence of a file that indicates billing files are ready to be imported to a database with SSIS.

    You should try

    DECLARE @DirectoryInfo TABLE (FileExists bit,

    FileIsADirectory bit,

    ParentDirectoryExists bit)

    INSERT INTO @DirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists)

    EXECUTE [master].dbo.xp_fileexist 'c:\temp\x.txt'

    God is real, unless declared integer.

  • September 16, 2016 at 9:13 am

    #1901377

    In case anyone missed it, here's a good article on the (non) dangers of xp_cmdshell:

    Tech Corner: xp_cmdshell isn’t evil[/url]

    by Jennifer McGowan

  • September 16, 2016 at 9:14 am

    #1901378

    Jeff Moden (9/15/2016)

    Heh... I think I'm being prodded to publish. 😀

    😉

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • September 16, 2016 at 9:31 am

    #1901384

    Probably a bit off topic but commands like xp_dirtree have been listed as unsupported for over a decade but have been available in all that time.

    Why don't Microsoft support them? It seems very odd to keep an unsupported command across many versions of a product without it being supported. It smacks of a "don't push the big red shiny highly prominent button right there on the wall. Have we mentioned that it is big and shiny....and red"?

    LinkedIn Profile

  • September 16, 2016 at 9:46 am

    #1901389

    Not so simple to use PowerShell from a stored procedure if xp_cmdshell is disabled... and as I said... a trade off.

  • September 16, 2016 at 10:00 am

    #1901392

    As a dev, I have yet found a situation where I needed xp_cmdshell. Just lucky, I guess.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • September 16, 2016 at 10:04 am

    #1901394

    We're using xp_cmdshell for ETL's that run via java command line for a data warehouse. It is vendor supported so wasn't our decision, but it works well. We've set up proxy accounts to OS accounts with just the right privileges so it's fairly locked down, but it took some doing (especially as I was a junior dba just starting out when given the project).

    I wouldn't say it's dangerous, but there are thinner margins for error.

  • September 16, 2016 at 10:38 am

    #1901407

    mike 62740 (9/16/2016)

    Not so simple to use PowerShell from a stored procedure if xp_cmdshell is disabled... and as I said... a trade off.

    You can easily create a job that uses/abuses it. You still haven't said, though... how are the developers on your end abusing xp_CmdShell. Not challenging here... would just like to know and I might be able to help prevent such abuse. And, hopefully, this isn't in prod, is it?

    --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)
    Intro to Tally Tables and Functions

  • September 16, 2016 at 10:41 am

    #1901409

    t.franz (9/16/2016)

    I think the risk is reasonable, as long you are using an xp_cmdshell_proxy_account with minimal privileges.

    I agree the risk can be mitigated.

    t.franz (9/16/2016)

    On the other side it would nice, if SQL Server would provide us with more OS native operations (as file copy or an 'internal' T-SQL bcp command), so that we do not need xp_cmdshell.

    YES!!!! YES!!!! YES!!! We have BULK INSERT, how about BULK EXPORT. How about being able to parameter-ize BULK INSERT. There are a number of ways that basic I/O could and should be facilitated through stored procedures.

    --Paul Hunter

  • September 16, 2016 at 10:42 am

    #1901410

    Jason Markantes (9/16/2016)

    We're using xp_cmdshell for ETL's that run via java command line for a data warehouse. It is vendor supported so wasn't our decision, but it works well. We've set up proxy accounts to OS accounts with just the right privileges so it's fairly locked down, but it took some doing (especially as I was a junior dba just starting out when given the project).

    I wouldn't say it's dangerous, but there are thinner margins for error.

    If you gave an app or anyone other than trusted DBAs the privs to run it directly via proxy, then you don't have it locked down at all. In such cases, it's running as the server god and the OS accounts can do ANYTHING and EVERYTHING that a sysadmin would be able to do.

    You must NOT give any non-DBA account proxy privs to execute xp_CmdShell directly. It MUST be done through a strictly controlled stored procedure.

    --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)
    Intro to Tally Tables and Functions

  • September 16, 2016 at 10:45 am

    #1901411

    SQLNightOwl (9/16/2016)

    [I agree the risk can be mitigated.

    That's what I'm talking about. People think there's a risk in using xp_CmdShell and there isn't. The only risks there are are in the privs that you give people and giving anyone or anything that's not a DBA privs to run xp_CmdShell directly is the mistake. Not xp_CmdShell itself.

    --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)
    Intro to Tally Tables and Functions

  • September 16, 2016 at 11:00 am

    #1901418

    How so? If you meant why use powershell over xp_cmdshell ( you probably didn't mean that ), the answer would be because it has so much more functionality.

    If you mean why don't I use powershell more, I have no excuse.

Viewing 15 posts - 16 through 30 (of 62 total)

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