Post reply

I cringe every time I see a recommendation for xp_cmdshell.

  • November 9, 2012 at 9:25 am

    #1557678

    There is merit to your approach Jeff, namely keeping all database code transactionally consistent along with the data it operates on, but we do diverge when it comes to security. There are ways to create additional roadblocks before xp_cmdshell can be enabled, and all options should be employed. The road to securing a system is typically one of the more difficult roads available. The SA argument holds up until you decide (or are compelled too due to compliance rules or legal regulations) that you need to be monitoring the activity of your SA's.

    @mike.renwick-894639

    In the specific scenario where you want to call an external executable from xp_cmdshell (e.g. rs.exe) there are many other concerns beyond just running a simple cmd shell command (e.g. DEL, REN or COPY). Here is some additional reading on the subject that pertains specifically to your scenario:

    http://www.sqlservercentral.com/Forums/Topic1369246-391-1.aspx

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

  • November 9, 2012 at 10:05 am

    #1557705

    Thanks, yes, there are those obvious problems- if a report hangs for some reason (something I haven't seen in 4 years, fortunately), it would be a manual-cleanup job. We've not got anything that works with SSIS in this way, for obvious reasons.

    Our view has been to take that securing route: users have no access to xp_cmdshell. SA is secured etc etc. Obviously if that's compromised, it's pretty much game over anyway.

    The rest for something relatively simple, can feel like an architectural imposition, and I always find myself going down the rabbit hole- e.g. with a powershell-led setup, having to think how that would be kicked off remotely from a user's desktop, from within an excel instance/C# app/Refreshing an intranet PHP page or an IOS app (for example). How to do that securely.

    The benefit of handling everything via stored procs for us, has been that at the user end, we only really have to manage access to the prereq routine, and it handles everything else. While that "everything" encapsulates a lot more than a pure database routine maybe should, in reality, it's only chewing on some data, selecting a few rows and presenting it! Being able to see a database, and managing the related security then becomes the only real concern, in terms of flexibility.

    It still amazes me that for something as simple as this, there are so many hoops to jump through!

    Fascinating reading though. Thanks both for your comments.

Viewing 2 posts - 76 through 76 (of 76 total)

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