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 Primer
Configure Auditing to Detect Common OS Command Execution Techniques
Configure Auditing to Detect Potential SQL Server Link Abuse
Detecting High Risk Behavior
Listing SQL Server Audit Policies and Specifications
Bypassing SQL Server Audit Policies

SQL Server Detective Control Primer

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)

Configure Auditing to Detect Common OS Command Execution Techniques

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)

Create Server Specification to 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)

Create Database Specification to 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)

Create Database Specification to Audit for the Execution of Potentially Dangerous Procedures

More database-level auditing groups can be found HERE.

-- DATABASE: Audit potentially dangerous procedures used for OS command execution
-- Windows Log: Application
-- Events: 33205 
USE master
GO
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)
GO

-- DATABASE: Audit potential credential and proxy account abuse
-- Windows Log: Application
-- Events: 33205 
USE msdb
GO
CREATE DATABASE AUDIT SPECIFICATION [ProxyAccountAuditSpec] 
FOR SERVER AUDIT [TestAudit] 
ADD (EXECUTE ON OBJECT::[dbo].[sp_add_proxy] BY [dbo]), 
ADD (EXECUTE ON OBJECT::[dbo].[sp_grant_proxy_to_subsystem] BY [dbo]) 
WITH (STATE = ON); 
GO

View Audit Policies to Verify Configurations

-- View audits
SELECT * FROM sys.dm_server_audit_status

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

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

Configure Auditing to Detect Potential SQL Server Link Abuse

This TSQL script can be used to configure SQL Server to log events commonly associated with linked server abuse to the Windows Application log. It requires sysadmin privileges to run successfully. At a minimum, linked servers are commonly used by attackers for data access, lateral movement, privilege escalation, file uploads, file downloads, and Active Directory recon.

**Create and Enable Audit Policies ** Define an audit and define where the output should go.

USE master 
CREATE SERVER AUDIT AuditMyLinkedServers
TO APPLICATION_LOG 
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE) 
ALTER SERVER AUDIT AuditMyLinkedServers
WITH (STATE = ON)

Create Server Audit Specification to Watch for Link Creation Monitor for modifications to the server configuration and 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 [AuditMyLinkedServers]
ADD (AUDIT_CHANGE_GROUP), 								-- Audit Audit changes
ADD (SERVER_OPERATION_GROUP),  								-- Audit server changes
ADD (FAILED_LOGIN_GROUP)                                                                -- Audit failed logins 
WITH (STATE = ON)

Create Database Audit Specification to Monitor for Linked Server Events These actions encompass data manipulation languages (DML) and data definition language (DDL) operations. An example of a DML operation would be a SELECT, UPDATE, or INSERT statement. An example of a DDL operation would be a CREATE, DROP, or ALTER statement.

-- DATABASE: Audit potentially dangerous procedures
-- Windows Log: Application
-- Events: 33205 
USE master
CREATE DATABASE AUDIT SPECIFICATION [Audit_SysServers]
FOR SERVER AUDIT [AuditMyLinkedServers]
ADD (AUDIT_CHANGE_GROUP),                                                  -- Changes to AUDIT configuration
ADD (SELECT ON OBJECT::[MASTER].[dbo].[sysservers] BY [dbo]),              -- Log listing links via sysserver access
ADD (EXECUTE ON OBJECT::[MASTER].[dbo].[sp_linkedservers] BY [dbo]),       -- Log listing links via sp_linkedservers 
ADD (EXECUTE ON OBJECT::[MASTER].[dbo].[sp_addlinkedserver] BY [dbo]),     -- Add linked server
ADD (EXECUTE ON OBJECT::[MASTER].[dbo].[sp_addserver] BY [dbo]),           -- Add linked server
ADD (EXECUTE ON OBJECT::[MASTER].[dbo].[sp_dropserver] BY [dbo]),          -- Drop linked server
ADD (EXECUTE ON OBJECT::[MASTER].[dbo].[sp_addlinkedsrvlogin] BY [dbo]),   -- Log linked server user configuration 
ADD (EXECUTE ON OBJECT::[MASTER].[dbo].[sp_droplinkedsrvlogin] BY [dbo]),  -- Log linked server user configuration 
ADD (EXECUTE ON OBJECT::[MASTER].[dbo].[sp_serveroption] BY [dbo])         -- Configured linked server settings
WITH (STATE = ON)

View Audit Policies to Verify Configurations

-- View audits
SELECT * FROM sys.dm_server_audit_status

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

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

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 associated with command execution:

• 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 associated with command execution:

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

Windows Application Log
Event ID: 33205
Description: This event applies to the SQL Server Agent and database level changes.
Filter for events and patterns associated with Linked Server Abuse:

• Monitor for multiple queries for SQL Server links. All link crawling will require querying SQL Server for a list of SQL Server links from the sysservers table. After enabling and configuring the database audit specification above, Windows Application log event 33205 should be generated any time that table is queried. Those logs will likely include the string "sysservers" or "sp_linkedservers". While querying for linked servers is only a medium-fidelity indicator of malicious activity, it can be an early indicator of potential SQL Server link abuse. Especially, if 5 or more 33205 events are observed with the prementioned strings within a minute or less that have originated from multiple servers.
• Monitor for SQL Server link creation. When attackers are using SQL Server links for lateral movement they may also create their own links to move between SQL Server instances. The creation of links can also be used by attackers to read files, write files, and query Active Directory. After enabling and configuring the database audit specification above, Windows Application log event 33205 should be generated any time those actions occurs. Those logs will likely include the string "sp_addlinkedserver", "sp_addserver", "sp_addlinkedsrvlogin", or "sp_serveroption". While adding a new linked servers is not a high-fidelity indicator of malicious activity, it can be a indicator of potential SQL Server link abuse.
• Monitor for excessive failed logins. When links have been misconfigured or are out of date, it's not uncommon to see fail log attempts when crawling links. However, this would be considered a low-fidelity indicator. Using the server audit specification example above, it is possible to log failures to Windows Application log event ID 33205. Windows Application log event ID 18456 will also be generated. Those can then be identified by filtering for the string "Login failed for". If 10 or more of those events occur within a minute from multiple sources it may be an indicator of links crawling or distributed dictionary attacks.
• Monitor for multiple instances of OpenQuery in single statement. SQL Server link crawling requires DML statements that use two or more instances of the string "OpenQuery". While it may be possible to obfuscate that pattern, it should still be considered a high-fidelity indicator, because OpenQuery statements are not nested during legitimate use. However, the SQL Server database audit specifications don't appear to support logging the use of arbitrary strings (a.k.a "OpenQuery") directly so it may require third-party software. Some alternative options may include SQL Server extended events, triggers, transactions logs, and profiler traces. It may also be possible to create an agent job to query for cached query plans on a regular interval to review for the use of OpenQuery in TSQL statements, and then manually create a audit log with a custom stored procedure.

Listing SQL Server Audit Policies and Specifications

As an attacker it's particularly useful to know what audit policies are in place so they can be removed or avoided. Below are some queries to get you started.

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 SQL Server 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)