Detect SQL Stored Procedures in CrowdStrike LogScale
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/
LogScale Detection Query
// T1505.001 — SQL Server Stored Procedure Persistence
// Pattern 1: Shell processes spawned by SQL Server (xp_cmdshell)
#event_simpleName=ProcessRollup2
| ParentBaseFileName = /(?i)^(sqlservr|sqlagent)\.exe$/
| FileName = /(?i)^(cmd|powershell|wscript|cscript|mshta|certutil|bitsadmin|rundll32|net|net1|whoami|ipconfig)\.exe$/
| DetectionType := "SQL_Server_OS_Command_Execution"
| table([_time, ComputerName, UserName, DetectionType, FileName, CommandLine, ParentBaseFileName, ParentCommandLine])
// Pattern 2: Files written by SQL Server outside default directories (CLR assembly drop)
// Run separately or union with Pattern 1
// #event_simpleName=PeFileWritten OR #event_simpleName=SuspiciousFileWrite
// | ImageFileName = /(?i)(sqlservr|sqlagent)\.exe/
// | not TargetFileName = /(?i)(Microsoft SQL Server|MSSQL)/
// | DetectionType := "SQL_Server_File_Write"
// | table([_time, ComputerName, UserName, DetectionType, TargetFileName, ImageFileName])
// Pattern 3: Registry modifications for SQL Server xp_cmdshell or CLR config
// #event_simpleName=RegValueUpdate
// | TargetObject = /(?i)(SQL Server|MSSQLSERVER|SQLServer)/
// | TargetValueName = /(?i)(xp_cmdshell|clr enabled|startup|CLR)/
// | DetectionType := "SQL_Server_Config_Change"
// | table([_time, ComputerName, UserName, DetectionType, TargetObject, TargetValueName, TargetValueData, ImageFileName])
| groupBy([ComputerName, UserName, DetectionType], function=[count(as=event_count), collect([FileName, CommandLine, ParentBaseFileName])])
| sort(event_count, order=desc)CrowdStrike LogScale (CQL) detection for T1505.001 SQL stored procedure persistence using Falcon telemetry. Primary pattern uses ProcessRollup2 events to identify shell interpreters (cmd.exe, PowerShell) spawned by sqlservr.exe or sqlagent.exe, a strong indicator of xp_cmdshell execution. Additional commented patterns cover PeFileWritten events for CLR assembly drops outside SQL directories and RegValueUpdate events for xp_cmdshell/CLR enablement in SQL Server registry keys. Results are grouped by host and user for analyst triage.
Data Sources
Required Tables
False Positives & Tuning
- SQL Server Agent scheduled jobs legitimately configured with CmdExec job steps that invoke cmd.exe or PowerShell for maintenance automation
- Third-party database monitoring solutions (Redgate, Idera, SolarWinds) that hook into sqlservr.exe and spawn child diagnostic processes
- SQL Server Full-Text Search or Reporting Services processes spawning filter daemon (msftefd.exe) or rendering worker processes that match shell process patterns
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.