SQL Stored Procedures (T1505.001) is a persistence technique. MITRE lists Windows and Linux. Stored procedures are saved, reusable code that database users call by name or that runs on defined events, for example when the SQL server application starts or restarts.
How attackers use it
MITRE explains that adversaries can craft malicious stored procedures to keep a foothold in a database server. Because the code can fire on a defined event such as a restart, it can survive reboots.
To run operating system commands from SQL syntax, the adversary may first have to enable extra functionality, such as xp_cmdshell in Microsoft SQL Server. Microsoft SQL Server can also enable common language runtime (CLR) integration, which lets developers write stored procedures in any .NET language. MITRE notes that adversaries may craft or modify CLR assemblies linked to stored procedures, since those can be made to run arbitrary commands.
How to detect it
MITRE's analytics focus on two things:
- Creation or modification of stored procedures that invoke xp_cmdshell or CLR assemblies for command execution and persistence.
- Stored procedures that invoke OS level commands through an xp_cmdshell equivalent or through user defined functions.
Watch for configuration changes that turn those features on, and for new or altered procedures outside change windows. Also watch the database service process: it should rarely start a command shell.
How to reduce the risk
MITRE lists three mitigations:
- Audit: regularly check component software on critical services for unexpected changes.
- Code Signing: make sure application component binaries are signed by the correct developers.
- Privileged Account Management: do not use administrator accounts that can add component software for day to day work, where they may be exposed to adversaries on unprivileged systems.
Check your own environment
Read only checks for Microsoft SQL Server, run in a query window:
-- Are xp_cmdshell and CLR integration enabled?
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN ('xp_cmdshell', 'clr enabled');
-- Which user defined CLR assemblies exist?
SELECT name, create_date, modify_date
FROM sys.assemblies
WHERE is_user_defined = 1;
If either feature is on and nobody can explain why, treat that as a finding to investigate.
Quick check
Why does xp_cmdshell matter to this technique?
It lets SQL syntax run operating system commands. Adversaries may need to enable it before a stored procedure can do that.
