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 «««45678»»

The Command Shell Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 7:42 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 1,796, Visits: 5,800
patrickmcginnis59 10839 (4/8/2013)
opc.three (4/7/2013)
Jeff Moden (4/6/2013)

[quote][quote]Shifting gears a bit, you've been stressing the auditing aspect of things. What type of system auditing do you currently have setup on your machines?

I do not know. That is not my area of responsibility and I am not privy to what is being done from an auditing standpoint. I am pretty sure that is actually by design. It reduces the possibility that any one person can defeat the system if everyone is forced to operate on the network as themselves and there are distinct separations of responsibility. I think all of this points to the concept of layering security within an environment.

Does anybody know how to fully deny sa enabling xp_cmdshell without leaving a trail? Obviously disabling the agent would be an undesireable option. Also I want to assume the rogue sa has complete knowlege of all aspects of SQL server, ie., security through obscurity is not what I'm asking here.

Can I log this somehow without the rogue sa discovering where the log is at and modifying it accordingly? I will look some also but I'm just wondering what the folks who don't want xp_cmdshell running do to ensure it doesn't get enabled without a clear audit trail as it sounds like some folks deny xp_cmdshell and I was wondering whats the bulletproof method of doing so.


What is there to say that you are not the "rogue sa" who is trying to find a way round already existing security?


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1439830
    Posted Monday, April 8, 2013 8:01 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 6:36 AM
    Points: 1,707, Visits: 4,853
    Jeff Moden (4/7/2013)
    opc.three (4/7/2013)
    Jeff Moden (4/6/2013)
    If someone get's in as SA, it's not going to matter if you have xp_CmdShell turned off or not.

    You keep throwing this one back into the conversation as if it's the only thing that matters. It's only one aspect of the case for or against using xp_cmdshell. No one has or is saying that by itself leaving xp_cmdshell disabled is going to secure a system but there is no arguing that enabling it and sanctioning its use lowers the bar for security and compromises ones ability to audit the actions taking place in the system. Repeating what you said over and over doesn't change the fact that having xp_cmdshell enabled is a security threat. If you choose not to see or consider other aspects of issue, and I do believe you have entrenched yourself in that choice regardless of how many points are made contrary to your position, then that's your prerogative, i.e. I think we have reached an impasse and am content with leaving the conversation here given that no new progress is being made.

    [quote]Some shops have the SQL Agent service disabled in their environment.



    What were they using to schedule their jobs, then?

    Windows Task Scheduler I already mentioned, and one place a few years back implemented an enterprise job scheduling tool from UC4. An enterprise job scheduling tool from Computer Associates is being looked at in the current shop.

    Shifting gears a bit, you've been stressing the auditing aspect of things. What type of system auditing do you currently have setup on your machines?

    I do not know. That is not my area of responsibility and I am not privy to what is being done from an auditing standpoint. I am pretty sure that is actually by design. It reduces the possibility that any one person can defeat the system if everyone is forced to operate on the network as themselves and there are distinct separations of responsibility. I think all of this points to the concept of layering security within an environment.


    Heh... and you keep repeating as much as I do. I don't believe it's a security problem and you do for the reasons that each of us has stated. I believe that overall security is the problem and that the tools used by trusted individuals are not.

    But, as you say, we've reached an impasse that's probably boring everyone to tears and I'll save our mutual disagreement for another time.

    The good part about all of this is that people have been given both sides of the story rather than the usual one sided story. They now have enough information to make a choice and might be a heck of a lot smarter about security than they otherwise may have been. So, my hat is off to you with sticking to this dicsussion for as long as you have. A lot of good stuff came to the surface as a result. Well done, Orlando.

    Sometimes the solution to a problem can found by looking at it in reverse. Let's assume that you're a SQL Server sysadmin and you lregitimately need xp_cmdshell to work for some critical process. What could go wrong?
    Based on a Bing search, there are reports of xp_cmdshell occasionally failing, even for sysadmin accounts, with an error related to xplog70.dll. So, perhaps the host server admin could block the SQL Server admin's usage of xp_cmdshell by deleting or denying access to this .dll at the file system level.
    Post #1439849
    Posted Monday, April 8, 2013 8:10 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Today @ 6:49 AM
    Points: 362, Visits: 2,521
    mister.magoo (4/8/2013)
    patrickmcginnis59 10839 (4/8/2013)
    opc.three (4/7/2013)
    Jeff Moden (4/6/2013)

    [quote][quote]Shifting gears a bit, you've been stressing the auditing aspect of things. What type of system auditing do you currently have setup on your machines?

    I do not know. That is not my area of responsibility and I am not privy to what is being done from an auditing standpoint. I am pretty sure that is actually by design. It reduces the possibility that any one person can defeat the system if everyone is forced to operate on the network as themselves and there are distinct separations of responsibility. I think all of this points to the concept of layering security within an environment.

    Does anybody know how to fully deny sa enabling xp_cmdshell without leaving a trail? Obviously disabling the agent would be an undesireable option. Also I want to assume the rogue sa has complete knowlege of all aspects of SQL server, ie., security through obscurity is not what I'm asking here.

    Can I log this somehow without the rogue sa discovering where the log is at and modifying it accordingly? I will look some also but I'm just wondering what the folks who don't want xp_cmdshell running do to ensure it doesn't get enabled without a clear audit trail as it sounds like some folks deny xp_cmdshell and I was wondering whats the bulletproof method of doing so.


    What is there to say that you are not the "rogue sa" who is trying to find a way round already existing security?


    I could absolutely be that rogue sa as you say. However, if you examine the question, I'm NOT asking how to circumvent security, but how to implement it. In this case, your answer COULD indicate that you do not believe a rogue sa could be guarded against, ie., that the solution I'm asking for does not exist, that its impossible to completely track sa's use [edit: unauditable enabling] of xp_cmdshell.

    If providing security information instructs the intruder and can only cost the victim, perhaps we're not truly discussing a securable system. Perhaps opc.three's posts have all been a moot point and Jeff is unqualifiably correct. Remember, I'm not asking for security through obscurity.
    Post #1439856
    Posted Monday, April 8, 2013 8:47 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 9:16 PM
    Points: 7,126, Visits: 12,726
    patrickmcginnis59 10839 (4/8/2013)
    opc.three (4/7/2013)
    Jeff Moden (4/6/2013)

    [quote][quote]Shifting gears a bit, you've been stressing the auditing aspect of things. What type of system auditing do you currently have setup on your machines?

    I do not know. That is not my area of responsibility and I am not privy to what is being done from an auditing standpoint. I am pretty sure that is actually by design. It reduces the possibility that any one person can defeat the system if everyone is forced to operate on the network as themselves and there are distinct separations of responsibility. I think all of this points to the concept of layering security within an environment.

    Does anybody know how to fully deny sa enabling xp_cmdshell without leaving a trail? Obviously disabling the agent would be an undesireable option. Also I want to assume the rogue sa has complete knowlege of all aspects of SQL server, ie., security through obscurity is not what I'm asking here.

    Can I log this somehow without the rogue sa discovering where the log is at and modifying it accordingly? I will look some also but I'm just wondering what the folks who don't want xp_cmdshell running do to ensure it doesn't get enabled without a clear audit trail as it sounds like some folks deny xp_cmdshell and I was wondering whats the bulletproof method of doing so.

    What is "the agent" ?


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    Post #1439882
    Posted Monday, April 8, 2013 8:50 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 9:16 PM
    Points: 7,126, Visits: 12,726
    patrickmcginnis59 10839 (4/8/2013)
    mister.magoo (4/8/2013)
    patrickmcginnis59 10839 (4/8/2013)
    opc.three (4/7/2013)
    Jeff Moden (4/6/2013)

    [quote][quote]Shifting gears a bit, you've been stressing the auditing aspect of things. What type of system auditing do you currently have setup on your machines?

    I do not know. That is not my area of responsibility and I am not privy to what is being done from an auditing standpoint. I am pretty sure that is actually by design. It reduces the possibility that any one person can defeat the system if everyone is forced to operate on the network as themselves and there are distinct separations of responsibility. I think all of this points to the concept of layering security within an environment.

    Does anybody know how to fully deny sa enabling xp_cmdshell without leaving a trail? Obviously disabling the agent would be an undesireable option. Also I want to assume the rogue sa has complete knowlege of all aspects of SQL server, ie., security through obscurity is not what I'm asking here.

    Can I log this somehow without the rogue sa discovering where the log is at and modifying it accordingly? I will look some also but I'm just wondering what the folks who don't want xp_cmdshell running do to ensure it doesn't get enabled without a clear audit trail as it sounds like some folks deny xp_cmdshell and I was wondering whats the bulletproof method of doing so.


    What is there to say that you are not the "rogue sa" who is trying to find a way round already existing security?


    I could absolutely be that rogue sa as you say. However, if you examine the question, I'm NOT asking how to circumvent security, but how to implement it. In this case, your answer COULD indicate that you do not believe a rogue sa could be guarded against, ie., that the solution I'm asking for does not exist, that its impossible to completely track sa's use [edit: unauditable enabling] of xp_cmdshell.

    If providing security information instructs the intruder and can only cost the victim, perhaps we're not truly discussing a securable system. Perhaps opc.three's posts have all been a moot point and Jeff is unqualifiably correct. Remember, I'm not asking for security through obscurity.

    If your argument is "someone in the sysadmin Role can take down the system so why bother running a system with xp_cmdshell disabled" then I think you are completely missing the point and are only focusing on one aspect of the discussion.


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    Post #1439884
    Posted Monday, April 8, 2013 9:14 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Today @ 6:49 AM
    Points: 362, Visits: 2,521
    opc.three (4/8/2013)
    patrickmcginnis59 10839 (4/8/2013)
    mister.magoo (4/8/2013)
    patrickmcginnis59 10839 (4/8/2013)
    opc.three (4/7/2013)
    Jeff Moden (4/6/2013)

    [quote][quote]Shifting gears a bit, you've been stressing the auditing aspect of things. What type of system auditing do you currently have setup on your machines?

    I do not know. That is not my area of responsibility and I am not privy to what is being done from an auditing standpoint. I am pretty sure that is actually by design. It reduces the possibility that any one person can defeat the system if everyone is forced to operate on the network as themselves and there are distinct separations of responsibility. I think all of this points to the concept of layering security within an environment.

    Does anybody know how to fully deny sa enabling xp_cmdshell without leaving a trail? Obviously disabling the agent would be an undesireable option. Also I want to assume the rogue sa has complete knowlege of all aspects of SQL server, ie., security through obscurity is not what I'm asking here.

    Can I log this somehow without the rogue sa discovering where the log is at and modifying it accordingly? I will look some also but I'm just wondering what the folks who don't want xp_cmdshell running do to ensure it doesn't get enabled without a clear audit trail as it sounds like some folks deny xp_cmdshell and I was wondering whats the bulletproof method of doing so.


    What is there to say that you are not the "rogue sa" who is trying to find a way round already existing security?


    I could absolutely be that rogue sa as you say. However, if you examine the question, I'm NOT asking how to circumvent security, but how to implement it. In this case, your answer COULD indicate that you do not believe a rogue sa could be guarded against, ie., that the solution I'm asking for does not exist, that its impossible to completely track sa's use [edit: unauditable enabling] of xp_cmdshell.

    If providing security information instructs the intruder and can only cost the victim, perhaps we're not truly discussing a securable system. Perhaps opc.three's posts have all been a moot point and Jeff is unqualifiably correct. Remember, I'm not asking for security through obscurity.

    If your argument is "someone in the sysadmin Role can take down the system so why bother running a system with xp_cmdshell disabled" then I think you are completely missing the point and are only focusing on one aspect of the discussion.

    Thats fine, I'm perfectly ok with your reply. I do believe that we can drill down on these issues, and for those interested in drilling down, my position is that (to play advocate of a particular side of this debate for the sake of discussion) in the previous post, I'm looking to audit the undesired use of xp_cmdshell.

    One direction I did find has Microsoft suggesting we use another sql server set up for a security audit administrator, deny sa access on this server to the original server sa account and set up "sql server audit" as described here http://msdn.microsoft.com/en-us/library/cc280386.aspx. This doesn't look to require "security by obscurity" but is an actual mechanism for tracking ops on one server using another. Probably lots of pesky details to be worked out, but I think that page pretty well was what I was looking for.
    Post #1439899
    Posted Monday, April 8, 2013 11:08 AM


    SSC-Dedicated

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

    Group: General Forum Members
    Last Login: 2 days ago @ 1:53 PM
    Points: 35,366, Visits: 31,905
    opc.three (4/8/2013)
    If your argument is "someone in the sysadmin Role can take down the system so why bother running a system with xp_cmdshell disabled" then I think you are completely missing the point and are only focusing on one aspect of the discussion.


    Gosh. I thought you were going to quit.

    The big point that I've been trying to make is that simply disabling xp_CmdShell offers only a thin veil of auditability and does not act as any kind of an obstacle to a would-be attacker or mischievous user, either internally or externally, if that person has SA privs.


    --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 #1439947
    Posted Monday, April 8, 2013 2:25 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 9:16 PM
    Points: 7,126, Visits: 12,726
    Jeff Moden (4/8/2013)
    opc.three (4/8/2013)
    If your argument is "someone in the sysadmin Role can take down the system so why bother running a system with xp_cmdshell disabled" then I think you are completely missing the point and are only focusing on one aspect of the discussion.


    Gosh. I thought you were going to quit.

    I did...quit debating the same disagreement over the comment you continue to reiterate, with no progress. There is no chance I am going to quit steering people away from implementing xp_cmdshell in favor of more secure, more auditable and more robust tools like PowerShell, SSIS and .NET.


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
    Post #1440022
    Posted Monday, April 8, 2013 4:31 PM


    SSC-Dedicated

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

    Group: General Forum Members
    Last Login: 2 days ago @ 1:53 PM
    Points: 35,366, Visits: 31,905
    opc.three (4/8/2013)
    Jeff Moden (4/8/2013)
    opc.three (4/8/2013)
    If your argument is "someone in the sysadmin Role can take down the system so why bother running a system with xp_cmdshell disabled" then I think you are completely missing the point and are only focusing on one aspect of the discussion.


    Gosh. I thought you were going to quit.

    I did...quit debating the same disagreement over the comment you continue to reiterate, with no progress. There is no chance I am going to quit steering people away from implementing xp_cmdshell in favor of more secure, more auditable and more robust tools like PowerShell, SSIS and .NET.


    Then game on, ol' firend. Whether it get's used or not, turning xp_CmdShell off is like holding up a bathtowel to sheild yourself from a nuclear blast. The real security problems need to be addressed instead.


    --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 #1440060
    Posted Monday, April 8, 2013 7:13 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 9:16 PM
    Points: 7,126, Visits: 12,726
    Jeff Moden (4/8/2013)
    opc.three (4/8/2013)
    Jeff Moden (4/8/2013)
    opc.three (4/8/2013)
    If your argument is "someone in the sysadmin Role can take down the system so why bother running a system with xp_cmdshell disabled" then I think you are completely missing the point and are only focusing on one aspect of the discussion.


    Gosh. I thought you were going to quit.

    I did...quit debating the same disagreement over the comment you continue to reiterate, with no progress. There is no chance I am going to quit steering people away from implementing xp_cmdshell in favor of more secure, more auditable and more robust tools like PowerShell, SSIS and .NET.


    Then game on, ol' firend. Whether it get's used or not, turning xp_CmdShell off is like holding up a bathtowel to sheild yourself from a nuclear blast. The real security problems need to be addressed instead.

    If nuclear blasts were the only reason to leave xp_cmdshell disabled then you would have a great point. Unfortunately, that is simply too narrow a view.


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

    Add to briefcase «««45678»»

    Permissions Expand / Collapse