Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQLCMD Expand / Collapse
Author
Message
Posted Sunday, November 10, 2013 10:32 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:24 AM
Points: 3,537, Visits: 2,647
Dana Medley (11/6/2013)
Jeff Moden (11/5/2013)
SQLRNNR (11/5/2013)
Jeff Moden (11/5/2013)
Heh... I had to think about it. For me, the correct answer would have started with "Exec xp_CmdShell".


not sp_configure??



Heck no. I leave xp_CmdShell on all the time. There's no security advantage to turning it off.


+1


+2.
Post #1513001
Posted Monday, November 11, 2013 6:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:44 AM
Points: 1,124, Visits: 513
Never used the SQLCMD before; but I guessed it right; lol
Post #1513108
Posted Tuesday, November 12, 2013 12:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
Nice question. We just went the route of genericizing some scripts using sqlcmd and having access to it within SSMS is a nice feature for developing those. Best of both worlds, IDE for dev, command line for automation, and no xp_cmdshell thank you very much

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1513344
Posted Tuesday, November 19, 2013 2:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
Jeff Moden (11/5/2013)
SQLRNNR (11/5/2013)
Jeff Moden (11/5/2013)
Heh... I had to think about it. For me, the correct answer would have started with "Exec xp_CmdShell".


not sp_configure??



Heck no. I leave xp_CmdShell on all the time. There's no security advantage to turning it off.

Sorry, I was lead back here looking for another previous post. Your statement requires qualification Jeff.

Maybe in some environments that is true enough, obviosuly it is in your environment and fo ryou, but in general you're statement is incorrect. Having xp_cmdshell enabled reduces the overall security and auditability of an instance.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1515779
Posted Wednesday, November 20, 2013 3:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
opc.three (11/19/2013)
Jeff Moden (11/5/2013)
SQLRNNR (11/5/2013)
Jeff Moden (11/5/2013)
Heh... I had to think about it. For me, the correct answer would have started with "Exec xp_CmdShell".


not sp_configure??



Heck no. I leave xp_CmdShell on all the time. There's no security advantage to turning it off.

Sorry, I was lead back here looking for another previous post. Your statement requires qualification Jeff.

Maybe in some environments that is true enough, obviosuly it is in your environment and fo ryou, but in general you're statement is incorrect. Having xp_cmdshell enabled reduces the overall security and auditability of an instance.


No, its not. It's just wrong for you and what you believe.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516236
Posted Wednesday, November 20, 2013 4:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
Jeff Moden (11/20/2013)
opc.three (11/19/2013)
Jeff Moden (11/5/2013)
SQLRNNR (11/5/2013)
Jeff Moden (11/5/2013)
Heh... I had to think about it. For me, the correct answer would have started with "Exec xp_CmdShell".


not sp_configure??



Heck no. I leave xp_CmdShell on all the time. There's no security advantage to turning it off.

Sorry, I was lead back here looking for another previous post. Your statement requires qualification Jeff.

Maybe in some environments that is true enough, obviosuly it is in your environment and fo ryou, but in general you're statement is incorrect. Having xp_cmdshell enabled reduces the overall security and auditability of an instance.


No, its not. It's just wrong for you and what you believe.

Sorry Jeff, but I must disagree with you, again. You can attempt to put this onto a belief, or you can refer to the facts. I prefer to refer to the facts.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1516247
Posted Wednesday, November 20, 2013 5:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
opc.three (11/20/2013)
Jeff Moden (11/20/2013)
opc.three (11/19/2013)
Jeff Moden (11/5/2013)
SQLRNNR (11/5/2013)
Jeff Moden (11/5/2013)
Heh... I had to think about it. For me, the correct answer would have started with "Exec xp_CmdShell".


not sp_configure??



Heck no. I leave xp_CmdShell on all the time. There's no security advantage to turning it off.

Sorry, I was lead back here looking for another previous post. Your statement requires qualification Jeff.

Maybe in some environments that is true enough, obviosuly it is in your environment and fo ryou, but in general you're statement is incorrect. Having xp_cmdshell enabled reduces the overall security and auditability of an instance.


No, its not. It's just wrong for you and what you believe.

Sorry Jeff, but I must disagree with you, again. You can attempt to put this onto a belief, or you can refer to the facts. I prefer to refer to the facts.


Crud. Here we go again.

Tell me how you can stop an SA from getting to the command prompt through SQL Server? You can't.
Who are the only people that can enable xp_CmdShell? Only SAs.
Tell me the only people that can use xp_CmdShell (unless you messed up with a proxy)? Only SAs.
Can anyone other than an SA turn on xp_CmdShell? No.
Can you determine WHO turned on xp_CmdShell through through auditing? You can tell what the SPID was but there is no true conviction path to the person. You could even make a self deleting job do your dirty work for you.

It would be better if you spent more time telling people how to prevent bad guys from getting in as SA them telling them to turn off xp_Cmdshell because turning it off provides no extra security from internal people or external people that get in as SA.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516278
Posted Wednesday, November 20, 2013 8:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
Jeff Moden (11/20/2013)
Tell me how you can stop an SA from getting to the command prompt through SQL Server? You can't.

You can achieve it through the use of WMI. I showed how to detect when a cmd shell is spawned by the sqlsrvr.exe process on another thread here on SSC. If you can detect it, you can intercept it. Beyond WMI, it is quite likely that one could write a program to intercept and prevent use of cmd using Windows process hooks, but that's hocus pocus to me. I am thinking about how Windows can prevent non-interactive users from running programs (the 'are you sure you want to execute this' type popups), or how anti-virus software can decide a given executable should not be allowed to execute.

Who are the only people that can enable xp_CmdShell? Only SAs.

And why would they feel compelled to do that?

If you think you need xp_cmdshell, I say you're doing something wrong. Using it leads to the desire to elevate the service-accounts permissions which only further puts your system and data at risk.

If you only use xp_cmdshell to delete old files from the backup directory, then why not use one of the expended stored procs already built into the system? Give me an example of when you need to use xp_cmdshell and I'll give you several options on how to avoid its use. It simply is not required.

Allowing the use of xp_cmdshell in effect sanctions the idea that a user can impersonate the service account without providing a password for that account. Do you see where I am going with this? I know you do, and it's ok if you disagree, but the argument is valid. Enabling and using xp_cmdshell makes your system less secure and less auditable.

Tell me the only people that can use xp_CmdShell (unless you messed up with a proxy)? Only SAs.

I can think of ways a non-SA could be setup so they could enable xp_cmdshell without the use of a proxy. Your point?

Can anyone other than an SA turn on xp_CmdShell? No.

I can think of ways a non-SA could be setup so they could enable xp_cmdshell. What is your point?

Can you determine WHO turned on xp_CmdShell through through auditing?

Sure. Through the use of Audit, XE and Trace you have a fighting chance of isolating who made a change should a breach be detected. Could an SA turn these things off? Maybe, if they knew these technologies existed and had the minimum level of knowledge to work around them, but potentially not without causing a gap in the audit trail. The overarching point is the bar to attack the system in question and get away without being caught has been raised. Most breaches are internal. Trust is important, but so are checks, balances and verifications. Anyone who says "you can't stop SA so let's not bother trying to protect our systems" has become apathetic about their system's security.

You can tell what the SPID was but there is no true conviction path to the person.

See earlier comments.

You could even make a self deleting job do your dirty work for you.

You're assuming SQL Agent is in play. Let's just say it is on a hypothetical system, then you're right, and I have said this many times before, securing Agent must be a consideration when locking down a system. There are many places where folks are handed SA inadvertently by gaining membership in an AD Group improperly. Others gain it via poor security practices and the careless storage of configuration information written in plain-text, some from a post-in note or a packet sniffer. At any turn, those internal threats exist.

It would be better if you spent more time telling people how to prevent bad guys from getting in as SA them telling them to turn off xp_Cmdshell because turning it off provides no extra security from internal people or external people that get in as SA.

I work towards all of the above, and so should everyone. Are some of the things I mentioned too much for the general SQL shop to implement, probably, but it does not take away from the fact that categorically speaking, enabling and using xp_cmdshell makes a system less auditable and less secure.

If I may, you would be better off figuring out more secure and auditable alternatives to using xp_cmdshell than continuing to abuse T-SQL and SQL Server in general as if it were some one-size-fits-all application server technology. You have earned your seat at the proverbial table in the SQL community but in my opinion, in this particular context, you're absolutely misusing it and leading people astray.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1516303
Posted Wednesday, November 20, 2013 9:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
opc.three (11/20/2013)
I can think of ways a non-SA could be setup so they could enable xp_cmdshell without the use of a proxy. Your point?
I can think of ways a non-SA could be setup so they could enable xp_cmdshell. What is your point?



I don't believe either of the above is true but if it is, then you've made my point. Turning it off is a futile security effort. I'd also like to know how you think either of the above can actually be accomplished.

Also, you should post you WMI intercept code on this thread, as well. I'm sure other people would be interested in it... especially since you don't actually have anything that will stop the use. I don't mean that sarcastically, either. It may be that someone could help setup a block (although I believe that might have some adverse affects on the operation of things like backups, etc).

And no... the XP that deletes files is good only for backup and certain types of report files. If you know of another XP that can delete text files, I'd sure like to know about it.

You and I started this argument several years ago and you said that you had a "Visceral Fear" about the use of xp_CmdShell. I agree that you do.

In the meantime, I'm going to continue to advocate the use of xp_CmdShell and you're going to continue to advocate against it. I am not, however, going to get into any more long discussions with you about it. I'm simply going to state the you have a visceral fear about it and that it's up to the reader if they want to take on a like fear.

Trust is important, but so are checks, balances and verifications. Anyone who says "you can't stop SA so let's not bother trying to protect our systems" has become apathetic about their system's security.


I can assure you that I'm not apathetic about system security and I strongly resent the implication. Turning off xp_CmdShell does absolutely nothing to increase system security. Nothing. On the other hand, I can very carefully control what the server can see through xp_CmdShell and the SQL Agent. And, there's just as much risk that someone will gain extraordinary privs through an AD mistake that will allow them to use Powershell, SSIS, or even Word/Excel to do extremely grave damage. If you don't have control over AD, then turning off xp_CmdShell isn't going to do you any good anyway.

As far as creating xp_CmdShell use habits go, that's what code reviews by the DBA are for. As a DBA, at least I have control over those. I don't have control over people that use Powershell or SSIS.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1516312
Posted Wednesday, November 20, 2013 10:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
Jeff Moden (11/20/2013)
opc.three (11/20/2013)
I can think of ways a non-SA could be setup so they could enable xp_cmdshell without the use of a proxy. Your point?
I can think of ways a non-SA could be setup so they could enable xp_cmdshell. What is your point?



I don't believe either of the above is true but if it is, then you've made my point. Turning it off is a futile security effort. I'd also like to know how you think either of the above can actually be accomplished.

Chances are I could accomplish this through a SQLCLR. I could definitely setup an Agent job that a low-priv user could run by executing a stored proc. Potentially a signed stored procedure but haven't tried it. I would bet there are lots of ways...

Also, you should post you WMI intercept code on this thread, as well. I'm sure other people would be interested in it... especially since you don't actually have anything that will stop the use. I don't mean that sarcastically, either. It may be that someone could help setup a block (although I believe that might have some adverse affects on the operation of things like backups, etc).

http://www.sqlservercentral.com/Forums/FindPost1450182.aspx

And no... the XP that deletes files is good only for backup and certain types of report files. If you know of another XP that can delete text files, I'd sure like to know about it.

Right, I said old files from the backup directory, implying database backup files.

You and I started this argument several years ago and you said that you had a "Visceral Fear" about the use of xp_CmdShell. I agree that you do.

In the meantime, I'm going to continue to advocate the use of xp_CmdShell and you're going to continue to advocate against it. I am not, however, going to get into any more long discussions with you about it. I'm simply going to state the you have a visceral fear about it and that it's up to the reader if they want to take on a like fear.

That was a creative use of words on my part meant to make a point, not meant to be taken literally. This is not the first time you have brought that comment up, and I am impressed that you do bring that up periodically because it means my wordsmithing worked, you remembered! Hopefully others will take the decision to enable xp_cmdshell seriously and after reading some of our long discussions, choose to take a route other than exposing a conduit into the host operating system via their SQL Server that supports identity obfuscation and many times permission elevation. It's one of those things, as the saying goes, if I can prevent one person from making the mistake of enabling and using xp_cmdshell then all the typing and time spent was worth it.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1516314
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse