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 ««12

SQL ADVANCE COPY/MOVE AND RENAME FUNCTIONS Expand / Collapse
Author
Message
Posted Sunday, January 13, 2013 12:38 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:58 PM
Points: 36,794, Visits: 31,253
I think having the ability to audit is definitely the definitive reason to use CLR over xp_CmdShell. I think it's great that the user the hacker broke in as will be audited and be the one to get all the blame.

Auditing an attack is a bit like discovering the brand of matches that burned down the barn. Using CLR instead of xp_CmdShell will not prevent any attack. Even taking the extreme measure of deleting the xp_Star DLL (deletes xp_CmdShell) won't prevent, lessen the ferocity of, or reduce the damage done in any attack. Any reasonably skilled attacker that can get in as "SA" in SQL Server can get to a command prompt even with xp_CmdShell deleted.

Thinking that using CLR instead of xp_CmdShell will somehow protect you from attack is a false sense of security that will cost you dearly. Attackers just won't use your bloody CLRs.


--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 #1406501
Posted Sunday, January 13, 2013 1:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 7,094, Visits: 12,581
Jeff Moden (1/13/2013)
I think having the ability to audit is definitely the definitive reason to use CLR over xp_CmdShell. I think it's great that the user the hacker broke in as will be audited and be the one to get all the blame.

Auditing an attack is a bit like discovering the brand of matches that burned down the barn. Using CLR instead of xp_CmdShell will not prevent any attack. Even taking the extreme measure of deleting the xp_Star DLL (deletes xp_CmdShell) won't prevent, lessen the ferocity of, or reduce the damage done in any attack. Any reasonably skilled attacker that can get in as "SA" in SQL Server can get to a command prompt even with xp_CmdShell deleted.

Thinking that using CLR instead of xp_CmdShell will somehow protect you from attack is a false sense of security that will cost you dearly. Attackers just won't use your bloody CLRs.

I never mentioned that a comparable file system manipulation routine using the SQLCLR was more secure than one using xp_cmdshell. You mentioned security. It's two paths to the same destination in terms of manipulating the file system itself. I lead with saying that I would not do this type of work in T-SQL at all and I stand by that 100%. However, some folks either want to or are being forced to do things in T-SQL against their will so as a more robust, managed code alternative, I merely suggested that there are options available when using the SQLCLR that simply are not available when using xp_cmdshell making it a better overall option. Microsoft seems to subscribe to the same thought process as well given their deprecation announcement regarding User-defined Extended Stored Procedures as of SQL Server 2005. It may just be a matter of time before they deprecate xp_cmdshell and rollout a SQLCLR object that exposes access to a PowerShell prompt to take its place, which I wouldn't bother using either by the way.

The ancillary benefit for enabling and using the SQLCLR is that xp_cmdshell remains disabled on the system, and in lieu of moving file system manipulation activities out of the SQL Server altogether, and thereby successfully resisting the urge to extend SQL Server as an application server by exposing a conduit to an operating system shell, that is a trade I would take 100% of the time.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1406507
Posted Sunday, January 13, 2013 1:41 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:58 PM
Points: 36,794, Visits: 31,253
That's precisely what I'm afraid of, Orlando. People saying they'd take that kind of a trade 100% of the time. I know you know better (the larger picture) but there are people that don't. They'll believe that just because they're using a CLR and that that they've got xp_CmdShell disabled, that their system is secure and that an attacker can't use xp_CmdShell or any other method to get to a command prompt with extraordinary privs intact. You and I both know better than that.

To wit, if you have a properly secured system, it isn't going to matter if you have xp_CmdShell turned on or not. In fact, you could have stored procedures that use it and your system would still be properly locked down. xp_CmdShell has nothing to do with security risks and does not enable or prevent any risk. Bad security does.


--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 #1406511
Posted Sunday, January 13, 2013 2:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 7,094, Visits: 12,581
Security and risk mitigation have to be layered and the risk-to-benefit ratio for xp_cmdshell simply makes it a non-choice for me. I put SQLCLR objects with EXTERNAL_ACCESS and UNSAFE in the same category by default and there are ways to have the SQLCLR enabled and only allow SAFE assemblies.

As I said before, my stance is that none of these types of operations should be initiated from within SQL Server. Having xp_cmdshell disabled means there is one less attack vector available. And I agree, there is little defense against a malicious sysadmin that knows how to work some magic (such as yourself and a handful of others we know that frequent this site) to re-instate the dll (if removed) and enable xp_cmdshell (if disabled) to get to a cmd shell prompt but that is no reason to ignore that the roadblock will often suffice, or at least hinder enough to where another layer of auditing or controls are tripped and the exposure can be mitigated before any real loss of assets occur.

I see the extension of your argument as: if you have sysadmin, because there are ways to circumvent the system to get to a cmd shell prompt even if xp_cmdshell is disabled or the underlying dll removed from the system then we should not bother enacting these additional roadblocks and I wholeheartedly disagree. I think it is worth steering operations that involve the file system to processes initiated outside the SQL Server, e.g. PowerShell, and if they must stay in SQL Server then use a managed code option, e.g. the SQLCLR.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1406516
Posted Sunday, January 13, 2013 7:44 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:58 PM
Points: 36,794, Visits: 31,253
Hmmm. I can see your point. I mean about not doing file handling in SQL Server and that those types of things should be done well outside of SQL Server. Of course, then you have an additional system to secure and privs to control and learn and document and audit and maintain.

And, yes, you're correct. Then extension of my argument is and always has been that if someone can get in with SA privs, then there is no way to stop the attacker before the attacker can do extremely grave damage at any level even if xp_CmdShell has been deleted. It won't even slow an attacker down long enough for anyone to realize they're being attacked before they've done what they setout to do. It'll simply be too late and your auditing will simply show that someone got in as "little Bobby" and threw a world of hurt on the server. Disabling xp_CmdShell as "another roadblock" is a total waste of time. Someone attacking as an "SA" can turn it back on in the first few miliseconds they're "in". All having it turned off does is give a very dangerous false sense of security. It's a bit like holding a dancing veil up to shield against a nuclear blast. Looks real pretty and is associated with some great moves but does no real good.

I'll also say that unless someone can get in as "SA", they won't be able to use xp_CmdShell even if it's turned on and all greased up with a proxy. I will absolutely agree that allowing any non-SA prived user to directly use xp_CmdShell through a proxy is one of the most incredibly stupid things that anyone could do. I can't actually believe that MS allowed for such a thing especially since it's just not needed.

To be absolutely clear, I also believe that only the DBAs themselves should have any privs other than PUBLIC and exec privs on a handful of stored procedures. Users/logins/apps shouldn't even need even db_datareader or db_datawriter.

Also, what is to prevent the external systems you speak of from being an attack point? Nothing, really. It's no different than doing file handling via any tool whether it be SSIS, xp_CmdShell, some third party tool, PowerShell, or the DOS prompt, itself. As a DBA, I can much more closely guard what goes on from SQL Server than I can when some 3rd party software comes into play or some "qualified" user is allowed in at the DOS Prompt to run PowerShell. I can also get my job done much more quickly by automating jobs in the environment I know best. Shoot... I even use xp_CmdShell to call PowerShell.

Just to be clear, I never give individuals or apps the privs to run xp_CmdShell directly. There's just no need. A user with only PUBLIC privs can be given the privs to execute a stored procedure that does the job correctly. The truth is, users have no business running even those. The system should take care of such things auto-magically.

And, no... I'm not saying that you shouldn't put obstacles up in front of would-be attackers. I'm just saying that turning off xp_CmdShell is no obstacle to an attacker what so ever because they're expecting it to be turned off and have already included different attack vectors in their code to easily get around it.

The bottom line is that you and I agree to disagree on this subject. That's a very good thing, actually. That means that when you and I have such a conversation (does seem to be a lot), people are really going to benefit because they get to see both sides of the story to make an intelligent decision based on their particular needs.


--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 #1406544
Posted Sunday, January 13, 2013 8:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 7,094, Visits: 12,581
Jeff Moden (1/13/2013)
Hmmm. I can see your point. I mean about not doing file handling in SQL Server and that those types of things should be done well outside of SQL Server. Of course, then you have an additional system to secure and privs to control and learn and document and audit and maintain.

Thank you for acknowledging there is some value in moving file system manipulation work outside of SQL Server. That might be the first time I have heard you say anything close to that. For the record I do not view the app server that would handle this work as an "additional system" to lock down. It's part of the same system and the security is layered and orchestrated across all parts of the system. File handling systems are quite simple to lock down and audit, at least compared to locking down a SQL Server that has xp_cmdshell enabled or file handling routines implemented in SQLCLR that are not allowing the callers identity to pass through to the file system.

And, yes, you're correct. Then extension of my argument is and always has been that if someone can get in with SA privs, then there is no way to stop the attacker before the attacker can do extremely grave damage at any level even if xp_CmdShell has been deleted. It won't even slow an attacker down long enough for anyone to realize they're being attacked before they've done what they setout to do. It'll simply be too late and your auditing will simply show that someone got in as "little Bobby" and threw a world of hurt on the server. Disabling xp_CmdShell as "another roadblock" is a total waste of time. Someone attacking as an "SA" can turn it back on in the first few miliseconds they're "in". All having it turned off does is give a very dangerous false sense of security. It's a bit like holding a dancing veil up to shield against a nuclear blast. Looks real pretty and is associated with some great moves but does no real good.

I like the analogy but all of that can be blocked at the OS level. It was monitored at one shop I was in and I received phone calls about it regularly asking if we could "stop using xp_cmdshell from SQL Server." Which unfortunately we could not because the legacy application that used it was designed as if the original architects were being paid a bonus for every call they made to it in their stored procs.

I'll also say that unless someone can get in as "SA", they won't be able to use xp_CmdShell even if it's turned on and all greased up with a proxy. I will absolutely agree that allowing any non-SA prived user to directly use xp_CmdShell through a proxy is one of the most incredibly stupid things that anyone could do. I can't actually believe that MS allowed for such a thing especially since it's just not needed.

To be absolutely clear, I also believe that only the DBAs themselves should have any privs other than PUBLIC and exec privs on a handful of stored procedures. Users/logins/apps shouldn't even need even db_datareader or db_datawriter.

On these two points we agree 100%. I would take it a step further and say that allowing one to get to the file system from within T-SQL at all was a major mistake.

I can also get my job done much more quickly by automating jobs in the environment I know best. Shoot... I even use xp_CmdShell to call PowerShell.

I see. So if someone were to prevent you from using xp_cmdshell then it would be bad for you. Is it that having xp_cmdshell makes you feel more productive than any other set of tools you have acquainted yourself with? As a litmus test, if someone offered you a job making 50% more than you make now with the opportunity to gain a very high level of job satisfaction based on the job description but the pre-condition was that "corporate" has banned xp_cmdshell, would you not entertain the offer?

Just to be clear, I never give individuals or apps the privs to run xp_CmdShell directly.

You're missing the giant risk right under your nose. A DBA is an individual and the fact that they have the title of DBA makes them no less likely to want to steal data or become disgruntled and put the company at risk.

And, no... I'm not saying that you shouldn't put obstacles up in front of would-be attackers. I'm just saying that turning off xp_CmdShell is no obstacle to an attacker what so ever because they're expecting it to be turned off and have already included different attack vectors in their code to easily get around it.

There are plenty of obstacles within and outside SQL Server including having something running in Windows that can intercept and block cmd shell requests emanating out of SQL Server.

The bottom line is that you and I agree to disagree on this subject. That's a very good thing, actually. That means that when you and I have such a conversation (does seem to be a lot), people are really going to benefit because they get to see both sides of the story to make an intelligent decision based on their particular needs.

It does seem to occur quite a lot but it's not a bad dialogue to reprise once in a while


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

Add to briefcase ««12

Permissions Expand / Collapse