Auditing for New SQL Server Agent Jobs

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,<br>   j.date_created<br>FROM msdb.dbo.sysjobs AS J<br>  JOIN sys.server_principals AS L<br>    ON J.owner_sid = L.[sid]<br>WHERE j.date_created > DATEADD(DAY, -1, GETDATE());

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

Query Result Showing Job Created Within the Last Day

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,<br>   j.date_created<br>FROM msdb.dbo.sysjobs AS J<br>   JOIN (<br>      -- Get all members of sysadmin<br>      SELECT L.[name], L.[sid]<br>      FROM sys.server_principals AS L<br>         JOIN sys.server_role_members AS RM<br>           ON L.principal_id = RM.member_principal_id<br>         JOIN sys.server_principals AS R<br>           ON RM.role_principal_id = R.principal_id<br>      WHERE R.[name] = 'sysadmin'<br><br>      UNION<br><br>      -- Control Server<br>      SELECT L.[name], L.[sid]<br>      FROM sys.server_principals AS L<br>         JOIN sys.server_permissions AS Perm<br>           ON L.principal_id = Perm.grantee_principal_id<br>      WHERE Perm.[type] = 'CL' AND Perm.[state] = 'G'<br>      ) AS AllLogins<br>   ON J.[owner_sid] = AllLogins.[sid]<br>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:

Query Returns which focus on jobs owned by sysadmin or CONTROL SERVER

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,<br>   j.date_modified<br>FROM msdb.dbo.sysjobs AS J<br>  JOIN sys.server_principals AS L<br>    ON J.owner_sid = L.[sid]<br>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,<br>   j.date_modified<br>FROM msdb.dbo.sysjobs AS J<br>   JOIN (<br>      -- Get all members of sysadmin<br>      SELECT L.[name], L.[sid]<br>      FROM sys.server_principals AS L<br>         JOIN sys.server_role_members AS RM<br>           ON L.principal_id = RM.member_principal_id<br>         JOIN sys.server_principals AS R<br>           ON RM.role_principal_id = R.principal_id<br>      WHERE R.[name] = 'sysadmin'<br><br>      UNION<br><br>      -- Control Server<br>      SELECT L.[name], L.[sid]<br>      FROM sys.server_principals AS L<br>         JOIN sys.server_permissions AS Perm<br>           ON L.principal_id = Perm.grantee_principal_id<br>      WHERE Perm.[type] = 'CL' AND Perm.[state] = 'G'<br>      ) AS AllLogins<br>   ON J.[owner_sid] = AllLogins.[sid]<br>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

Related Posts

One comment

  1. Many thanks for the information.

    I suggest to use “LEFT JOIN sys.server_principals AS L” – since there could be Jobs created by users from Domain Groups,
    such users don’t appear in Logins as a separate entities, but still appear as job owners…

Leave a Reply

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