SQL Server Audit Recipe – xp_cmdshell

This article assumes you already have a basic understanding of SQL Server Audit, but if not, use this link to catch up on all the details.

Are you required to have xp_cmdshell enabled on one of your servers? If so, then setup a SQL Audit now to track its use. Never mind the implications of enabling xp_cmdshell, as a DBA you are responsible for what happens on your servers and tracking the use of xp_cmdshell should be a priority.

The first step is to create a server audit to hold the events that we collect.

USE master;
GO
CREATE SERVER AUDIT Audit_xp_cmdshell
TO FILE (FILEPATH = 'E:\SQL2017\SQL_Audit')
WITH (ON_FAILURE = CONTINUE)
WHERE (object_name = 'xp_cmdshell');
GO

You’ll notice that we added a WHERE clause that instructs the audit to only collect events that reference the object xp_cmdshell. All other events will be ignored.

Next, we need to create a server audit specification using the
SCHEMA_OBJECT_ACCESS_GROUP. This server-level action group is triggered when a permission is used to access an object such as xp_cmdshell.

CREATE SERVER AUDIT SPECIFICATION AuditSpec_xp_cmdshell
FOR SERVER AUDIT Audit_xp_cmdshell
  ADD (SCHEMA_OBJECT_ACCESS_GROUP);
GO

Running the following commands will make sure both the audit and audit specification are enabled.

ALTER SERVER AUDIT Audit_xp_cmdshell WITH (STATE = ON);
GO
ALTER SERVER AUDIT SPECIFICATION AuditSpec_xp_cmdshell WITH (STATE = ON);
GO

To test our audit, we need to make sure xp_cmdshell is enabled.

EXEC sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1;
GO
RECONFIGURE;
GO

Then call xp_cmdshell to create some activity.

EXEC xp_cmdshell 'DIR E:\SQL2017\SQL_Tempdb\*.* /b';
GO

Viewing the audit log, you can clearly see the command that was executed and the login that called it.

As we have seen, if you have a server that has xp_cmdshell enabled, then using SQL Audit can help you keep track of the commands that have been executed.

Everything we have covered here will work in all editions of SQL Server 2012 and above.

Blob Auditing for Azure SQL Database

In February 2017, Microsoft announced the general availability of Blob Auditing for Azure SQL Database. While auditing features were available before in Azure, this is a huge leap forward, especially in having more granular control over what audit records are captured.

Before Blob Auditing, there was Table Auditing. This is something I like to equate to the C2 auditing feature of SQL Server. It’s only configurable options were ON or OFF. In reality, Table Auditing has a few more controls than that, but you get the idea. There was no way to audit actions against one specific table. Blob Auditing provides us with that level of granularity. However, controlling that granularity cannot be accomplished through the Azure Portal; it can only be done with PowerShell or REST API.

Continue reading here...