Detect SQL Stored Procedures in IBM QRadar
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/
QRadar Detection Query
SELECT
DATEFORMAT(starttime, 'yyyy-MM-dd HH:mm:ss') AS event_time,
logsourcename(logsourceid) AS log_source,
"username",
"sourceip",
"hostname",
QIDNAME(qid) AS event_name,
"EventID",
"ParentProcessName",
"ProcessName",
"CommandLine",
CASE
WHEN LOWER("ParentProcessName") LIKE '%sqlservr.exe%' OR LOWER("ParentProcessName") LIKE '%sqlagent.exe%'
AND (LOWER("ProcessName") LIKE '%cmd.exe%' OR LOWER("ProcessName") LIKE '%powershell.exe%'
OR LOWER("ProcessName") LIKE '%wscript.exe%' OR LOWER("ProcessName") LIKE '%cscript.exe%'
OR LOWER("ProcessName") LIKE '%mshta.exe%' OR LOWER("ProcessName") LIKE '%certutil.exe%'
OR LOWER("ProcessName") LIKE '%bitsadmin.exe%' OR LOWER("ProcessName") LIKE '%rundll32.exe%'
OR LOWER("ProcessName") LIKE '%whoami.exe%')
THEN 'SQL_Server_OS_Command_Execution'
WHEN LOWER("ProcessName") LIKE '%sqlservr.exe%'
AND (LOWER("CommandLine") LIKE '%xp_cmdshell%' OR LOWER("CommandLine") LIKE '%sp_addstartup%'
OR LOWER("CommandLine") LIKE '%clr enabled%' OR LOWER("CommandLine") LIKE '%startup procedure%')
THEN 'SQL_Server_Config_Abuse'
ELSE 'SQL_Server_Suspicious_File_Write'
END AS detection_type
FROM events
WHERE
LOGSOURCETYPEID IN (12, 13, 15)
AND "EventID" IN ('1', '4688', '11', '13')
AND (
(LOWER("ParentProcessName") LIKE '%sqlservr%' OR LOWER("ParentProcessName") LIKE '%sqlagent%')
OR (LOWER("ProcessName") LIKE '%sqlservr%'
AND (LOWER("CommandLine") LIKE '%xp_cmdshell%'
OR LOWER("CommandLine") LIKE '%sp_addstartup%'
OR LOWER("CommandLine") LIKE '%clr enabled%'))
)
AND starttime > NOW() - 1 DAYS
ORDER BY starttime DESC
LAST 1000AQL rule detecting SQL Server stored procedure abuse across three patterns: OS command shells spawned by sqlservr.exe/sqlagent.exe (xp_cmdshell indicator), SQL Server processes executing configuration-altering commands (sp_addstartup, CLR enablement), and suspicious file write events from SQL Server processes. Correlates Sysmon EventID 1 (process create), 11 (file create), and 13 (registry value set) log sources.
Data Sources
Required Tables
False Positives & Tuning
- DBA-initiated SQL Agent jobs with OS command steps for legitimate backup automation using xp_cmdshell
- SQL Server integration services (SSIS) packages that invoke PowerShell or cmd for ETL pipeline processing
- Third-party SQL monitoring tools (SolarWinds DPA, SentryOne) that attach to sqlservr.exe and execute diagnostic child processes
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.