Check if xp_cmdshell is turned on and flip the bit

  • SQLShark

    SSCrazy

    Points: 2196

    Comments posted to this topic are about the item Check if xp_cmdshell is turned on and flip the bit

  • Jeff Moden

    SSC Guru

    Points: 994701

    Great script, Edward.  I'd only use it once, though, because I'd turn it on and leave it on.  There's no extra safety to having it turned off.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • SQLShark

    SSCrazy

    Points: 2196

    Jeff, in our environment it is a security violation to leave this turned on. So we flip it on to use the functionality and then flip it off when done. I automated a DR restore and once each db is restored it is copied to a archive directory and the house keeping is performed form there. Not as painful automated. Thanks for the compliment ! Ed

  • Jeff Moden

    SSC Guru

    Points: 994701

    SQLShark - Friday, February 17, 2017 6:13 AM

    Jeff, in our environment it is a security violation to leave this turned on. So we flip it on to use the functionality and then flip it off when done. I automated a DR restore and once each db is restored it is copied to a archive directory and the house keeping is performed form there. Not as painful automated. Thanks for the compliment ! Ed

    Understood.  But, considering that only those with sysadmin or controlserver privs can use it or turn it on or off, what amount of security do they think that's going to provide?  If a hacker gets in with sysadmin privs, it won't even be a 1ms speed bump for their attack software.  If you have a bunch of people that aren't supposed to be using it but have sysadmin or controlserver privs, then you have a security problem.  Have xp_CmdShell turned on isn't a security problem.  If you'd like, I could send you my presentation on why xp_CmdShell isn't a security problem and what you really need to do to secure your system.

    And, yeah... it actually was a compliment because most people won't allow usage of xp_CmdShell ever.  It's good to see someone that understands what a valuable tool it is.  It's just that enabling it to use it and disabling it when done is an unnecessary complication of code that doesn't provide any extra security.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • SQLShark

    SSCrazy

    Points: 2196

    I agree, this is the first shop I worked at that it is a requirement to have it turned off. As you noted normally I am use to just using it and calling it up very useful utility. Before PowerShell I used osql, then sqlcmd and a huge fan of xp_cmdshell to call my cmdline utilites for deploying code or querying servers for information. I would like to see the deck on it you mentioned. You can email me at sqlscripters@Hotmail.com I am older and have been around for a while so don't laugh but this is how I monitored my SQL engines back in the day and it worked well keeping the code inside each data center:
    Had a Create Proc with encryption above...Also used the OLE to call CDONTS then CDOSYS mail objects for sending alerts. Yeah that's old school....
    DECLARE @result int
    EXEC @result = master..xp_cmdshell 'Osql -SSQLSERVER -E , 'NO_OUTPUT'
    Print @result
    IF (@result = 0)
    Begin
    Return
    End
    Else
    Begin
    Print 'Send alert'
    End
    Declare @fr varchar(50),@t varchar (50),@sub varchar(50), @bod varchar(55),@time varchar(19)
    Select @time = (Left(CONVERT(varchar, CURRENT_TIMESTAMP, 0), 19))
    Select @fr = '*'
    select @sub = 'Alert'
    select @bod = 'MSSQLService is down on ServerName '+@time

  • David Burrows

    SSC Guru

    Points: 64525

    Jeff Moden - Friday, February 17, 2017 8:21 AM

    ...  If you'd like, I could send you my presentation on why xp_CmdShell isn't a security problem and what you really need to do to secure your system.

    Yes please, I'd be interested to see that 🙂

    Far away is close at hand in the images of elsewhere.
    Anon.

  • SQLShark

    SSCrazy

    Points: 2196

    I like your signature also, I have pushed code example my DMV toolkit 2,700 lines of procs to 200+ servers using a simple read_only cursor to read server names form my table and call up sqlcmd in maybe 16 minutes or less. Or change the SA password in 6-8 minutes on the 200+ if ever compromised. I have been learning PS and it has some cool features I have used for automation such as moving a TDE encrypted db from server A to server B, masking the data, remove encryption ect...but find I am using invoke-sqlcmd which is sqlcmd pumped up.. I did my first big push with it and we touched 726 serves in 1hour and 15 min, not too shabby....

  • Jeff Moden

    SSC Guru

    Points: 994701

    I'll send it to the two of you over the weekend.  Thanks for your interest.  I'm converting it to an article so I'd be interested in your feedback, good or bad.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994701

    Actually, I had previously uploaded the presentation to this site.  You can find it at the following URL.  Hard to believe I first did this one almost 4 years ago.
    https://www.sqlservercentral.com/Forums/Attachment17582.aspx

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994701

    Jeff Moden - Friday, February 17, 2017 11:10 PM

    Actually, I had previously uploaded the presentation to this site.  You can find it at the following URL.  Hard to believe I first did this one almost 4 years ago.
    https://www.sqlservercentral.com/Forums/Attachment17582.aspx

    Any feedback?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Thom A

    SSC Guru

    Points: 98346

    Jeff Moden - Saturday, February 25, 2017 12:26 PM

    Jeff Moden - Friday, February 17, 2017 11:10 PM

    Actually, I had previously uploaded the presentation to this site.  You can find it at the following URL.  Hard to believe I first did this one almost 4 years ago.
    https://www.sqlservercentral.com/Forums/Attachment17582.aspx

    Any feedback?

    Found it really interesting myself Jeff. We have xp_cmdshell disabled at work, for the exact reasons people "believe" its a bad idea. I think that is mainly a hangover from prior versions of SQL that our senior DBA used.

    I'm more than happy to admit there have been several times when i would have loved to be able to use xp_cmdshell and haven't. Thus I've had to create SSIS packages to do the task instead. I doubt it'll change things, but I'll bring up this, or our finalised article, with the senior DBA at some point. I know both our Servcie accounts are quite limited in what they can already do on the network.

    I assume, however, that question 10 was meant to read "Does disabling xp_CmdShell change the rules as to who can use it according to Answer #9?" though, as otherwise it's a repeat of question 9.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • David Burrows

    SSC Guru

    Points: 64525

    Jeff Moden - Saturday, February 25, 2017 12:26 PM

    Jeff Moden - Friday, February 17, 2017 11:10 PM

    Actually, I had previously uploaded the presentation to this site.  You can find it at the following URL.  Hard to believe I first did this one almost 4 years ago.
    https://www.sqlservercentral.com/Forums/Attachment17582.aspx

    Any feedback?

    Found the information useful and it would make a good article.
    Can't think of any changes or additions.
    Currently have it disabled, probably due to the misconceptions you indicated.
    Although do not have a need, as yet, to enable it 😀

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Jeff Moden

    SSC Guru

    Points: 994701

    Thank you both for the feedback.  In fact, I've been considering converting it to an article so I really appreciate 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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • SQLShark

    SSCrazy

    Points: 2196

    Great Deck, I agree with all your points. Sometimes it would be nice to include a DBA in security meetings for playing the other side of the fence such as a hacker can just turn it on in 1 second and use it so why turn it off. Focus on keeping the bad seeds at bay.
    Ed

  • Jeff Moden

    SSC Guru

    Points: 994701

    SQLShark - Monday, February 27, 2017 8:24 AM

    Great Deck, I agree with all your points. Sometimes it would be nice to include a DBA in security meetings for playing the other side of the fence such as a hacker can just turn it on in 1 second and use it so why turn it off. Focus on keeping the bad seeds at bay.
    Ed

    Our local PASS chapter had a guy come in and demonstrate some of the hacking tools.  There's some really spooky stuff they can do and, as several people have said, we have to be right all the time... a hacker only needs to be right once.  Once his code successfully broke in with "SA" privs, it determined that xp_CmdShell was off, turned it on, wrote some "back door guarantee" code to the disk, and then turned xp_CmdShell off.  The whole thing was measured in milliseconds.

    Like you said, folks need to focus on keeping the bad guys out even if the bad guys might be insiders.

    Thank you very much for the feedback.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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