I think most people dealing with SQL Server understand that xp_cmdshell can be a bit of a security hole if not used correctly. But... how do you know it is being used appropriately? Wouldn't it be a nice feature to be able to see exactly who is using xp_cmdshell, when and why?
You can with a SQL Server Audit. SQL Server Audits are built on top of the Extended Events engine, but because they are security related, they cannot be access directly with a
CREATE EVENT SESSION ...
syntax. So, how do we do this?.. Here's how:-
First, we create a Server Audit
CREATE SERVER AUDIT [xp_cmdshell] TO FILE (FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log') WHERE object_name = 'xp_cmdshell' GO
Hopefully this is pretty self-explanatory... the Audit log file resides under 'FILEPATH' and the object we are interested in monitoring is
object_name = 'xp_cmdshell'.
Now we need to create a Server Audit Specification
CREATE SERVER AUDIT SPECIFICATION [Audit-xp_cmdshell] FOR SERVER AUDIT [xp_cmdshell] ADD (SCHEMA_OBJECT_ACCESS_GROUP), ADD (AUDIT_CHANGE_GROUP); GO
The final two steps are to simply enable the specification and the audit:
ALTER SERVER AUDIT SPECIFICATION [Audit-xp_cmdshell] WITH (STATE = ON) GO
ALTER SERVER AUDIT [xp_cmdshell] WITH (STATE = ON) GO
Now, if any programs try to use 'xp_cmdshell', the audit will track it. So, for my example, I did:
sp_configure 'xp_cmdshell',1 reconfigure GO EXEC xp_cmdshell 'dir *.exe' GO
and in my server audit, I get:
and details of:
Nice!... Now to clean up:
ALTER SERVER AUDIT [xp_cmdshell] WITH (STATE = OFF) GO ALTER SERVER AUDIT SPECIFICATION [Audit-xp_cmdshell] WITH (STATE = OFF) GO DROP SERVER AUDIT SPECIFICATION [Audit-xp_cmdshell] GO DROP SERVER AUDIT [xp_cmdshell] GO
So, with this approach, we can name and shame the programs using xp_cmdshell thus ensuring we KNOW exactly who, what and when they are doing their work.
How about stopping them from using xp_cmdshell at all? How do we go about that one? Obviously we can disable xp_cmdshell with an sp_configure:
sp_configure 'xp_cmdshell', 0 reconfigure go
But what stops a program / job with sufficient access from simply turning it back on again? How about a Server level trigger:
CREATE TRIGGER Audit_XP_CMDSHELL ON ALL SERVER FOR ALTER_INSTANCE AS BEGIN DECLARE @SQL NVARCHAR(4000); SET @SQL = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)', 'nvarchar(4000)')); IF (CHARINDEX('sp_configure', @SQL) > 0) AND (CHARINDEX('xp_cmdshell', @SQL) > 0) BEGIN RAISERROR('Attempt to enable xp_cmdshell detected. This operation is denied!', 16, 1) WITH LOG; ROLLBACK; END; END;
Now, the Server level trigger will fire if someone does an ALTER_INSTANCE type command, collect the EVENTDATA() for the event and look inside it to see if an 'sp_configure' command with an 'xp_cmdshell' has been issued. If we match, we rollback the operation and send a copy of the EVENTDATA to the windows event log.
So - now we have tools to put a measure of control around the use of 'xp_cmdshell'. Obviously this is not foolproof - someone with sufficient access rights could disable the Server trigger, use xp_cmdshell, and re-enable the trigger. But hopefully this gives a good idea of what can be accomplished.