Detection rules › Kusto
SQL Server spawning suspicious child process
This query looks for potential abuse of the SQL Server stored procedure xp_cmdshell which allows command execution on the OS. Running xp_cmdshell on the system triggers the follow process chain: sqlservr.exe => xp_cmdshell 'whoami' => "cmd.exe /c" whoami => whoami.exe. This rule tries to identify running of suspicious commands as a grandchild of sqlservr.exe. The rule is based on a block-list of executables of LOLBINs and other known recon commands or any executable executed with a low prevalence.
Known false positives
- The xp_cmdshell functionality is often used by legitimate applications for interfacing with the OS and performing all kinds of maintenance tasks (i.e., back-ups, reporting, etc.).
MITRE ATT&CK coverage
| Tactic | Techniques |
|---|---|
| Persistence | |
| Privilege Escalation |
Telemetry coverage
Rule body
let timeframe = 2*1h;
let default_global_prevalence = 0;
let lolbins = dynamic(["at.exe", "atbroker.exe", "bash.exe", "bitsadmin.exe", "certreq.exe", "certutil.exe", "cmd.exe", "cmdkey.exe", "cmstp.exe", "control.exe", "csc.exe", "cscript.exe", "desktopimgdownldr.exe", "dfsvc.exe", "diantz.exe", "diskshadow.exe", "dnscmd.exe", "esentutl.exe", "eventvwr.exe", "expand.exe", "extexport.exe", "extrac32.exe", "findstr.exe", "forfiles.exe", "ftp.exe", "gfxdownloadwrapper.exe", "gpscript.exe", "hh.exe", "ie4uinit.exe", "ieexec.exe", "ilasm.exe", "infdefaultinstall.exe", "installutil.exe", "jsc.exe", "makecab.exe", "mavinject.exe", "microsoft.workflow.compiler.exe", "mmc.exe", "mpcmdrun.exe", "msbuild.exe", "msconfig.exe", "msdt.exe", "mshta.exe", "msiexec.exe", "netsh.exe", "odbcconf.exe", "pcalua.exe", "pcwrun.exe", "pktmon.exe", "presentationhost.exe", "print.exe", "psr.exe", "rasautou.exe", "reg.exe", "regasm.exe", "regedit.exe", "regini.exe", "register-cimprovider.exe", "regsvcs.exe", "regsvr32.exe", "replace.exe", "rpcping.exe", "rundll32.exe", "runonce.exe", "runscripthelper.exe", "sc.exe", "schtasks.exe", "scriptrunner.exe", "syncappvpublishingserver.exe", "ttdinject.exe", "tttracer.exe", "vbc.exe", "verclsid.exe", "wab.exe", "wmic.exe", "wscript.exe", "wsreset.exe", "xwizard.exe", "agentexecutor.exe", "appvlp.exe", "bginfo.exe", "cdb.exe", "csi.exe", "devtoolslauncher.exe", "dnx.exe", "dotnet.exe", "dxcap.exe", "excel.exe", "mftrace.exe", "msdeploy.exe", "msxsl.exe", "ntdsutil.exe", "powerpnt.exe", "rcsi.exe", "sqldumper.exe", "sqlps.exe", "sqltoolsps.exe", "squirrel.exe", "te.exe", "tracker.exe", "vsjitdebugger.exe", "winword.exe", "wsl.exe", "powershell.exe", "pwsh.exe"]);
let binaries_of_interest = dynamic(["net.exe", "net1.exe", "whoami.exe", "ipconfig.exe", "tasklist.exe", "quser.exe", "tracert.exe", "route.exe", "runas.exe", "klist.exe", "wevtutil.exe", "wmiprvse.exe", "powershell.exe", "bash.exe", "qwinsta.exe", "rwinsta.exe", "replace.exe", "findstr.exe", "icacls.exe", "cacls.exe", "xcopy.exe", "robocopy.exe", "takeown.exe", "vssadmin.exe", "nltest.exe", "nltestk.exe", "sctasks.exe", "nbtstat.exe", "nbtinfo.exe", "mofcomp.exe", "nltestrk.exe", "dnscmd.exe", "registercimprovider.exe", "registercimprovider2.exe", "procdump", "ru.exe", "pspasswd.exe", "psexec.c", "psexec.exe", "pslist.exe", "regsize", "pskill.exe", "pkill.exe", "wsmprovhost.exe", "fltmc.exe", "sdbinst.exe"]);
// Merge both lists into one reference list.
let original_file_name_set=array_concat(lolbins,binaries_of_interest);
let allGrandChilderen = materialize(
DeviceProcessEvents
| where ingestion_time() >= ago(timeframe)
| where ActionType =~ "ProcessCreated"
| where InitiatingProcessParentFileName =~ "sqlservr.exe"
| where InitiatingProcessCommandLine startswith "\"cmd.exe\" /c"
);
let allSuspiciousHashes = (
// The reason for looking at grand children of SQL Server instead of children is that xp_cmdshell always runs a command
// via cmd.exe /c so the child process will always be cmd.exe. We are interested in the grand child process which is the actual command.
allGrandChilderen
// FileProfile is case-sensitive and works on lower-case hashes.
| where not(isempty(SHA1))
| summarize DeviceCount=dcount(DeviceId) by SHA1
| top 1000 by DeviceCount asc
| invoke FileProfile(SHA1, 1000)
| where not(ProfileAvailability =~ "Error")
| where coalesce(GlobalPrevalence,default_global_prevalence) < 250
| where SignatureState != "SignedValid"
);
allGrandChilderen
| where FileName in~ (original_file_name_set) or SHA1 in ((allSuspiciousHashes))
| join kind=leftouter allSuspiciousHashes on SHA1
// Begin environment-specific filter.
// End environment-specific filter.
Stages and Predicates
Parameters
let timeframe = 2*1h;
let default_global_prevalence = 0;
let original_file_name_set = array_concat(lolbins,binaries_of_interest);
let allGrandChilderen is inlined into the numbered stages below.
Let binding: lolbins
let lolbins = dynamic(["at.exe", "atbroker.exe", "bash.exe", "bitsadmin.exe", "certreq.exe", "certutil.exe", "cmd.exe", "cmdkey.exe", "cmstp.exe", "control.exe", "csc.exe", "cscript.exe", "desktopimgdownldr.exe", "dfsvc.exe", "diantz.exe", "diskshadow.exe", "dnscmd.exe", "esentutl.exe", "eventvwr.exe", "expand.exe", "extexport.exe", "extrac32.exe", "findstr.exe", "forfiles.exe", "ftp.exe", "gfxdownloadwrapper.exe", "gpscript.exe", "hh.exe", "ie4uinit.exe", "ieexec.exe", "ilasm.exe", "infdefaultinstall.exe", "installutil.exe", "jsc.exe", "makecab.exe", "mavinject.exe", "microsoft.workflow.compiler.exe", "mmc.exe", "mpcmdrun.exe", "msbuild.exe", "msconfig.exe", "msdt.exe", "mshta.exe", "msiexec.exe", "netsh.exe", "odbcconf.exe", "pcalua.exe", "pcwrun.exe", "pktmon.exe", "presentationhost.exe", "print.exe", "psr.exe", "rasautou.exe", "reg.exe", "regasm.exe", "regedit.exe", "regini.exe", "register-cimprovider.exe", "regsvcs.exe", "regsvr32.exe", "replace.exe", "rpcping.exe", "rundll32.exe", "runonce.exe", "runscripthelper.exe", "sc.exe", "schtasks.exe", "scriptrunner.exe", "syncappvpublishingserver.exe", "ttdinject.exe", "tttracer.exe", "vbc.exe", "verclsid.exe", "wab.exe", "wmic.exe", "wscript.exe", "wsreset.exe", "xwizard.exe", "agentexecutor.exe", "appvlp.exe", "bginfo.exe", "cdb.exe", "csi.exe", "devtoolslauncher.exe", "dnx.exe", "dotnet.exe", "dxcap.exe", "excel.exe", "mftrace.exe", "msdeploy.exe", "msxsl.exe", "ntdsutil.exe", "powerpnt.exe", "rcsi.exe", "sqldumper.exe", "sqlps.exe", "sqltoolsps.exe", "squirrel.exe", "te.exe", "tracker.exe", "vsjitdebugger.exe", "winword.exe", "wsl.exe", "powershell.exe", "pwsh.exe"]);
Let binding: binaries_of_interest
let binaries_of_interest = dynamic(["net.exe", "net1.exe", "whoami.exe", "ipconfig.exe", "tasklist.exe", "quser.exe", "tracert.exe", "route.exe", "runas.exe", "klist.exe", "wevtutil.exe", "wmiprvse.exe", "powershell.exe", "bash.exe", "qwinsta.exe", "rwinsta.exe", "replace.exe", "findstr.exe", "icacls.exe", "cacls.exe", "xcopy.exe", "robocopy.exe", "takeown.exe", "vssadmin.exe", "nltest.exe", "nltestk.exe", "sctasks.exe", "nbtstat.exe", "nbtinfo.exe", "mofcomp.exe", "nltestrk.exe", "dnscmd.exe", "registercimprovider.exe", "registercimprovider2.exe", "procdump", "ru.exe", "pspasswd.exe", "psexec.c", "psexec.exe", "pslist.exe", "regsize", "pskill.exe", "pkill.exe", "wsmprovhost.exe", "fltmc.exe", "sdbinst.exe"]);
Let binding: allSuspiciousHashes
let allSuspiciousHashes = (
allGrandChilderen
| where not(isempty(SHA1))
| summarize DeviceCount=dcount(DeviceId) by SHA1
| top 1000 by DeviceCount asc
| invoke FileProfile(SHA1, 1000)
| where not(ProfileAvailability =~ "Error")
| where coalesce(GlobalPrevalence,default_global_prevalence) < 250
| where SignatureState != "SignedValid"
);
Stage 1: source
let allGrandChilderen
Stage 2: source
let allSuspiciousHashes
Stage 3: source
DeviceProcessEvents
Stage 4: where
where ...
Stage 5: where
where ActionType =~ "ProcessCreated"
Stage 6: where
where InitiatingProcessParentFileName =~ "sqlservr.exe"
Stage 7: where
where InitiatingProcessCommandLine startswith "\"cmd.exe\" /c"
Stage 8: where
where (FileName =~ "original_file_name_set")
Stage 9: join
join kind=leftouter (allSuspiciousHashes) on SHA1
Stage 10: summarize aggregation inside the join branch
summarize by SHA1
Exclusions
The rule actively suppresses these predicates.
| Field | Kind | Excluded values | Search |
|---|---|---|---|
ProfileAvailability | eq | Error | excludes:ProfileAvailability field:"ProfileAvailability" value:"Error" |
SHA1 | is_null | excludes:SHA1 |
Indicators
These rows show field, operator, and value matches.
| Field | Kind | Values | Search |
|---|---|---|---|
ActionType | eq |
| field:"ActionType" kind:eq value:"ProcessCreated" |
FileName | in |
| field:"file_name" kind:in value:"original_file_name_set" |
GlobalPrevalence | lt |
| field:"GlobalPrevalence" kind:lt value:"250" |
InitiatingProcessCommandLine | starts_with |
| field:"ParentCommandLine" kind:starts_with |
InitiatingProcessParentFileName | eq |
| field:"GrandParentImage" kind:eq value:"sqlservr.exe" |
SignatureState | ne |
| field:"SignatureState" kind:ne value:"SignedValid" |
Output fields
These fields are emitted when the rule matches.
| Field | Source |
|---|---|
SHA1 | summarize |