Detect SQL Stored Procedures in Elastic Security
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/
Elastic Detection Query
sequence by host.name with maxspan=5m
[process where event.type == "start" and
process.parent.name : ("sqlservr.exe", "sqlagent.exe") and
process.name : ("cmd.exe", "powershell.exe", "wscript.exe", "cscript.exe",
"mshta.exe", "certutil.exe", "bitsadmin.exe", "rundll32.exe",
"net.exe", "net1.exe", "whoami.exe", "ipconfig.exe")]
OR
any where event.category == "file" and event.action == "creation" and
process.name : ("sqlservr.exe", "sqlagent.exe") and
not file.path : ("*\\Microsoft SQL Server\\*", "*\\MSSQL\\*")
OR
any where event.category == "registry" and
registry.key : ("*SQL Server*", "*MSSQLSERVER*", "*SQLServer*") and
registry.value : ("xp_cmdshell", "clr enabled", "startup", "CLR")Detects SQL Server stored procedure persistence via three signals: (1) child processes (cmd.exe, PowerShell, etc.) spawned by sqlservr.exe or sqlagent.exe indicating xp_cmdshell execution; (2) file creation events from SQL Server processes outside standard SQL directories indicating CLR assembly drops; (3) registry modifications to SQL Server configuration keys enabling xp_cmdshell or CLR integration.
Data Sources
Required Tables
False Positives & Tuning
- Legitimate SQL Server maintenance scripts that invoke cmd.exe or PowerShell for backup, restore, or ETL operations executed by DBAs
- SQL Server Agent jobs legitimately configured to run OS-level commands as part of scheduled maintenance (log shipping, DBCC checks)
- SQL Server installation or patching processes writing assemblies or configuration files outside default directories
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.