Auditing for New SQL Server Agent Jobs

By: K. Brian Kelley | | Related Tips:More >SQL Server Agent

Attend a SQL Server Conference for FREE >> click to learn more

Problem

Reading through the various ways someone can run code as a sysadmin on SQL Server, I realized that someone could create aSQL Server Agent Job to run and make its owner thesa account or anothersysadmin 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 withCONTROL 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 astored 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 Update:
2017-09-22

MSSQLTips责编内容来自:MSSQLTips (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Auditing for New SQL Server Agent Jobs

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录