March 26, 2013 at 8:42 pm
See, now here I thought xp_cmdshell was a virus. I learned something today:
http://sqlanddotnetdevelopment.blogspot.com/2011/06/sql-server-blocked-access-to-procedure.html?m=1
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2013 at 8:49 pm
opc.three (3/26/2013)
Sergiy (3/26/2013)
He was not referring to sp_configure at all.
Oh, really?
What was that about then?
http://www.galileowaswrong.com/galileowaswrong
You're a clown, that's funny 😛
It's sad you don't even realise how pathetic you are...
:ermm:
_____________
Code for TallyGenerator
March 26, 2013 at 9:17 pm
Here is Erland Sommarskog saying to Microsoft "The first thought at hand is to use xp_cmdshell to run a DEL command, but xp_cmdshell is best disabled."
If you have any doubts about whether Erland Sommarskog understands how xp_cmdshell works, hit up Bing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2013 at 9:20 pm
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.
March 26, 2013 at 9:26 pm
opc.three (3/26/2013)
Here is Erland Sommarskog saying to Microsoft "The first thought at hand is to use xp_cmdshell to run a DEL command, but xp_cmdshell is best disabled."If you have any doubts about whether Erland Sommarskog understands how xp_cmdshell works, hit up Bing.
I have a huge amount of respect for Erland but if he thinks that having xp_CmdShell disabled provides anything more than a highly permeable and easily lifted veil of security, then he's also wrong. The problem is real security, not xp_CmdShell. Improper use of xp_CmdShell is nothing more than a symptom of otherwise bad security.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2013 at 9:30 pm
Sergiy (3/26/2013)
opc.three (3/26/2013)
Sergiy (3/26/2013)
He was not referring to sp_configure at all.Oh, really?
What was that about then?
http://www.galileowaswrong.com/galileowaswrong
You're a clown, that's funny 😛
It's sad you don't even realise how pathetic you are...
:ermm:
This from a guy that argues about the optimizer with Paul White...get a clue troll.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2013 at 9:33 pm
Jeff, I respect your position, however vehemently I might disagree with it. You make valid points but for me, xp_cmdshell is not and never will be cast in a positive light and hope it is eventually dropped from the product like some of the other Extended Stored Procedures that have started to get picked off as the releases roll by.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2013 at 9:45 pm
opc.three (3/26/2013)
Jeff, I respect your position, however vehemently I might disagree with it. You make valid points but for me, xp_cmdshell is not and never will be cast in a positive light and hope it is eventually dropped from the product like some of the other Extended Stored Procedures that have started to get picked off as the releases roll by.
I also respect your personal position on the subject but you're still missing the point. I don't mind you casting a bad light on xp_CmdShell and recommending that people don't use it even if I disagree with that position. Tell them about the alternatives that you use. I just don't want people to think that turning xp_CmdShell off provides them with any form of protection from command line usage because it doesn't. Any user with "SA" privs can get to the command line in a totally undetectable fashion even if xp_CmdShell is turned off and bad security is the real problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2013 at 9:53 pm
opc.three (3/26/2013)
Sergiy (3/26/2013)
opc.three (3/26/2013)
Securing SQL Server by Denny Cherry:- page 153 recommends to "disable xp_cmdshell"
- page 161 recommends "removing the extended stored proc xp_cmdshell" but goes on to say that (paraphrased) "you may need to add them back before doing system upgrades and they can be re-added by a crafty attacker with the right level of permissions and knowledge of the system"
OK, another one fallen into the same misconception.
Not really surprising.
Jeff pointed out that it's a very common one.
Denny left the back door open for him to escape though.
Still not sure that knowing how to use "sp_configure" makes you some kind of crafty one.
He was not referring to sp_configure at all.
http://www.galileowaswrong.com/galileowaswrong
You're a clown, that's funny 😛
What Sergiy is saying (or at least the way I took it) is that it's actually super easy to add xp_CmdShell back into the system and use it (I just got done reading about a tool that hackers {both internal and external} can use that actually does it automatically) if you have "SA" privs. Then all you have to do is use sp_Configure to turn it on or use the self-deleting Job hack.
He's also saying that just because the "church" of SQL says it isn't so, they're not always right even if there are millions of people of the same opinion.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2013 at 10:51 pm
Jeff, you're a gentleman and a scholar, and it distresses me that much more that I cannot make my point clearly. You have seen countless of my posts. I absolutely do recommend alternatives to xp_cmdshell, namely PowerShell, SSIS, .NET, anything but xp_cmdshell. One more item to clear up, I absolutely alert folks to keep control of their list of sysadmin members, but again that's only one part if the story.
I think you are ignoring a significant point which is that security must be layered throughout an environment. Leaving xp_cmdshell enabled, and not protecting a change in its configuration with PBM and possibly even removing the xsp altogether depending on what else the system is tasked with doing, is leaving an available layer out of the mix. If you say that an extremely skilled DBA can get around those roadblocks in a short amount of time and therefore it is not worth adding them, that's apathy in my opinion and does not make for a security strategy. Consider that things don't always go the way you want them to. What if there is an AD group that is in the sysadmin Role because that's how the Enterprise does things and you do not always know or have control over the people who are in the sysadmin Role. It happens. You know what else happens, people leave passwords unprotected in notebooks, whiteboards and other places like in the comments of a website page served publicly.
It takes no time at all to add some additional roadblocks in an "instance setup" script that can have a net positive effect on the security of your data. You are a reasonable person, will you not concede that taking those steps would improve security of the data and improve auditability of the environment, however miniscule you think the improvement might be? I am saying that enabling xp_cmdshell has a negative net effect on security and taking these steps I mentioned has a positive net effect, so there really is no choice. xp_cmdshell has no place in an environment. Yes, there are other areas of weakness in an environment, but why put out a welcome mat?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2013 at 10:52 pm
mister.magoo (3/26/2013)
How do we all feel about SQL Agent Jobs and the ability to run operating system commands from them?(I know the user running the job will have been configured to have minimal permissions, but it still may have access to resources the attacker wouldn't normally have access to)
And SSIS packages that can FTP / email / perform file operations / run ad-hoc .net code - are they ok ?
Don't they also provide the opportunity for an "attacker" known or unknown to perform tasks with permissions other than their own?
Or how about someone gaining access to your workstation or the server and using SQLCMD mode in SSMS to run operating system commands? (assuming you have already locked down the dos prompt and the windows Run command and the "Run..." command on the windows task manager and the File...Open dialogs in Office)...
Oh hold on, while I was typing this, someone stole my server...damn it!
:hehe:
I'm pretty sure that having xp_CmdShell turned off isn't going to help any of those. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2013 at 1:55 am
Jeff Moden (3/26/2013)
mister.magoo (3/26/2013)
How do we all feel about SQL Agent Jobs and the ability to run operating system commands from them?(I know the user running the job will have been configured to have minimal permissions, but it still may have access to resources the attacker wouldn't normally have access to)
And SSIS packages that can FTP / email / perform file operations / run ad-hoc .net code - are they ok ?
Don't they also provide the opportunity for an "attacker" known or unknown to perform tasks with permissions other than their own?
Or how about someone gaining access to your workstation or the server and using SQLCMD mode in SSMS to run operating system commands? (assuming you have already locked down the dos prompt and the windows Run command and the "Run..." command on the windows task manager and the File...Open dialogs in Office)...
Oh hold on, while I was typing this, someone stole my server...damn it!
:hehe:
I'm pretty sure that having xp_CmdShell turned off isn't going to help any of those. 🙂
No, but to be fair (because tension is high enough in here) no-one has suggested it would, but my unasked question there is "would opc.three also disable these tools?" I doubt it.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 27, 2013 at 7:52 am
mister.magoo (3/27/2013)
Jeff Moden (3/26/2013)
mister.magoo (3/26/2013)
How do we all feel about SQL Agent Jobs and the ability to run operating system commands from them?(I know the user running the job will have been configured to have minimal permissions, but it still may have access to resources the attacker wouldn't normally have access to)
And SSIS packages that can FTP / email / perform file operations / run ad-hoc .net code - are they ok ?
Don't they also provide the opportunity for an "attacker" known or unknown to perform tasks with permissions other than their own?
Or how about someone gaining access to your workstation or the server and using SQLCMD mode in SSMS to run operating system commands? (assuming you have already locked down the dos prompt and the windows Run command and the "Run..." command on the windows task manager and the File...Open dialogs in Office)...
Oh hold on, while I was typing this, someone stole my server...damn it!
:hehe:
I'm pretty sure that having xp_CmdShell turned off isn't going to help any of those. 🙂
No, but to be fair (because tension is high enough in here) no-one has suggested it would, but my unasked question there is "would opc.three also disable these tools?" I doubt it.
Actually what I am saying is that if you disable xp_cmdshell on all your instances great riches will magically appear in your bank account, great friendship will find you, world peace will descend from the heavens all over the globe, and your instances will automatically be impenetrable from all attackers, internal and external.
xp_cmdshell is but one attack vector. The others you mentioned also need to be considered and locked down to the best of one's ability in the context of the given environment and the policies and procedures in place. They are actually no less important to do, but it is far less straightforward than simply avoiding xp_cmdshell and taking some of the additional steps I mentioned, and far less of a topic on these forums I might add when it comes to discussing how to get data from instanceA to instanceB with possibly some light transforms in between.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2013 at 1:22 pm
Or, you could do what I've done. Work with the "team" to properly lock down the system and then use all of those tools as they were intended to be used.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2013 at 2:34 pm
...removed post...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 76 through 90 (of 96 total)
You must be logged in to reply to this topic. Login to reply