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