opc.three (3/26/2013)
Jeff Moden (3/26/2013)
opc.three (3/25/2013)
Jeff Moden (3/25/2013)
opc.three (3/24/2013)
You're still hung up on 'external attackers.' The point is, xp_cmdshell is a blunt tool that cannot be audited and allows people to run commands as someone else, possibly with more permissions than their own, without the possibility of being detected or tracked. That is not something to be taken lightly and is certainly something most people making decisions about the security of their environment and data would object too if it was fully explained.You need to read the question I posed again. I said nothing about 'external attackers'. In fact, I specifically stated that "None of those 'individuals' are actually externally outside SQL server". Here's my post, again.
Fine. Support your words as I have supported mine. If only few (let's say, 2 DBAs) very trusted individuals have "SA" privs and none of those "individuals" are actually externally outside SQL Server) facing apps (an important point that you've left out that I've emphasized time and again), what kind of problems is having xp_CmdShell turned on going to cause and what kind of problems will be avoided by having it turned off?
So tell us all, "what kind of problems is having xp_CmdShell turned on going to cause and what kind of problems will be avoided by having it turned off"? If the answer is only "logging", please drive through because an "SA" can do just about anything without it being logged and where it is logged, (s)he can actually delete.
Maybe so, but all of that leaves an audit trail, and holes in the audit trail are an audit trail of their own, and can be grounds for termination. I do not need to make my point any clearer. Like I said to Sergiy, if you want to be in denial about the risks and exposure that leaving xp_cmdshell enabled creates that's your prerogative. But peddling it on these forums as if it is "as safe as a SELECT statement" is simply irresponsible, and I won't let it stand if I run into it.
Ok. I'll be more specific and simplify my question. You are the DBA for a company. Being a good DBA, you've given no one and no thing "SA" privs except yourself and maintenance tasks in the form of jobs running on SQL Server. You've ensured that the "SA" login password is very long and you don't use it in your daily duties. You only login as yourself.
xp_CmdShell is turned on because you have stored procedures that have been enabled to use it for your maintenance tasks.
Whether it be an internal or external user, name all of the users that can use xp_CmdShell.
Now explain how having xp_CmdShell turned on causes a security hole.
It doesn't.
It does if having access to the cmd prompt on the server as the SQL Server service account affords them access to anything they would not normally have access too. Are you sure that is not the case?
The funny thing your example above is that it is not even close to an honest representation of how we got here, on this thread or on the others where you tout its use and how safe it is. If it were just DBAs doing admin work with it I doubt there would ever be a dust-up. The people I am mostly trying to steer away from using xp_cmdshell are developers. This is where the floodgates open in terms of file share access and poor design patterns that effectively pain people into corners and cost tons of money to refactor later, the definition of an anti-pattern.
Now we're getting somewhere, ol' friend.
First, the scenario I gave wasn't meant to be an real life scenario for most. In my case, however, it is. Only DBAs have "SA" privs at work. But that means nothing because I absolutely agree that it's not real life for most. Your answer of...
If it were just DBAs doing admin work with it I doubt there would ever be a dust-up.
... depicts a more real life scenario. So does your other observation...
It does if having access to the cmd prompt on the server as the SQL Server service account affords them access to anything they would not normally have access too. Are you sure that is not the case?
Once again, you're absolutely correct.
But, we're also talking about proper security again, which is what I've been harping on since the git. The SQL Server service login and the SQL Agent login should be setup to only have privs to those things that people should be allowed to see becausre anyone with "SA" privs can setup a self deleting job that will execute an "Operating System (CmdExec)" job [font="Arial Black"]even with xp_CmdShell turned off [/font]and it won't show up in either the Job log or the default trace. Having xp_CmdShell turned off did nothing to protect against a malicious user that has "SA" privs.
I'll certainly agree that having xp_CmdShell turned off will catch the unwary user that has "SA" privs if they turn it on but, chances are really good that if a user knows that xp_CmdShell is turned off, they also know that turning it on will be logged with their login and machine in the default trace (the SQL Server log only logs the event and is basically useless for determining who caused the event). The method in the previous paragraph is only 1 of a dozen simple workarounds. Having it turned off provides no deterent to such maliciousness and, in the previous example, a command line execution isn't even logged.
Turning xp_CmdShell off did nothing to prevent a user with "SA" privs from easily using a command line level command in a totally undetected fashion. The user could be a DBA or any user with "SA". (The same problem exists with SSIS).
Shifting gears to your next statement of...
The people I am mostly trying to steer away from using xp_cmdshell are developers. This is where the floodgates open in terms of file share access and poor design patterns that effectively pain people into corners and cost tons of money to refactor later, the definition of an anti-pattern.
You use file shares all the time in things like SSIS. There's no difference in having SQL Server have the same kind of access. Notice that I did NOT say letting the Developers have the file access. You've previously witnessed first hand that a stored procedure that uses xp_CmdShell can be used by people with only PUBLIC privs but they can't use xp_CmdShell themselves. How is having a well formed stored procedure executing against a file share a "poor design pattern" or and "anti-pattern"? Some of the best ETL systems I've ever seen have been written in T-SQL using xp_CmdShell to get to things like Robo-Copy and PowerShell.
But my personal success with the command line, whether through a Job or directly through xp_CmdShell has nothing to do with me saying to just turn it on and leave it on. If you want, turn it off and leave it off. Either way, anyone with "SA" privs will be able to get to the command line and they can get there in a totally undectable fashion. Having it turned off does nothing for security and having it turned on does NOT create a security hole. People and apps having "SA" privs is the ONLY security hole because only people or apps with "SA" privs can use xp_CmdShell or do one of the totally undetectable work arounds to using xp_CmdShell.
THAT's my only point. Most people follow the mantra that xp_CmdShell should be turned of and there are MS and other authors of huge numbers of white papers that agree with that mantra and a whole lot of them are missing the point. The point is...
... having xp_CmdShell turned on does not create a security hole that's already there. People and apps with "SA" privs are the only security hole. Anyone with those privs can get to the command line in a totally undectable manner even if xp_CmdShell is turned off.
Start warning people about THAT instead of wasting your breath about turning off xp_CmdShell. It does NOTHING for security.
--Jeff Moden
Change is inevitable... Change for the better is not.