Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Auditing for New SQL Server Agent Jobs


By:   |   Last Updated: 2017-09-22   |   Comments   |   Related Tips: More > 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:

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,
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:

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,
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


Last Updated: 2017-09-22


next webcast button


next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools