xp_cmdshell - Naming, Shaming and Taming

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

[sql]
CREATE EVENT SESSION ...
[/sql]

syntax. So, how do we do this?.. Here's how:-

First, we create a Server Audit

[sql]
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
[/sql]

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'.

ok, cool.

Now we need to create a Server Audit Specification

[sql]
CREATE SERVER AUDIT SPECIFICATION [Audit-xp_cmdshell]
FOR SERVER AUDIT [xp_cmdshell]
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (AUDIT_CHANGE_GROUP);
GO
[/sql]

The final two steps are to simply enable the specification and the audit:

[sql]
ALTER SERVER AUDIT SPECIFICATION [Audit-xp_cmdshell]
WITH (STATE = ON)
GO
[/sql]

and

[sql]
ALTER SERVER AUDIT [xp_cmdshell]
WITH (STATE = ON)
GO
[/sql]

Now, if any programs try to use 'xp_cmdshell', the audit will track it. So, for my example, I did:

[sql]
sp_configure 'xp_cmdshell',1
reconfigure
GO
EXEC xp_cmdshell 'dir *.exe'
GO
[/sql]

and in my server audit, I get:

and details of:

Nice!... Now to clean up:

[sql]
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
[/sql]

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:

[sql]
sp_configure 'xp_cmdshell', 0
reconfigure
go
[/sql]

But what stops a program / job with sufficient access from simply turning it back on again? How about a Server level trigger:

[sql]
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)[1]', '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;
[/sql]

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.