November 20, 2013 at 4:03 pm
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??
:w00t::-D
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
November 20, 2013 at 5:52 pm
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??
:w00t::-D
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
Change is inevitable... Change for the better is not.
November 20, 2013 at 8:57 pm
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
November 20, 2013 at 9:50 pm
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
Change is inevitable... Change for the better is not.
November 20, 2013 at 10:09 pm
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
November 20, 2013 at 10:29 pm
opc.three (11/20/2013)
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.
Heh... who on this good green Earth with even an ounce of concern for security would allow that to happen in an uncontrolled manner? Yes, I agree that there are many ways that, as an SA prived DBA, I could allow that to happen (emphasis on uncontrolled manner). That's part of my point. It either takes a person with SA privs to use it, never mind enable it. The exception to the enabling rule is that someone with Control Server privs could also enable it. Any DBA that gives a non-DBA those privs should be fired for reasons of bad security. The exception to direct usage is if some DBA is dumb enough to grant usage privs to a non-DBA user to execute xp_CmdShell directly. The DBA should be fired for that mistake, as well.
In a controlled manner, I see no problem with, for example, giving the user privs to do a DIR on a limited set of directories through a stored procedure that uses xp_CmdShell. I would never, however, give them privs to run xp_CmdShell directly.
And I understand about the backup thing. My question to you was do you know of any xp that can delete text or other files? I do. It's called xp_CmdShell 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2013 at 10:33 pm
Jeff Moden (11/20/2013)
opc.three (11/20/2013)
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.Heh... who on this good green Earth with even an ounce of concern for security would allow that to happen in an uncontrolled manner? Yes, I agree that there are many ways that, as an SA prived DBA, I could allow that to happen. That's part of my point. It either takes a person with SA privs to use it, never mind enable it. The exception to the enabling rule is that someone with Control Server privs could also enable it. Any DBA that gives a non-DBA those privs should be fired for reasons of bad security. The exception to direct usage is if some DBA is dumb enough to grant usage privs to a non-DBA user to execute xp_CmdShell directly. The DBA should be fired for that mistake, as well.
Ahhh, some common ground. And to that end, just because you can, doesn't mean you should 😉
And I understand about the backup thing. My question to you was do you know of any xp that can delete text or other files? I do. It's called xp_CmdShell 😉
And my question to you is, why would you ever need to delete text files on the host operating system's file system using T-SQL? The answer is you don't.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 20, 2013 at 10:36 pm
opc.three (11/20/2013)
Jeff Moden (11/20/2013)
opc.three (11/20/2013)
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.Heh... who on this good green Earth with even an ounce of concern for security would allow that to happen in an uncontrolled manner? Yes, I agree that there are many ways that, as an SA prived DBA, I could allow that to happen. That's part of my point. It either takes a person with SA privs to use it, never mind enable it. The exception to the enabling rule is that someone with Control Server privs could also enable it. Any DBA that gives a non-DBA those privs should be fired for reasons of bad security. The exception to direct usage is if some DBA is dumb enough to grant usage privs to a non-DBA user to execute xp_CmdShell directly. The DBA should be fired for that mistake, as well.
Ahhh, some common ground. And to that end, just because you can, doesn't mean you should 😉
And I understand about the backup thing. My question to you was do you know of any xp that can delete text or other files? I do. It's called xp_CmdShell 😉
And my question to you is, why would you ever need to delete text files on the host operating system's file system using T-SQL? The answer is you don't.
You're correct. I don't. I could use Powershell or SSIS or DOS or PERL or VB/VBScript, Java Script, or any of a thousand other tools... but I prefer not to have code scattered all over. If I can do it all from a single point, then things are a whole lot easier to manage and automate.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2013 at 10:42 pm
Jeff Moden (11/20/2013)
opc.three (11/20/2013)
Jeff Moden (11/20/2013)
opc.three (11/20/2013)
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.Heh... who on this good green Earth with even an ounce of concern for security would allow that to happen in an uncontrolled manner? Yes, I agree that there are many ways that, as an SA prived DBA, I could allow that to happen. That's part of my point. It either takes a person with SA privs to use it, never mind enable it. The exception to the enabling rule is that someone with Control Server privs could also enable it. Any DBA that gives a non-DBA those privs should be fired for reasons of bad security. The exception to direct usage is if some DBA is dumb enough to grant usage privs to a non-DBA user to execute xp_CmdShell directly. The DBA should be fired for that mistake, as well.
Ahhh, some common ground. And to that end, just because you can, doesn't mean you should 😉
And I understand about the backup thing. My question to you was do you know of any xp that can delete text or other files? I do. It's called xp_CmdShell 😉
And my question to you is, why would you ever need to delete text files on the host operating system's file system using T-SQL? The answer is you don't.
You're correct. I don't. I could use Powershell or SSIS or DOS or PERL or VB/VBScript, Java Script, or any of a thousand other tools... but I prefer not to have code scattered all over. If I can do it all from a single point, then things are a whole lot easier to manage and automate.
That's fair and I completely understand your position. But none of the tools you mention suffer from the security or auditing problems xp_cmdshell does. None of them intrinsically obfuscate the executors identity, potentially elevating their permissions in the process. So, it's clear you value the things you value, automation and simplicity of code management, more than leaving the security and auditing challenges xp_cmdshell brings with it on the sidelines. That's perfectly OK but that doesn't make those things any less present, or make it any less irresponsible for you to advocate a tool without the lengthy disclaimers it deserves, and those are some of the points I have been making for years now.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 20, 2013 at 10:52 pm
That's just it. There are no security challenges to it. I don't allow users to use xp_CmdShell directly. I don't allow apps to execute it directly (never mind them having SA privs). They have no chance of elevating their privs because they cannot use it that way but, even if they could, they wouldn't get far because I also limit what the SQL Server login and the SQL Server Agent login have privs to do. So far as audit goes, you can bet your sweet bippy that the stored procs that use xp_CmdShell log who called them. Heh... I even do that with some of the stored procs that don't call xp_CmdShell.
As for auditing, lets ask the question about how many apps that have insert/update/delete privs that don't pass the identity of the person using the app. Now That's a concern and that includes SSIS.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2013 at 11:00 pm
Jeff Moden (11/20/2013)
That's just it. There are no security challenges to it. I don't allow users to use xp_CmdShell directly. I don't allow apps to execute it directly (never mind them having SA privs). They have no chance of elevating their privs because they cannot use it that way but, even if they could, they wouldn't get far because I also limit what the SQL Server login and the SQL Server Agent login have privs to do. So far as audit goes, you can bet your sweet bippy that the stored procs that use xp_CmdShell log who called them. Heh... I even do that with some of the stored procs that don't call xp_CmdShell.
And the sysadmin members? ...oh that's right, you argue that you must trust them implicitly.
As for auditing, lets ask the question about how many apps that have insert/update/delete privs that don't pass the identity of the person using the app. Now That's a concern and that includes SSIS.
If you're doing it right then your internal apps are written such that your app users maintain their identity all the way through the stack and in a SQL Server context ORIGINAL_LOGIN() will be your friend. If you're dealing with a public-facing website that allows users to contribute or manage content and you don't have individual database Logins per web-user (which you won't if you want to scale up leveraging conection pooling of any kind) then you have a whole different set of auditing challenges.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 21, 2013 at 8:27 am
opc.three (11/20/2013)
And the sysadmin members? ...oh that's right, you argue that you must trust them implicitly.
Jeez, Orlando. If you can't trust an SQL Server Admin or a Windows Admin to do the job you hired them for, then they shouldn't be sysadmin members or you need to fire them for being untrustworthy, etc., because they can't do their job right if they can't be trusted. It's that simple. Sure, you can setup some really strict auditing but who's going to audit the person who sets that up and monitors the logs?
You have to trust someone or it's time to turn off the computers and go home.
If you're doing it right then your internal apps are written such that your app users maintain their identity all the way through the stack and in a SQL Server context ORIGINAL_LOGIN() will be your friend. If you're dealing with a public-facing website that allows users to contribute or manage content and you don't have individual database Logins per web-user (which you won't if you want to scale up leveraging conection pooling of any kind) then you have a whole different set of auditing challenges.
Correct. In comparison to the number of apps that are written in such a thoughtful and security-wise fashion, what percentage of apps have you seen that are written incorrectly? Like I said, that's a real concern.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2013 at 10:38 am
Jeff Moden (11/21/2013)
opc.three (11/20/2013)
And the sysadmin members? ...oh that's right, you argue that you must trust them implicitly.
Jeez, Orlando. If you can't trust an SQL Server Admin or a Windows Admin to do the job you hired them for, then they shouldn't be sysadmin members or you need to fire them for being untrustworthy, etc., because they can't do their job right if they can't be trusted. It's that simple. Sure, you can setup some really strict auditing but who's going to audit the person who sets that up and monitors the logs?
You have to trust someone or it's time to turn off the computers and go home.
Not quite. This is why network security, database admin and system admin are separate groups within a large organization. Like I said, for you your approach works and in most shops "xp_cmdshell is safe" is true enough, but irresponsible to simply bandy about.
If you're doing it right then your internal apps are written such that your app users maintain their identity all the way through the stack and in a SQL Server context ORIGINAL_LOGIN() will be your friend. If you're dealing with a public-facing website that allows users to contribute or manage content and you don't have individual database Logins per web-user (which you won't if you want to scale up leveraging conection pooling of any kind) then you have a whole different set of auditing challenges.
Correct. In comparison to the number of apps that are written in such a thoughtful and security-wise fashion, what percentage of apps have you seen that are written incorrectly? Like I said, that's a real concern.
Absolutely agree. It is a real shortcoming in many app designs. From personal experience and reading the experiences of others I suspect the security in most apps that interact with SQL Server are poorly thought our or poorly implemented when it comes to database security and auditing. From my perspective, the introduction of xp_cmdshell into any stack immediately brings the app into that poorly thought out or poorly implemented category due to its security and auditing shortcomings.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 22, 2013 at 3:16 am
Hany Helmy (11/11/2013)
Never used the SQLCMD before; but I guessed it right; lol 🙂
exactly same answer
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply