Post reply

Does anyone have a good reason to run xp_CmdShell?

  • June 21, 2011 at 5:46 pm

    #1342186

    opc.three (6/21/2011)

    I won't let the cat out of the bag as to how I think you're planning to create the misdirection necessary to accomplish a secure xp_cmdshell environments without any security misqueues...will there be an article or was Craig's reference to "next article" about something else?

    Don't look to me to have insider information. I'm blindly stumbling along in the wake of this thread, being the voice of the average soul going "Wait, wut?". I think that's why Jeff isn't directly answering many of my comments, I think I'm getting closer and he's seeing if I can figure out the puzzle with enough hints... and watching my brain smoke for entertainment... :laugh:

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • June 21, 2011 at 7:34 pm

    #1342200

    Jeff Moden (6/21/2011)

    The problem that I'm becoming more and more aware of is that people really don't know how to run procs that contain xp_CmdShell without giving the proverbial keys to the city to a low prived user. In those cases and in the face of such a lack of knowledge, I agree that they shouldn't allow xp_CmdShell to be enabled.

    Hmmmm..... sounds like the intro for a fantastic new SQL Server Central article from Jeff!!!

    Rich

  • June 21, 2011 at 9:12 pm

    #1342209

    opc.three (6/21/2011)

    Re: the hiding, I have to ask because I know I'm missing something...ORIGINAL_LOGIN() shows us "who really ran the proc" in T-SQL and SessionLoginName shows us "who" inside a trace. Where else do we lose visibility?

    Simple things such as SUSER_NAME() are where the hiding occurs (of course, you knew that). They show the "EXECUTE AS" user rather than the person actually running the proc. I’m still doing testing on the SQL Profiler side of the house to see what happens under what has turned out to be a whole slew of scenarios including the two secure methods I’ve been talking about.

    I actually have SUSER_NAME() to thank for steering me away from EXECUTE AS ad WITH EXECUTE AS. I didn't want to change all the audit triggers and defaults on the tables that had been created at my current company that use SUSER_NAME() and it drove me until I found the methods I was looking for. Sommarskog helped me a whole lot with one method (he's revamping one of his articles after a bit of code swapping between us). To be sure, there's not an "EXECUTE AS" or "WITH EXECUTE AS" anywhere in the mix and even SUSER_NAME() correctly identifies the user actually running the "PFM" procs.

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

  • June 21, 2011 at 9:19 pm

    #1342211

    Craig Farrell (6/21/2011)

    Craig Farrell (6/20/2011)

    I may need to, however, learn more about this before I go any further in this debate:

    opc.three (6/20/2011)

    xp_cmdshell can be used by everyone on the instance but that means everyone will assume the identity of the xp_cmdshell proxy account credentials and you only get one of those per instance.

    I have some reading to do tomorrow.

    Yes, I'm quoting myself for reference.

    For anyone else who got sideswiped by this:

    xp_cmdshell in msdn: http://msdn.microsoft.com/en-us/library/ms175046.aspx. See remarks.

    Activating the proxy account: http://msdn.microsoft.com/en-us/library/ms190359.aspx

    In particular:

    xp_cmdshell Proxy Account

    When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

    The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

    This in 2k was a bit different. It functioned under the SQLAgent proxy.

    http://msdn.microsoft.com/en-us/library/aa260689(v=SQL.80).aspx

    When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000.

    In earlier versions, which is where 'kill it, Kill It, KILL IT' apparently was drummed into our heads:

    Note In earlier versions, a user who was granted execute permissions for xp_cmdshell ran the command in the context of the MSSQLServer service's user account. SQL Server could be configured (through a configuration option) so that users who did not have sa access to SQL Server could run xp_cmdshell in the context of the SQLExecutiveCmdExec Windows NT account.

    My most recent argument falls dead. By using a designated proxy account in 2k5 or higher, you can still allow your local agent the power it needs for DBA maintenance and restrict what your xp_cmdshell can do... at least as well as you can via a SQLAgent login controlling SSIS.

    My final argument is non-logical, non-sensical, and just simply my own internal input after having too many beer lollipops: 😛

    Good show, Jeff, learned something quite important from this thread. 😀

    Heh... on no... The "good show" is the fact that you're digging into all of this and sharing your results. I raise hell, prop it up on a stick, and good folks like you start hammering things out on their own. Very well done, Craig. I'd love to work back-to-back with you on a project sometime. 🙂

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

  • June 21, 2011 at 9:46 pm

    #1342217

    opc.three (6/21/2011)

    I won't let the cat out of the bag as to how I think you're planning to create the misdirection necessary to accomplish a secure xp_cmdshell environments without any security misqueues...will there be an article or was Craig's reference to "next article" about something else?

    Very cool. I really appreciate that. No, I'm not "planning" to create the misdirection necessary.... With Sommarskog's help, it's already been done one way. With the help of another author, who shall remain nameless at this point, there's a second method, as well. I've actually begun implimentation of the second method for the xp_CmdShell side of things because it's so easy to use and so easy to limit. I'll use Sommarskog's methods for all the other stuff.

    So far as an article goes, I'm considering it. Like the Tally Table, I didn't invent these methods, but I can help bring them to light. As Craig has started to find out, there's a whole lot of misinformation and even dangerous information out there. There's a lot of folks that use xp_CmdShell for whatever reason and, from what I've seen plastered all over the internet, 99.999999% of them are doing it all very, very wrong and very, very dangerously.

    I say "considering it" because there's so much bad information out there that it's caused a whole lot of otherwise intelligent people to summarily reject a useful tool and they do so with great prejudice. I'm not sure any of them would listen if I offered the secure methods in an article especially since both methods will violate another totally unreasonable fear in those same, otherwise intelligent people.

    Shifting gears, I recently saw a post where you wrote some PowerShell that would copy files older than 60 days to an archive folder. I've not yet dug into the wiles of PowerShell and have lost track of that post. Would you do me the favor of posting that script on this thread so I can play with it a bit? Thanks.

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

  • June 21, 2011 at 10:29 pm

    #1342224

    Jeff Moden (6/21/2011)

    opc.three (6/21/2011)

    I won't let the cat out of the bag as to how I think you're planning to create the misdirection necessary to accomplish a secure xp_cmdshell environments without any security misqueues...will there be an article or was Craig's reference to "next article" about something else?

    Very cool. I really appreciate that. No, I'm not "planning" to create the midirection necessary.... With Sommarskog's help, it's already been done one way. With the help of another author who shall remane nameless, at this point, there's a second method, as well. I've actually begun implimentation of the second method for the xp_CmdShell side of things because it's so easy to use and so easy to limit. I'll use Sommarskog's methods for all the other stuff.

    I can't wait, partly because I am confident I'll learn something exciting about SQL Server, and partly because I want to see if what I am thinking matches up to one of the ways you came up with 🙂

    So far as an article goes, I'm considering it. Like the Tally Table, I didn't invent these methods, but I can help bring them to light. As Craig has started to find out, there's a whole lot of misinformation and even dangerous information out there. There's a lot of folks that use xp_CmdShell for whatever reason and, from what I've seen plastered all over the internet, 99.999999% of them are doing it all very, very wrong and very, very dangerously.

    I say "considering it" because there's so much bad information out there that it's caused a whole lot of otherwise intelligent people to summarily reject a useful tool and they do so with great prejudice.

    Visceral would be a good word to describe my reaction when I see recommendations like "oh yeah, you can just use xp_CmdShell to do that" doled out nonchalantly on sql forums all over the tubes. I find that analogous to: "Here you go son, I know you have no idea what you're doing because you just asked me to teach you how to tie your shoes, but here's a bazooka, enjoy! ...oh yeah, and don't tell your mother I gave that to you or we'll both be in trouble!".

    I'm not sure any of them would listen if I offered the secure methods in an article especially since both methods will violate another totally unreasonable fear in those same, otherwise intelligent people.

    I would read, not sure about whether I would follow your lead on this one though. For me, the xp_CmdShell ship has sailed and I have moved on to steering people towards managed language solutions.

    Shifting gears, I recently saw a post where you wrote some PowerShell that would copy files older than 60 days to an archive folder. I've not yet dug into the wiles of PowerShell and have lost track of that post. Would you do me the favor of posting that script on this thread so I can play with it a bit? Thanks.

    Happily...here are a few recent posts. All solutions are based on the same boilerplate code which is credited in the first post:

    Delete files older than n-days

    Delete folders older than n-days

    Copy files older than 60 days somewhere

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

  • June 21, 2011 at 10:31 pm

    #1342225

    Craig Farrell (6/21/2011)

    opc.three (6/21/2011)

    I won't let the cat out of the bag as to how I think you're planning to create the misdirection necessary to accomplish a secure xp_cmdshell environments without any security misqueues...will there be an article or was Craig's reference to "next article" about something else?

    Don't look to me to have insider information. I'm blindly stumbling along in the wake of this thread, being the voice of the average soul going "Wait, wut?". I think that's why Jeff isn't directly answering many of my comments, I think I'm getting closer and he's seeing if I can figure out the puzzle with enough hints... and watching my brain smoke for entertainment... :laugh:

    Oh well, can't blame me for trying 🙂 I too am a little dazed (in a good way) from hanging in there on this thread...good stuff gents!

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

  • June 21, 2011 at 10:47 pm

    #1342229

    Craig Farrell (6/21/2011)

    Don't look to me to have insider information. I'm blindly stumbling along in the wake of this thread, being the voice of the average soul going "Wait, wut?". I think that's why Jeff isn't directly answering many of my comments, I think I'm getting closer and he's seeing if I can figure out the puzzle with enough hints... and watching my brain smoke for entertainment... :laugh:

    My most sincere apologies for me making it look like I'm ignoring you, Craig, but you're precisely correct and I do like the way you think. Contrary to what you may currently believe, I also think that you "get it"... you just haven't figured out that you already know what "it" is, yet.. 🙂

    C'mon lightbulb.

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

  • June 21, 2011 at 11:48 pm

    #1342237

    opc.three (6/21/2011)

    Visceral would be a good word to describe my reaction when I see recommendations like "oh yeah, you can just use xp_CmdShell to do that" doled out nonchalantly on sql forums all over the tubes. I find that analogous to: "Here you go son, I know you have no idea what you're doing because you just asked me to teach you how to tie your shoes, but here's a bazooka, enjoy! ...oh yeah, and don't tell your mother I gave that to you or we'll both be in trouble!".

    Absolutely perfect! Dang! If I do write an article on this, I'd like your permission to use that exact quote because that's the way most of us (and "us" includes me) feel about those types of recommedations.

    BWAA-HAAA!!!! I just got this very funny image of a kid picking up the bazooka backwards and Dad's brand new car is right behind him and... boy, is Mom gonna be pissed! :hehe:

    I would read, not sure about whether I would follow your lead on this one though. For me, the xp_CmdShell ship has sailed and I have moved on to steering people towards managed language solutions.

    Absolutely understood and I agree. I wouldn't try to talk anyone out of the idea of using something other than xp_CmdShell especially if they've found a way they're comfortable with for doing things like file management and the like. There's more than one way to do everything. I just want to make sure that little Johnny doesn't blow up his Dad's new car.

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

  • June 21, 2011 at 11:56 pm

    #1342238

    rmechaber (6/21/2011)

    Jeff Moden (6/21/2011)

    The problem that I'm becoming more and more aware of is that people really don't know how to run procs that contain xp_CmdShell without giving the proverbial keys to the city to a low prived user. In those cases and in the face of such a lack of knowledge, I agree that they shouldn't allow xp_CmdShell to be enabled.

    Hmmmm..... sounds like the intro for a fantastic new SQL Server Central article from Jeff!!!

    Rich

    Gosh... thanks for the kind words, Rich. :blush: I have to tell you, though... I never thought I'd end up writing an article on security or xp_CmdShell.

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

  • June 22, 2011 at 2:55 am

    #1342276

    Jeff Moden (6/21/2011)

    Craig Farrell (6/21/2011)

    Don't look to me to have insider information. I'm blindly stumbling along in the wake of this thread, being the voice of the average soul going "Wait, wut?". I think that's why Jeff isn't directly answering many of my comments, I think I'm getting closer and he's seeing if I can figure out the puzzle with enough hints... and watching my brain smoke for entertainment... :laugh:

    My most sincere apologies for me making it look like I'm ignoring you, Craig, but you're precisely correct and I do like the way you think. Contrary to what you may currently believe, I also think that you "get it"... you just haven't figured out that you already know what "it" is, yet.. 🙂

    C'mon lightbulb.

    Bah, Jeff. We've gone round the block a few times on things like this. For you to outright ignore my commentary means one of two things. Either A) I'm being an *** and deserve to be ignored (not an impossibility, but Celko didn't get involved here) or B) You've thought through the reasons to leave me to my own devices.

    After re-reading my own posts to make sure (A) didn't apply... I had to conclude (B). For you to decide (B) was appropriate meant ... due to deduction... you felt I'd get there on my own if I'd stop reacting and start thinking.

    I needed the hint from Orlando to realize everything I knew was antique.

    So, me and my unplugged lightbulb will be hanging around the next few days seeing if I can puzzle out 1 and 1 is 11, not 2. 🙂 That's mostly because work would like me to actually work. I think you're right, and I think I know where you're going with this... but it skeers me. Skeers me right outa me britches. Thankfully, I'm not wearing britches... so I don't care. 😀

    For anyone following along with the Level II masquerading as a III, here's my next hint for research:

    With Sommarskog's help

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • June 22, 2011 at 5:38 am

    #1342318

    Craig Farrell (6/22/2011)

    For anyone following along with the Level II masquerading as a III, here's my next hint for research:

    With Sommarskog's help

    Hey, I resemble that remark 😀 and if that's where we're going then I could see what this comment of Jeff's was alluding too and that would setup a whole new block to walk around.

    I'm not sure any of them would listen if I offered the secure methods in an article especially since both methods will violate another totally unreasonable fear in those same, otherwise intelligent people.

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

  • June 22, 2011 at 6:30 am

    #1342335

    Craig Farrell (6/21/2011)

    For anyone else who got sideswiped by this:

    xp_cmdshell in msdn: http://msdn.microsoft.com/en-us/library/ms175046.aspx. See remarks.

    Activating the proxy account: http://msdn.microsoft.com/en-us/library/ms190359.aspx

    In particular:

    xp_cmdshell Proxy Account

    When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

    The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

    Craig, I looked at BOL for this following your suggestions, and I have a question:

    Looks like you must create a Windows account, grant it NTFS permissions to access the relevant shares, etc., then create SQL login based on the Windows user, etc. BUT: doesn't this tie all non-sysadmin execution of xp_cmdshell to that proxy account?

    So how would you ever configure this to allow various users to have differing levels of permissions for different purposes? Am I missing something?

    Thanks for any clarification you or anyone else can offer. I don't use xp_cmdshell at all, but this looks like a good thread to learn more about SQL security,

    Rich

  • June 22, 2011 at 7:42 am

    #1342378

    haha good picture the kid with the bazooka (if the security inspector sees it hes gonna jump 2 meters)

    the fact is that i prepared a procedure with use of xp_cmdshell, with 3 steps

    -enable xp_cmdshell

    -use of the xp_cmdshell

    -disable xp_cmdshell

  • June 22, 2011 at 7:42 am

    #1342379

    Jeff Moden (6/21/2011)

    BWAA-HAAA!!!! I just got this very funny image of a kid picking up the bazooka backwards and Dad's brand new car is right behind him and... boy, is Mom gonna be pissed! :hehe:

    Bloody Hell! I can't get this image out of my noggin...it's times like this I wish I knew how to make Flash videos 😛

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

Viewing 15 posts - 76 through 90 (of 107 total)

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