The Danger of xp_cmdshell

  • Comments posted to this topic are about the item The Danger of xp_cmdshell

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

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

    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.

    God is real, unless declared integer.

  • Publish Jeff!

    I'd ask what people wanted to use xp_cmdshell for. There are loads of things you can do within SQL Server, for me a request to make calls out to the OS is a potential indicator that someone has a hammer and everything is a nail.

    They may be completely justified in what they want to do and how they want to do it, in which case fine. I'd always check though.

  • t.franz (9/16/2016)


    Ior an 'internal' T-SQL bcp command), so that we do not need xp_cmdshell.

    BULK INSERT? Or are you lamenting the absence of BULK EXPORT?

    I quite like MySql LOAD XML INFILE & LOAD DATA INFILE, but not the way those commands coerce the data into their target without throwing an error. Come to think of it I wasn't too impressed when an INSERT INTO...VALUES statement with the fields in the wrong order resulted in a string containing filenames being coerced into an integer field. Had a few head scratching moments trying to find out where the large integer values were coming from.

  • BULK INSERT? Or are you lamenting the absence of BULK EXPORT?

    I'm missing BULK EXPORT.

    Another point: even if you can do some OS-like stuff with SQL Server, the account that runs the SQL Server service has to have the privileges to do that. So it could more secure to do something with a proxy account that e.g. has no permissions on the SQL data / backup directory.

    God is real, unless declared integer.

  • Jeff Moden (9/15/2016)


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

    As soon as I read the title I thought of your blood pressure 😉

  • t.franz (9/16/2016)


    BULK INSERT? Or are you lamenting the absence of BULK EXPORT?

    I'm missing BULK EXPORT.

    Another point: even if you can do some OS-like stuff with SQL Server, the account that runs the SQL Server service has to have the privileges to do that. So it could more secure to do something with a proxy account that e.g. has no permissions on the SQL data / backup directory.

    The missing "BULK EXPORT" is the number 1 reason outside of DBA tasks that I have seen xp_cmdshell is needed. I know ssis can be used but that can be overkill

  • 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.

    Along these lines:

    DECLARE @ReturnCode INT -- xp_cmdshell returns 0 if the file is found

    EXEC @ReturnCode = master.dbo.xp_cmdshell "dir c:\STATS.dfhdfh", no_output

    IF @ReturnCode = 0

    PRINT 'File exists!'

    ELSE

    PRINT 'File not found'

  • We use xp_cmdshell in a large number of stored procedures that we call nightly. Every time I enable this setting on a new server build, I get a bit concerned, but have yet to see any adverse affects and I've been seeing it used for 17 years. I believe, I could be wrong, but with the enhanced security in windows and SQL server as well, it is not as easy to hack a system using this command.

    For now I will continue to enable it where it is needed.

  • Jeff Moden (9/15/2016)


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

    "Prodded"????

    My first thought on reading this was "oh, dear, Steve just shot a pork chop at Jeff Moden"! 😀

    Rich

  • As @Indianrock does, I turn it on only when needed. I recall having to write a custom database archival script. Within the script a database was backuped up, dropped, and copied to an archival path. I used xp_cmdshell to move the files. I scheduled the script to run in SQL Agent, but had extra job steps to enable and disable xp_cmdshell.

  • 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.

  • I find it convenient to use xp_cmdshell to do things like execute NET or DOS commands for informational purposes, like in environments where the host server doesn't allow RDP connections.

    Even when enabled, it's not usable by non sysadmin logins by default, and you have to explicitly jump through hoops to grant access. If your non-DBA users do have sysadmin privillage, they'll simply enable it themselves and then use it. If you tick them off by disabling xp_cmdshell, they'll simply drop your login. The bottom line is: don't grant non-DBA users membership in sysadmin role. Just follow that basic advice and a lot of these issues raised in forum discussions here simply won't be an issue, at least not for your organization.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Of course not... people are dangerous not computers or commands.

Viewing 15 posts - 1 through 15 (of 63 total)

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