Check if xp_cmdshell is turned on and flip the bit

Question

Check if xp_cmdshell is turned on and flip the bit

SQLShark

SSCrazy

Points: 2214
More actions
February 16, 2017 at 8:54 pm

#318860

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

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

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

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.

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)

SQLShark SSCrazy Points: 2214 More actions · Answer 2

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: 1004323 More actions · Answer 3

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.

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)

SQLShark SSCrazy Points: 2214 More actions · Answer 4

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: 65142 More actions · Answer 5

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: 2214 More actions · Answer 6

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: 1004323 More actions · Answer 7

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.

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)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

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.

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)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 9

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.

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)

Thom A SSC Guru Points: 99130 More actions · Answer 10

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.
Larnu.uk

David Burrows SSC Guru Points: 65142 More actions · Answer 11

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: 1004323 More actions · Answer 12

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.

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)

SQLShark SSCrazy Points: 2214 More actions · Answer 13

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: 1004323 More actions · Answer 14

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.

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)