Auditing for New SQL Server Agent Jobs

By: K. Brian Kelley   |   Updated: 2017-09-22   |   Comments (1)   |   Related: > SQL Server Agent

Problem

Reading through the various ways someone can run code as a sysadmin on SQL Server, I realized that someone could create a SQL Server Agent Job to run and make its owner the sa account or another sysadmin level account. Then the code would run with those privileges. How do I audit for these newly created jobs?

Solution

The good news is that all of the job information we care about is in the msdb database. As a result, with the result of a few queries, we can detect the creation of new jobs. We’re interested in the sysjobs table in msdb, along with sys.server_principals, so that we can determine what jobs are newly created. Here we’re looking for any jobs created within the last day:

SELECT j.[name] AS JobName, l.[name] AS JobOwner,
   j.date_created
FROM msdb.dbo.sysjobs AS J
  JOIN sys.server_principals AS L
    ON J.owner_sid = L.[sid]
WHERE j.date_created > DATEADD(DAY, -1, GETDATE());
   

It just so happens that I have such a job, and the query returns a result:

We could tweak the query to only return jobs owned by members of sysadmin, but then we’d also need to tweak the query to return any who had the CONTROL SERVER permissions, too. We can do so like so, but my preference is to return any created job, unless I know that jobs are being created automatically, such as with SQL Server Reporting Services and subscriptions.

SELECT j.[name] AS JobName, AllLogins.[name] AS JobOwner,
   j.date_created
FROM msdb.dbo.sysjobs AS J
   JOIN (
      -- Get all members of sysadmin
      SELECT L.[name], L.[sid]
      FROM sys.server_principals AS L
         JOIN sys.server_role_members AS RM
           ON L.principal_id = RM.member_principal_id
         JOIN sys.server_principals AS R
           ON RM.role_principal_id = R.principal_id
      WHERE R.[name] = 'sysadmin'

      UNION

      -- Control Server
      SELECT L.[name], L.[sid]
      FROM sys.server_principals AS L
         JOIN sys.server_permissions AS Perm
           ON L.principal_id = Perm.grantee_principal_id
      WHERE Perm.[type] = 'CL' AND Perm.[state] = 'G'
      ) AS AllLogins
   ON J.[owner_sid] = AllLogins.[sid]
WHERE j.date_created > DATEADD(DAY, -1, GETDATE());
   

I intentionally created one job that’s owned by a login with CONTROL SERVER, but which isn’t a member of the sysadmin fixed server role:

Modified Jobs

Another attack vector is to simply modify an existing job. Since looking at the modified date can tell us when someone has modified a job, we can modify the queries from before. For all jobs that have been changed (this will also pick up new jobs):

SELECT j.[name] AS JobName, l.[name] AS JobOwner,
   j.date_modified
FROM msdb.dbo.sysjobs AS J
  JOIN sys.server_principals AS L
    ON J.owner_sid = L.[sid]
WHERE j.date_modified > DATEADD(DAY, -1, GETDATE());
   

And for cases when we’re looking for the specific privileged logins as owners:

SELECT j.[name] AS JobName, AllLogins.[name] AS JobOwner,
   j.date_modified
FROM msdb.dbo.sysjobs AS J
   JOIN (
      -- Get all members of sysadmin
      SELECT L.[name], L.[sid]
      FROM sys.server_principals AS L
         JOIN sys.server_role_members AS RM
           ON L.principal_id = RM.member_principal_id
         JOIN sys.server_principals AS R
           ON RM.role_principal_id = R.principal_id
      WHERE R.[name] = 'sysadmin'

      UNION

      -- Control Server
      SELECT L.[name], L.[sid]
      FROM sys.server_principals AS L
         JOIN sys.server_permissions AS Perm
           ON L.principal_id = Perm.grantee_principal_id
      WHERE Perm.[type] = 'CL' AND Perm.[state] = 'G'
      ) AS AllLogins
   ON J.[owner_sid] = AllLogins.[sid]
WHERE j.date_modified > DATEADD(DAY, -1, GETDATE());
   

Automating These Queries

Of course, if you’re going to audit, you’re going to want to have these run on a schedule. Preferably, you want these to run using some scheduling solution other than SQL Server Agent, since that’s what we’re interested in monitoring. Task Scheduler is a possibility, especially on a system with the SQL Server client tools installed that’s not one of the SQL Servers you care about. Of course, if you have a more robust job scheduling solution, you should consider that first.

Why should we avoid SQL Server Agent? If you have a SQL Server Agent job that gives some indication you’re monitoring jobs, then an attacker may cover his or her tracks. Obviously, a DBA or someone with similar rights who knows about the monitoring can work around the solution, and you’ll be looking for other ways to detect changes, but this solution is designed to catch someone who doesn’t know that this type of monitoring is in place, such as someone from the outside. After all, too many organizations don’t do any monitoring, so this is typically a safe assumption to make.

What This Doesn’t Catch

We’re merely looking at if a job has been created or changed. However, if a job step makes a call to a stored procedure or calls a script at the command-line, we won’t pick up those changes. You’ll need other monitoring solutions to detect changes downstream.

Next Steps

• Learn how to query job history data for your SQL Server Agent jobs.
• Find out other information you can query with respect to Agent jobs.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips