April 8, 2013 at 7:42 am
patrickmcginnis59 10839 (4/8/2013)
opc.three (4/7/2013)
Jeff Moden (4/6/2013)
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
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 8, 2013 at 8:01 am
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.
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 8, 2013 at 8:10 am
mister.magoo (4/8/2013)
patrickmcginnis59 10839 (4/8/2013)
opc.three (4/7/2013)
Jeff Moden (4/6/2013)
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.
April 8, 2013 at 8:47 am
patrickmcginnis59 10839 (4/8/2013)
opc.three (4/7/2013)
Jeff Moden (4/6/2013)
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
April 8, 2013 at 8:50 am
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)
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
April 8, 2013 at 9:14 am
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)
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.
April 8, 2013 at 11:08 am
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
Change is inevitable... Change for the better is not.
April 8, 2013 at 2:25 pm
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
April 8, 2013 at 4:31 pm
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
Change is inevitable... Change for the better is not.
April 8, 2013 at 7:13 pm
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
April 11, 2013 at 9:25 am
If you want to know if a sysadmin has enabled SA, I would ensure an audit is in place, with logging to a file. Set permissions for your sysadmin groups (Windows level) and the service account for SQL Server to write only. Set read permissions for the audit groups.
April 11, 2013 at 4:47 pm
opc.three (4/8/2013)
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.
The circle continues so you've compelled me to say it again. If someone has SA privs, they can get to the command line whether xp_CmdShell is enabled or not. If someone doesn't have SA privs, they can't get to the command line whether xp_CmdShell is enabled or not. I believe it's a narrow view to think otherwise.
You're turn. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2013 at 8:41 pm
Jeff Moden (4/11/2013)
opc.three (4/8/2013)
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.The circle continues so you've compelled me to say it again. If someone has SA privs, they can get to the command line whether xp_CmdShell is enabled or not. If someone doesn't have SA privs, they can't get to the command line whether xp_CmdShell is enabled or not. I believe it's a narrow view to think otherwise.
You're turn. 😉
I remember, gee, must be about 2 years ago now, on a similar thread when I tried to end the round-and-round with an "I'll agree to disagree." For whatever reason that idea did not seem amenable to you that day and I think you literally responded with something like "no, wait...", and proceeded to build more of a case in that followup post. At the time I had a suspicion that you thought I had some wrong thinking that you could simply correct with more dialogue. Hopefully it is clear at this point that we are just two people with different views on the same subject. It's funny too because I think we agree on almost all other topics where we have compared notes so in a way it's a shame we have spent so much time on this one.
I have made several points surrounding security, auditability and application design explaining why one should not choose to use xp_cmdshell and I stand by those points. You stand by your points regarding members of the sysadmin Role, having your code be code consistent within backups, only needing to know one language with a sprinkle of cmd and Power shell, so here we are. Nowhere new, really, but maybe a bit more educated about each other's position. Hopefully others have benefited from our various dialogues as well. At present, as with before, and all along since that day 2 years ago really, I'll agree to disagree.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 11, 2013 at 9:09 pm
Well said on all counts, Orlando. I did try to end our conversation a couple of posts back. Time for us to move on.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2013 at 9:39 pm
Jeff Moden and opc.three
Now that you two have finally agreed to disagree, I'm going to throw oil on the fire.
Vendors are not your enemy!
I (and our team) were responsible to upgrade our company's SW on our hosted servers and on the clients hosted implementations of our software. Our development staff hard coded the use of mixed mode and use of SA to create the added logins and roles needed to run replication and the rest of it.
More than once we ran into an IT staff that said "We can't give you the SA password." We had a choice, go around you and change the SA password, or wait for you to give it to us. We didn't care if you changed it after the upgrade.
The problem is that if you don't trust your vendors you are not logical. We don't want to harm the end-user or anyone else. We want the upgrade to work.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
Viewing 15 posts - 61 through 75 (of 76 total)
You must be logged in to reply to this topic. Login to reply