SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Check if xp_cmdshell is turned on and flip the bit


Check if xp_cmdshell is turned on and flip the bit

Author
Message
SQLShark
SQLShark
SSChasing Mays
SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)

Group: General Forum Members
Points: 626 Visits: 239
Comments posted to this topic are about the item Check if xp_cmdshell is turned on and flip the bit



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

Group: General Forum Members
Points: 155619 Visits: 41783
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLShark
SQLShark
SSChasing Mays
SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)

Group: General Forum Members
Points: 626 Visits: 239
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
Jeff Moden
SSC Guru
SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

Group: General Forum Members
Points: 155619 Visits: 41783
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLShark
SQLShark
SSChasing Mays
SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)

Group: General Forum Members
Points: 626 Visits: 239
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
David Burrows
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13432 Visits: 10021
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
SQLShark
SSChasing Mays
SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)

Group: General Forum Members
Points: 626 Visits: 239
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
Jeff Moden
SSC Guru
SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

Group: General Forum Members
Points: 155619 Visits: 41783
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

Group: General Forum Members
Points: 155619 Visits: 41783
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

Group: General Forum Members
Points: 155619 Visits: 41783
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search