SQL Server Detective Control Cheat Sheet

Below is a cheat sheet that can be used for configuring SQL Server audit policies to log and detect potentially malicious behavior. It also includes some TSQL cheats for viewing the current audit policies so they can be avoided during red team and penetration test engagements.

SQL Server Detective Control Primary
Configuring SQL Server Audit Policies and Specifications
Detecting High Risk Behavior via Basic Event Log Analysis
Listing Audit Policies and Specifications
Bypassing Audit Policies

SQL Server Detective Control Primary

SQL Server supports audit policies that can monitor server level configuration changes and query execution. All of which can be logged directly to the Windows Application log. Below are some links that provide a little more background. Feel free to read ahead if you just want TSQL queries. :)

• SQL Server Audit Policy
• SQL Server Audit Policy - Server Audit Specifications (DDL)
• SQL Server Audit Policy - Database Audit Specifications (DML)

Configuring SQL Server Audit Policies and Specifications

This TSQL script can be used to configure SQL Server to log events commonly associated with operating system command execution to the Windows Application log. It requires sysadmin privileges to run successfully.

Create an Audit Policy

This will create an audit policy that the server and database specifications can be linked to.

-- Create and Enable Audit Policies
USE master 
CREATE SERVER AUDIT TestAudit
TO APPLICATION_LOG 
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE) 
ALTER SERVER AUDIT TestAudit
WITH (STATE = ON)

Audit For Server Configuration Changes

This TSQL sample will enable auditing of server level configuration changes and any changes to the audit configuration itself.

-- Server: Audit server configuration changes
-- Windows Log: Application
-- Events: 15457 
CREATE SERVER AUDIT SPECIFICATION [Audit_Server_Configuration_Changes]
FOR SERVER AUDIT TestAudit
ADD (AUDIT_CHANGE_GROUP), 	-- Audit for Audit changes
ADD (SERVER_OPERATION_GROUP)  	-- Audit for server changes
WITH (STATE = ON)

Audit for Common Agent Job Activity

This TSQL sample will enable auditing of agent job activity. More server-level auditing groups can be found HERE.

-- DATABASE: Audit common agent job activity
-- Windows Log: Application
-- Events: 33205 
Use msdb
CREATE DATABASE AUDIT SPECIFICATION [Audit_Agent_Jobs]
FOR SERVER AUDIT [TestAudit]
ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo])
WITH (STATE = ON)

Audit for the Execution of Potentially Dangerous Procedures

More database-level auditing groups can be found HERE.

-- DATABASE: Audit potentially dangerous procedures
-- Windows Log: Application
-- Events: 33205 
use master
CREATE DATABASE AUDIT SPECIFICATION [Audit_OSCMDEXEC]
FOR SERVER AUDIT [TestAudit]
ADD (EXECUTE ON OBJECT::[dbo].[xp_cmdshell] BY [dbo]),			-- Audit xp_cmdshell execution
ADD (EXECUTE ON OBJECT::[dbo].[sp_addextendedproc] BY [dbo]),		-- Audit for new custom extended stored procedures
ADD (EXECUTE ON OBJECT::[dbo].[sp_execute_external_script] BY [dbo]), 	-- Audit for external scripts like R and Python
ADD (EXECUTE ON OBJECT::[dbo].[Sp_oacreate] BY [dbo])			-- Audit OLE Automation Procedure execution
WITH (STATE = ON)

Detecting High Risk Behavior via Basic Event Log Analysis

The event IDs listed below can be filtered for specific keywords to help identify potential malicious behavior in SQL Server. White noise will vary depending on how the SQL Server is typically used, but this should at least offer a starting point.

Windows Application Log
Event ID: 15457
Description: This event is associated with server configuration changes.
Filter for events with the following keywords/strings:

• Configuration option 'external scripts enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
• Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
• Configuration option 'clr enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
• Configuration option 'clr strict security' changed from 0 to 1. Run the RECONFIGURE statement to install.
• Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
• Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

Windows Application Log
Event ID: 33205
Description: This event applies to the SQL Server Agent and database level changes.
Filter for events with the following keywords/strings:

• msdb.dbo.sp_add_job Watch for potentially malicious ActiveX, cmdexec, and powershell jobs.
• "sp_execute_external_script" Watch for cmd.exe and similar calls.
• "sp_OACreate" Watch for Sp_oacreate 'wscript.shell’ and similar calls
• "sp_addextendedproc" Watch for any usage.
• "sp_add_trusted_assembly" Watch for unauthorized usage.

Listing Audit Policies and Specifications

Listing Audits

Below is some sample TSQL that can list the existing audits.

-- View audits
SELECT * FROM sys.dm_server_audit_status

Listing Server Audit Specifications (DDL)

Below is some sample TSQL that can list the existing server audit specifications.

-- View server specifications
SELECT audit_id, 
a.name as audit_name, 
s.name as server_specification_name, 
d.audit_action_name, 
s.is_state_enabled, 
d.is_group, 
d.audit_action_id, 
s.create_date, 
s.modify_date 
FROM sys.server_audits AS a 
JOIN sys.server_audit_specifications AS s 
ON a.audit_guid = s.audit_guid 
JOIN sys.server_audit_specification_details AS d 
ON s.server_specification_id = d.server_specification_id 

Listing Database Audit Specifications (DML)

Below is some sample TSQL that can list the existing database audit specifications.

-- View database specifications
SELECT a.audit_id, 
a.name as audit_name, 
s.name as database_specification_name, 
d.audit_action_name, 
d.major_id,
OBJECT_NAME(d.major_id) as object,
s.is_state_enabled, 
d.is_group, s.create_date, 
s.modify_date, 
d.audited_result 
FROM sys.server_audits AS a 
JOIN sys.database_audit_specifications AS s 
ON a.audit_guid = s.audit_guid 
JOIN sys.database_audit_specification_details AS d 
ON s.database_specification_id = d.database_specification_id 

Bypassing Audit Policies

There are primarily three approaches for bypassing SQL Server audit controls:

• Remove them. This requires sysadmin privileges, can trigger additional alerts, and is not recommended.
• Disable them. This requires sysadmin privileges, can trigger additional alerts, and is not recommended.
• Avoid them. Simply choose to accomplish your objectives using functionality in SQL Server that isn't being monitored.

Remove Example

-- Remove Audit_Server_Configuration_Changes server specification
use master
ALTER SERVER AUDIT SPECIFICATION [Audit_Server_Configuration_Changes]
WITH (STATE = OFF)
DROP SERVER AUDIT SPECIFICATION [Audit_Server_Configuration_Changes]

-- Remove Audit_OSCMDEXEC database specification
USE master
ALTER DATABASE AUDIT SPECIFICATION [Audit_OSCMDEXEC]
WITH (STATE = OFF)
DROP DATABASE AUDIT SPECIFICATION [Audit_OSCMDEXEC]

-- Remove Audit_Agent_Jobs database specification
USE msdb
ALTER DATABASE AUDIT SPECIFICATION [Audit_Agent_Jobs]
WITH (STATE = OFF)
DROP DATABASE AUDIT SPECIFICATION [Audit_Agent_Jobs]

-- Remove TestAudit audit policy
ALTER SERVER AUDIT TestAudit
WITH (STATE = OFF)
DROP SERVER AUDIT TestAudit

Disable Example

-- Disable Audit_Server_Configuration_Changes server specification
use master
ALTER SERVER AUDIT SPECIFICATION [Audit_Server_Configuration_Changes]
WITH (STATE = OFF)

-- Disable Audit_OSCMDEXEC database specification
USE master
ALTER DATABASE AUDIT SPECIFICATION [Audit_OSCMDEXEC]
WITH (STATE = OFF)

-- Disable Audit_Agent_Jobs database specification
USE msdb
ALTER DATABASE AUDIT SPECIFICATION [Audit_Agent_Jobs]
WITH (STATE = OFF)

-- Disable TestAudit audit policy
ALTER SERVER AUDIT TestAudit
WITH (STATE = OFF)