April 3, 2013 at 5:11 pm
Steve Jones - SSC Editor (4/3/2013)
There is a potential issue for injection attacks as TravisDBA pointed out....
I brought it up in a post before that, as well.
--Jeff Moden
April 3, 2013 at 9:01 pm
I've been following this post since the beginning. You want to talk about not seeing the forest for the trees? This is getting into not seeing a tree because of the weeds.
I look at that the use of xp_cmdshell as a production DBA as a simple need. There are some things that can be achieved through CLR or PS, but take about 15 steps to the three needed to use xp_cmdshell.
A little background on my thought processes. In eleventh grade I was taking two programming classes. One was BASIC (not VB) and the other was assembler. My senior year was Pascal and COBOL. From my touching COBOL, I determined I'd starve on the street before I would program in it.
So now more than twenty years forward in time, I'm watching this argument and laughing at it. If you have someone that is at the sysadmin level and can use xp_cmdshell and can't find a way to beat the audit log then they deserve to be caught. If the SQL service account has any raised domain level permissions then you didn't set up it up right in the first place.
This was like my last company. The auditors demanded that we have a regular user account and then use a separate admin account for doing the admin work. We would login into our computers as our regular account then had a batch file to launch everything else as an admin privilege with an input put parameter for the password. Why? Because we realized the stupidity.
The argument over whether xp_cmdshell is a threat means that someone, with decent knowledge, is already so far in it doesn't matter. Now I catch a programmer doing that crap, I'm going to bust his butt. But the usual end-user using an application is not going to be the danger. So trying to guard against the normal edge is good. But going to paranoiac extremes generally makes no sense.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
April 4, 2013 at 7:59 am
Jim P. (4/3/2013)
The argument over whether xp_cmdshell is a threat means that someone, with decent knowledge, is already so far in it doesn't matter. Now I catch a programmer doing that crap, I'm going to bust his butt. But the usual end-user using an application is not going to be the danger. So trying to guard against the normal edge is good. But going to paranoiac extremes generally makes no sense.
Well said. That's my whole point about xp_CmdShell. If someone gets in deep enough (meaning with "SA" privs, in this case), you're dead even if it's turned off and depriving DBAs of its SA-only usage just doesn't make sense to me.
For the record, I'm also one of those folks that will allow it in carefully constructed application-facing stored procedures where the user doesn't actually have privs to run xp_CmdShell directly but that's a whole different argument.
--Jeff Moden
April 4, 2013 at 9:21 am
Leaving the development aspects aside per Steve's earlier comment...
Why do folks need xp_cmdshell for admin tasks? I definitely have a sense of people's attachment and willingness to use xp_cmdshell despite the fact that it brings with it various persistent and latent security risks. Comments about how using xp_cmdshell makes things simpler or easier just do not compute. Maybe it is because early in my career I ran into more than one mess of a system that heavily leveraged xp_cmdshell where I was asked to rewrite xp_cmdshell out of the system and either consciously or subconsciously made up my mind never to use it in new work. I found other ways to do things without xp_cmdshell that suit me quite well and while the languages have changed the techniques I have developed have come in quite handy in distributed environments where I have to manage more than just a few instances. I think I am being productive, and my scripts tend to scale out to manage n-instances quite easily, but there is always room for improvement. What types of tasks does xp_cmdshell make so much easier? Or reframed, why can't you live without xp_cmdshell?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2013 at 9:44 am
This is beginning to sound like the "Why do we need Cursors?" debate. Just like cursors, I'd be highly sceptical why anyone would need to leverage xp_cmdshell in a stored procedure. Still it's a useful tool for ad-hoc sysadmin tasks, like when the sysadmin doesn't have a login on the host operating system.
Case in point: Just this morning I was asked to rename a database (AbcCorp -> AbcCorpOld) in the DEV and QA environments and then create a new database with the same name (AbcCorp) and empty tables. To keep the physical file names consistent with the logical database name, I also had to detach, re-name files, and then re-attach the old database. However, to rename the files, I had to use xp_cmdshell, because I don't have a local login on the host operating server.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 4, 2013 at 11:30 am
opc.three (4/4/2013)
What types of tasks does xp_cmdshell make so much easier? Orreframed, why can't you live without xp_cmdshell?
A good note from Eric, but I'd say that often it's expediancy. I can get something done quickly with xp_cmdshell on the host OS that I might not have a feature for in SQL Server. I could do something in PoSh, but I'll say now that writing and testing a script in PoSh, at this point, is much more cumbersome and potentially fraught with danger than xp_cmdshell and basic DOS type operations.
That's certainly a reflection of my skill, but also of many other people's skills.
.NET is overkill for the one-off, quick, I need to do this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 4, 2013 at 6:26 pm
Eric M Russell (4/4/2013)
Case in point: Just this morning I was asked to rename a database (AbcCorp -> AbcCorpOld) in the DEV and QA environments and then create a new database with the same name (AbcCorp) and empty tables. To keep the physical file names consistent with the logical database name, I also had to detach, re-name files, and then re-attach the old database. However, to rename the files, I had to use xp_cmdshell, because I don't have a local login on the host operating server.
Thanks for posting Eric. I'll preface this by saying that I am not meaning to picking on you personally so please do not take it that way. I am picking on the generic scenario which I have a suspicion is probably very common and it goes straight to my point. If someone is tasked with doing something in the host OS'es file system then why do they not have direct access to make those changes in the context of their own account? The person not having access with their own account means someone who controls access in the environment either has not thought to grant those permissions, the DBA has not bothered to ask for those permissions to be granted either for fear of being shot down or because they already found an alternate way to do it without asking, or in the worst case they should not have the permissions at all and yet they gain access via xp_cmdshell. In any case there is a broken process somewhere or someone has violated an access rule, or both.
I have to do these types of tasks as well but I'll either access the server file system over the network via UNC, or I'll RDP to the server to make the file system changes locally. If and when I get around to it I am thinking of exploring Remote PowerShell which would effectively give me a PoSh prompt on my own machine that would actually be running commands on the remote server. Don Jones and Jeffrey Hicks compare Remote PoSh to a telnet session, except routed over HTTP/S which is easier to get routed than telnet these days, and with a PowerShell session instead of a DOS session on the remote computer. In any case, I want to, and I want others to as well, be making the changes under my own security context.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2013 at 10:03 pm
opc.three (4/4/2013)
Eric M Russell (4/4/2013)
Case in point: Just this morning I was asked to rename a database (AbcCorp -> AbcCorpOld) in the DEV and QA environments and then create a new database with the same name (AbcCorp) and empty tables. To keep the physical file names consistent with the logical database name, I also had to detach, re-name files, and then re-attach the old database. However, to rename the files, I had to use xp_cmdshell, because I don't have a local login on the host operating server.Thanks for posting Eric. I'll preface this by saying that I am not meaning to picking on you personally so please do not take it that way. I am picking on the generic scenario which I have a suspicion is probably very common and it goes straight to my point. If someone is tasked with doing something in the host OS'es file system then why do they not have direct access to make those changes in the context of their own account? The person not having access with their own account means someone who controls access in the environment either has not thought to grant those permissions, the DBA has not bothered to ask for those permissions to be granted either for fear of being shot down or because they already found an alternate way to do it without asking, or in the worst case they should not have the permissions at all and yet they gain access via xp_cmdshell. In any case there is a broken process somewhere or someone has violated an access rule, or both.
I have to do these types of tasks as well but I'll either access the server file system over the network via UNC, or I'll RDP to the server to make the file system changes locally. If and when I get around to it I am thinking of exploring Remote PowerShell which would effectively give me a PoSh prompt on my own machine that would actually be running commands on the remote server. Don Jones and Jeffrey Hicks compare Remote PoSh to a telnet session, except routed over HTTP/S which is easier to get routed than telnet these days, and with a PowerShell session instead of a DOS session on the remote computer. In any case, I want to, and I want others to as well, be making the changes under my own security context.
My question would be, why would anyone worry about a DBA doing his/her job? 😉 Being a DBA requires you to do things like this. Some companies grant special permissions to use the methods you used. Other companies grant permissions by saying that you're a DBA and do what you need to do through SQL Server. If you wanted to restrict such actions, it's a relatively simple thing to restrict what the SQL Server and SQL Agent logins can actually "see". If you want to limit even further, you can use PBM against the logins to restrict their actions, right?
The other thing about Eric's task is that he didn't need to use xp_CmdShell to do this. He could have done it through a self-deleting job or through the OPENROWSET hack just as easily even if xp_CmdShell was turned off and never turned on. Why? Because he's a DBA with "SA" privs. It's no different than you being granted privs to do the same thing. The fact that either of you changed some file names isn't going to be logged.
And that brings us back full circle to what I've been talking about. Having xp_CmdShell turned off provides no extra security. If you want good security, just turning off xp_CmdShell isn't going to do it for you.
--Jeff Moden
April 5, 2013 at 7:28 am
opc.three (4/4/2013)
Eric M Russell (4/4/2013)
Case in point: Just this morning I was asked to rename a database (AbcCorp -> AbcCorpOld) in the DEV and QA environments and then create a new database with the same name (AbcCorp) and empty tables. To keep the physical file names consistent with the logical database name, I also had to detach, re-name files, and then re-attach the old database. However, to rename the files, I had to use xp_cmdshell, because I don't have a local login on the host operating server.Thanks for posting Eric. I'll preface this by saying that I am not meaning to picking on you personally so please do not take it that way. I am picking on the generic scenario which I have a suspicion is probably very common and it goes straight to my point. If someone is tasked with doing something in the host OS'es file system then why do they not have direct access to make those changes in the context of their own account?
...
If I were the sysadmin on a production server, then I'd insist on having a login on the host system server. Really this type of one-off maintenance operations at the file system level occur more frequently in the development environment. In this situation they needed the ability to unit test two different releases of the ETL in parallel, which is hopefully not something a production sysadmin would ever have to screw with.
On this particular instance, which they are hosting on a virtual server they provisioned a few weeks back, I happened not to have a local host login. I could have put in a request to have a host login added, but that would have added another day to turn around time. I probably will request a local login now. However, even with that host login, I can still see the benefit of just leveraging xp_cmdshell in a T-SQL script, so I can more easily perform the same set of operations again when needed. It could be done using PowerShell, but I perfer T-SQL, and there is only that one step that involves the file system.
As a development DBA, there are just aspects of my job and routine tasks that would not and should not be performed by a production DBA.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 5, 2013 at 8:52 am
As long as one is fully aware of the associated security risks and auditability shortcomings, preference is a fair and honest reason to use xp_cmdshell.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 5, 2013 at 1:09 pm
Jeff Moden (4/4/2013)
My question would be, why would anyone worry about a DBA doing his/her job? 😉 Being a DBA requires you to do things like this. Some companies grant special permissions to use the methods you used. Other companies grant permissions by saying that you're a DBA and do what you need to do through SQL Server. If you wanted to restrict such actions, it's a relatively simple thing to restrict what the SQL Server and SQL Agent logins can actually "see". If you want to limit even further, you can use PBM against the logins to restrict their actions, right?
No one has or is saying that a DBA should be prevented from doing their job and that has nothing to with xp_cmdshell. If you choose to make the use of xp_cmdshell an issue, then it is an issue for you, but leaving xp_cmdshell disabled does not have to impact one's ability to do their job one way or the other. It may change how they have to do it, but it won't change the fact that they can do their job. The argument over how seems to be where this conversation is headed. If you know xp_cmdshell front-to-back and accept the security risks and audit shortcomings it brings with it, good for you, use it. However, freely recommending it to others is not a responsible thing to do in my opinion.
The other thing about Eric's task is that he didn't need to use xp_CmdShell to do this. He could have done it through a self-deleting job or through the OPENROWSET hack just as easily even if xp_CmdShell was turned off and never turned on. Why? Because he's a DBA with "SA" privs. It's no different than you being granted privs to do the same thing. The fact that either of you changed some file names isn't going to be logged.
1. The OPENROWSET hack you're talking about, the one that uses the VBA Shell function, looks to have been closed on x64 systems based on what I am seeing online and from some basic screwing around I did about a year ago trying to get it to work. The original hack was done on SQL 2000 and NT 4.0 and only works with the Jet Driver, which is only available on x86.
2. Not all shops have 'Ad Hoc Distributed Queries' enabled and the feature can be locked down using PBM.
3. Some shops have the SQL Agent service disabled in their environment.
And that brings us back full circle to what I've been talking about. Having xp_CmdShell turned off provides no extra security. If you want good security, just turning off xp_CmdShell isn't going to do it for you.
The recommendation is to leave xp_cmdshell disabled. There is nothing xp_cmdshell offers beyond personal preference and familiarity that cannot be done in a more secure, auditable way, in the same or less time.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2013 at 5:27 pm
opc.three (4/5/2013)
The recommendation is to leave xp_cmdshell disabled. There is nothing xp_cmdshell offers beyond personal preference and familiarity that cannot be done in a more secure, auditable way, in the same or less time.
While I believe that auditing is a good thing, it doesn't actually prevent anything especially when someone undesireable gets in with administrative privs. Only proper security can help in that area.
I'll double check the OPENROWSET hack on a 64 bit 2008 system and get back to you. But you don't actually need to go to such extremes. If someone get's in as SA, it's not going to matter if you have xp_CmdShell turned off or not. 😉
Some shops have the SQL Agent service disabled in their environment.
What were they using to schedule their jobs, then?
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?
--Jeff Moden
April 7, 2013 at 10:48 am
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 7, 2013 at 12:46 pm
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.
--Jeff Moden
April 8, 2013 at 6:40 am
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.
Viewing 15 posts - 46 through 60 (of 76 total)
You must be logged in to reply to this topic. Login to reply