Once again you're focusing in too narrow a field and only considering SQL Server auditing, and only a piece of that to boot. What about the folks that kick off an SSIS package, or a bcp command for those that abhore SSIS, using xp_cmdshell and that command connects to another server? How do we know who ran it? Consider if data were being loaded to a table with an audit column that defaults to use one of the system functions to pickup the name of the login running the process, e.g. ORIGINAL_LOGIN. We won't see any distinctions because everything appears as if the SQL Server service or proxy account did it. In a malicious scenario someone could use xp_cmdshell to cover their tracks by updating data while knowing a trigger will mark the row as having been updated by the service account's Login. Also consider that there are auditing tools outside SQL Server that can track cmd shell activity. If everything appears as if it is occurring under one service account then where is the auditability?

I am the first to admit that enabling and using SQLCLR is also a security risk, and pretty much most any feature of SQL Server to be honest. I would like for Microsoft to take the same approach with SQLCLR (and OLE Automation, and some other things too) as I mentioned with xp_cmdshell, no, not deprecate and eventually remove it from the product, but make adding it to the product an explicit installation requirement. In the case of SQLCLR I would take it a step further and force the installation of the various permission sets as well so we could choose only to install the option to promote assemblies marked SAFE, EXTERNAL_ACCESS or UNSAFE, or any combination. Now, if you must access the file system use SQLCLR for goodness sake. At least then you can have the credentials of the user making the call from T-SQL (must be a Login based on a Windows Account) pass through to the OS and eventually other external resources as well. This requires a bit more setup but then everyone is operating with their own credentials and can only access what they should be able to access, i.e. no chance of permissions elevation or obfuscation or credentials that would compromise auditing.

Now let's consider the case where a SQL session initiates the use of xp_cmdshell and that process becomes hung up in the OS. Here is a good way to test it:

1. On a test instance where xp_cmdshell is enabled. you are in the sysadmin Role and you also have Desktop access to the host OS so you can see the Windows Task Manager, open a T-SQL Query Window and execute this:

EXEC sys.xp_cmdshell 'ping 127.0.0.1 -t';

2. Now hop over to Task Manager for that machine and notice that there is now a process in Task Manager for cmd.exe running as the SQL Server service account:

3. Now go back to your Query Window and try to kill the session. On my SQL 2008 R2 instance this results in 'Cancelling Query...' in SSMS, and nothing happening...not good

4. So let's open a new Query Window and try killing the session. We are a sysadmin so that should be no problem:

KILL 53

Excellent...but when we check the session to make sure it is gone you'll see nothing was killed at all.

DBCC INPUTBUFFER(53)

Still shows we are running xp_cmdshell. Go back to your original Query Window and double check...nope, still going. Now what? Well, we will need to log into the OS and kill the cmd.exe process to get out of this one.

5. Remote Desktop to the machine if it's not your machine your local testing on, open Task Manager and let's look at the list of processes. There it is...amongst many others...so which one should we kill?

See any problems with this?

It happens, quite often at one shop where I did some work, and is yet another reason xp_cmdshell should be avoided. In the case of such a condition the typical response is to kill the SQL Server session that made the call to xp_cmdshell. Sometimes this works but other times this results in nothing happening as shown above. If the session does happen to end sometimes there is then an orphaned OS process which will be executing potentially indefinitely using CPU, I/O and occupying memory. In order to remedy that situation you would need restart the server or log into the OS to find and kill the orphaned OS process. However as we showed above that is not always the simplest or most straightforward affair. In one shop where xp_cmdshell was used extensively there could literally be dozens of cmd.exe processes running that were spawned by the SQL Server service account.

To be fair you could substitute anything in place of xp_cmdshell in your sentence and it would ring true, so it's just empty rhetoric. The real issue is: how well does xp_cmdshell facilitate the enacting and maintaining of good security? ...not very well. I would argue that in most cases, even when it is implemented well, the bar is so low in terms of how sysadmins and folks that know how to make themselves sysadmins can take advantage of xp_cmdshell that it is in fact a security risk.