Detection: Windows PowerShell Invoke-Sqlcmd Execution

Updated Date: 2025-02-03 ID: 5eb76fe2-a869-4865-8c4c-8cff424b18a1 Author: Michael Haag, Splunk Type: Hunting Product: Splunk Enterprise Security

Description

This detection identifies potentially suspicious usage of Invoke-Sqlcmd PowerShell cmdlet, which can be used for database operations and potential data exfiltration. The detection looks for suspicious parameter combinations and query patterns that may indicate unauthorized database access, data theft, or malicious database operations. Threat actors may prefer using PowerShell Invoke-Sqlcmd over sqlcmd.exe as it provides a more flexible programmatic interface and can better evade detection.

 1`powershell` EventCode=4104 ScriptBlockText="*invoke-sqlcmd*" 
 2| eval script_lower=lower(ScriptBlockText) 
 3| eval has_query=case( match(script_lower, "(?i)-query\\s+"), 1, match(script_lower, "(?i)-q\\s+"), 1, true(), 0 ), has_input_file=case( match(script_lower, "(?i)-inputfile\\s+"), 1, match(script_lower, "(?i)-i\\s+"), 1, true(), 0 ), has_url_input=case( match(script_lower, "(?i)-inputfile\\s+https?://"), 1, match(script_lower, "(?i)-i\\s+https?://"), 1, match(script_lower, "(?i)-inputfile\\s+ftp://"), 1, match(script_lower, "(?i)-i\\s+ftp://"), 1, true(), 0 ), has_admin_conn=case( match(script_lower, "(?i)-dedicatedadministratorconnection"), 1, true(), 0 ), has_suspicious_auth=case( match(script_lower, "(?i)-username\\s+sa\\b"), 1, match(script_lower, "(?i)-u\\s+sa\\b"), 1, match(script_lower, "(?i)-username\\s+admin\\b"), 1, match(script_lower, "(?i)-u\\s+admin\\b"), 1, true(), 0 ), has_suspicious_query=case( match(script_lower, "(?i)(xp_cmdshell
 4|sp_oacreate
 5|sp_execute_external
 6|openrowset
 7|bulk\\s+insert)"), 1, match(script_lower, "(?i)(master\\.\\.\\.sysdatabases
 8|msdb\\.\\.\\.backuphistory
 9|sysadmin
10|securityadmin)"), 1, match(script_lower, "(?i)(select.*from.*sys\\.
11|select.*password
12|dump\\s+database)"), 1, match(script_lower, "(?i)(sp_addextendedproc
13|sp_makewebtask
14|sp_addsrvrolemember)"), 1, match(script_lower, "(?i)(sp_configure.*show\\s+advanced
15|reconfigure
16|enable_xp_cmdshell)"), 1, match(script_lower, "(?i)(exec.*master\\.dbo\\.
17|exec.*msdb\\.dbo\\.)"), 1, match(script_lower, "(?i)(sp_password
18|sp_control_dbmasterkey_password
19|sp_dropextendedproc)"), 1, match(script_lower, "(?i)(powershell
20|cmd\\.exe
21|rundll32
22|regsvr32
23|certutil)"), 1, true(), 0 ), has_data_exfil=case( match(script_lower, "(?i)-outputas\\s+(dataset
24|datatables)"), 1, match(script_lower, "(?i)-as\\s+(dataset
25|datatables)"), 1, match(script_lower, "(?i)(for\\s+xml
26|for\\s+json)"), 1, match(script_lower, "(?i)(select.*into.*from
27|select.*into.*outfile)"), 1, true(), 0 ), has_cert_bypass=case( match(script_lower, "(?i)-trustservercertificate"), 1, true(), 0 )
28
29| eval risk_score=0 
30| eval risk_score=case( has_suspicious_query=1 AND has_data_exfil=1, risk_score + 90, has_url_input=1, risk_score + 80, has_suspicious_query=1, risk_score + 60, has_data_exfil=1, risk_score + 60, has_admin_conn=1, risk_score + 50, has_suspicious_auth=1, risk_score + 40, has_cert_bypass=1, risk_score + 20, true(), risk_score )
31
32| eval command_type=case( match(script_lower, "xp_cmdshell"), "xp_cmdshell abuse", match(script_lower, "https?://"), "Remote file execution", match(script_lower, "sys\\.server_principals"), "System enumeration", match(script_lower, "fn_my_permissions"), "Permission enumeration", match(script_lower, "username\\s+sa\\b"), "SA account usage", match(script_lower, "show\\s+advanced\\s+options"), "Configuration change attempt", match(script_lower, "select.*from\\s+customers"), "Large data export", match(script_lower, "select.*password"), "Sensitive data query", match(script_lower, "sp_configure.*xp_cmdshell"), "Enable xp_cmdshell", 1=1, "General database access" )
33
34| eval risk_factors=mvappend( if(has_suspicious_query=1 AND has_data_exfil=1, "High-risk query with data extraction: ".command_type, null()), if(has_url_input=1, "Remote file input detected in command", null()), if(has_suspicious_query=1, "Suspicious SQL query pattern: ".command_type, null()), if(has_data_exfil=1, "Potential data exfiltration using ".command_type, null()), if(has_admin_conn=1, "Administrative database connection", null()), if(has_suspicious_auth=1, "Suspicious authentication method used", null()), if(has_cert_bypass=1, "Certificate validation bypassed", null()) ) 
35| eval risk_message="PowerShell Invoke-Sqlcmd execution with risk factors: ".mvjoin(risk_factors, ", ")
36
37| where risk_score >= 30 
38| stats count min(_time) as firstTime max(_time) as lastTime by EventCode ScriptBlockText UserID Computer risk_message risk_score command_type 
39| rename Computer as dest, UserID as user 
40| `security_content_ctime(firstTime)` 
41| `security_content_ctime(lastTime)` 
42| `windows_powershell_invoke_sqlcmd_execution_filter`

Data Source

Name Platform Sourcetype Source
Powershell Script Block Logging 4104 Windows icon Windows 'xmlwineventlog' 'XmlWinEventLog:Microsoft-Windows-PowerShell/Operational'

Macros Used

Name Value
powershell (source=WinEventLog:Microsoft-Windows-PowerShell/Operational OR source="XmlWinEventLog:Microsoft-Windows-PowerShell/Operational" OR source=WinEventLog:PowerShellCore/Operational OR source="XmlWinEventLog:PowerShellCore/Operational")
windows_powershell_invoke_sqlcmd_execution_filter search *
windows_powershell_invoke_sqlcmd_execution_filter is an empty macro by default. It allows the user to filter out any results (false positives) without editing the SPL.

Annotations

- MITRE ATT&CK
+ Kill Chain Phases
+ NIST
+ CIS
- Threat Actors
ID Technique Tactic
T1059.001 PowerShell Execution
T1059.003 Windows Command Shell Execution
Installation
DE.AE
CIS 10

Default Configuration

This detection is configured by default in Splunk Enterprise Security to run with the following settings:

Setting Value
Disabled true
Cron Schedule 0 * * * *
Earliest Time -70m@m
Latest Time -10m@m
Schedule Window auto
Creates Risk Event False
This configuration file applies to all detections of type hunting.

Implementation

To successfully implement this detection, you need to be ingesting PowerShell logs with Script Block Logging and Module Logging enabled. The detection looks for Invoke-Sqlcmd usage in PowerShell scripts and evaluates the parameters and queries for suspicious patterns. Configure your PowerShell logging to capture script block execution and ensure the logs are mapped to the PowerShell node of the Endpoint data model. The analytic will need to be tuned based on organization specific data. Currently, set to hunting to allow for tuning. Invoke-Sqlcmd is a legitimate tool for database management and scripting tasks within enterprise environments.

Known False Positives

Database administrators and developers frequently use Invoke-Sqlcmd as a legitimate tool for various database management tasks. This includes running automated database maintenance scripts, performing ETL (Extract, Transform, Load) processes, executing data migration jobs, implementing database deployment and configuration scripts, and running monitoring and reporting tasks. To effectively manage false positives in your environment, consider implementing several mitigation strategies. First, establish a whitelist of known administrator and service accounts that regularly perform these operations. Second, create exceptions for approved script paths where legitimate database operations typically occur. Additionally, it's important to baseline your environment's normal PowerShell database interaction patterns and implement monitoring for any deviations from these established patterns. Finally, consider adjusting the risk score thresholds based on your specific environment and security requirements to achieve an optimal balance between security and operational efficiency.

Associated Analytic Story

References

Detection Testing

Test Type Status Dataset Source Sourcetype
Validation ✅ Passing N/A N/A N/A
Unit ✅ Passing Dataset XmlWinEventLog:Microsoft-Windows-PowerShell/Operational XmlWinEventLog
Integration ✅ Passing Dataset XmlWinEventLog:Microsoft-Windows-PowerShell/Operational XmlWinEventLog

Replay any dataset to Splunk Enterprise by using our replay.py tool or the UI. Alternatively you can replay a dataset into a Splunk Attack Range

Source: GitHub | Version: 1