Detect SQL Stored Procedures in Sumo Logic CSE
Adversaries abuse SQL stored procedures to establish persistent access to database servers. In MSSQL, the sp_addstartup or marking a procedure as a startup procedure causes it to execute automatically when SQL Server starts. Enabling xp_cmdshell allows execution of operating system commands. CLR assemblies compiled from .NET code can be registered and linked to stored procedures for arbitrary code execution. Stuxnet used xp_cmdshell for this purpose; Kaspersky documented attackers using startup procedures for persistent backdoor access.
MITRE ATT&CK
- Tactic
- Persistence
- Technique
- T1505 Server Software Component
- Sub-technique
- T1505.001 SQL Stored Procedures
- Canonical reference
- https://attack.mitre.org/techniques/T1505/001/
Sumo Detection Query
(_sourceCategory="windows/sysmon" OR _sourceCategory="windows/security")
| parse "<EventID>*</EventID>" as event_id
| parse "<Image>*</Image>" as process_image nodrop
| parse "<ParentImage>*</ParentImage>" as parent_image nodrop
| parse "<CommandLine>*</CommandLine>" as command_line nodrop
| parse "<TargetFilename>*</TargetFilename>" as target_filename nodrop
| parse "<TargetObject>*</TargetObject>" as registry_key nodrop
| parse "<Details>*</Details>" as registry_value nodrop
| parse "<User>*</User>" as user nodrop
| parse "<ComputerName>*</ComputerName>" as host nodrop
| where event_id in ("1", "11", "13")
| eval is_sql_parent = if(matches(toLowerCase(parent_image), ".*sql(servr|agent)\.exe.*"), 1, 0)
| eval is_sql_process = if(matches(toLowerCase(process_image), ".*sql(servr|agent)\.exe.*"), 1, 0)
| eval is_shell_child = if(matches(toLowerCase(process_image),
".*(cmd|powershell|wscript|cscript|mshta|certutil|bitsadmin|rundll32|net|whoami|ipconfig)\.exe.*"), 1, 0)
| eval is_sql_cmdline = if(matches(toLowerCase(command_line),
".*(xp_cmdshell|sp_addstartup|clr enabled|exec master|startup procedure|create procedure|alter procedure).*"), 1, 0)
| eval is_nondefault_path = if(!matches(target_filename, ".*(Microsoft SQL Server|MSSQL).*"), 1, 0)
| eval is_sql_registry = if(matches(toLowerCase(registry_key), ".*(sql server|mssqlserver|sqlserver).*")
and matches(toLowerCase(registry_value), ".*(xp_cmdshell|clr enabled|startup|clr).*"), 1, 0)
| eval detection_type = if(event_id == "1" and is_sql_parent == 1 and is_shell_child == 1, "SQL_Server_OS_Command_Execution",
if(event_id == "1" and is_sql_process == 1 and is_sql_cmdline == 1, "SQL_Admin_Command_Abuse",
if(event_id == "11" and is_sql_process == 1 and is_nondefault_path == 1, "SQL_Server_File_Write",
if(event_id == "13" and is_sql_registry == 1, "SQL_Server_Config_Change", null))))
| where !isNull(detection_type)
| table _messageTime, host, user, detection_type, process_image, command_line, parent_image, target_filename, registry_key, registry_value
| sort by _messageTime descSumo Logic detection for SQL Server stored procedure persistence abuse. Parses Sysmon EventIDs 1 (process create), 11 (file create), and 13 (registry set) to identify four patterns: OS command shells spawned by SQL Server, SQL admin commands abusing xp_cmdshell or sp_addstartup, SQL Server writing files outside its default directories (CLR assembly staging), and registry modifications enabling xp_cmdshell or CLR.
Data Sources
Required Tables
False Positives & Tuning
- SQL Server Reporting Services (SSRS) or Analysis Services processes spawning child processes for legitimate report rendering or data processing
- Database administrators running DBCC commands or executing maintenance stored procedures that invoke system-level diagnostics
- SQL Server CLR integration used legitimately by developers for custom aggregate functions or geospatial data processing via registered assemblies
Other platforms for T1505.001
Testing Methodology
Validate this detection against 3 adversary techniques from Atomic Red Team. Each test below lists the behaviour to exercise and the telemetry you should expect to see. Executable commands and cleanup steps are available with Pro.
- Test 1Enable and Execute xp_cmdshell (MSSQL)
Expected signal: Sysmon EventCode 1: sqlservr.exe spawning cmd.exe with /c whoami. Security Event 4688 from the SQL Server service account context. Windows Application event log: SQL Server configuration change event.
- Test 2Create SQL Server Startup Stored Procedure
Expected signal: Sysmon EventCode 1 (sqlcmd.exe execution). SQL Server error log entry for stored procedure creation. On next SQL Server restart, the startup procedure executes (logged in SQL Server error log).
- Test 3Query for Existing Startup Procedures and xp_cmdshell Status
Expected signal: Sysmon EventCode 1: sqlcmd.exe process creation. SQL Server error log: login event for executing user.
References (5)
- https://attack.mitre.org/techniques/T1505/001/
- https://www.netspi.com/blog/technical-blog/network-penetration-testing/sql-server-persistence-part-1-startup-stored-procedures/
- https://www.netspi.com/blog/technical-blog/adversary-simulation/attacking-sql-server-clr-assemblies/
- https://securelist.com/malicious-tasks-in-ms-sql-server/92167/
- https://github.com/redcanaryco/atomic-red-team/blob/master/atomics/T1505.001/T1505.001.md
Unlock Pro Content
Get the full detection package for T1505.001 including response playbook, investigation guide, and atomic red team tests.