September 26, 2017 at 4:26 pm
In the last several months, there has been some DBA level mischief on my employer's 2 production database servers. Databases have been mysteriously created with unusual and inappropriate names, logins have been impersonated, logins have been disabled/denied and/or their passwords altered. No real harm has been done but it is mischief and the things that were done could only have been done by someone in the sysadmin role which limits these activities to only 7 people counting myself.
As the company's Senior DBA, I've been tasked with discovering the source(s) of the mischief. After considerable research, I deployed SQL Server Audits on the company's 2 production servers. After deployment and testing, I am very pleased with the results. Although I've taken a minimalist approach to tracking events and activities on the servers, I think it is possible to "tighten up" the audits to the point that I could see if someone modified an electron on a server...well, that's an exaggeration, but not by much. The SQL Server Audits can track the modification of almost any metadata of any importance except for one thing: SQL Server Agent.
I have not found a way to track sysadmin activity/misuse/modifications/mischief involving SQL Agent jobs. I've created, altered, and dropped SQL Agent jobs and no where in the audits I've created does any information appear that shows my activities.
Does anyone know a way to track sysadmin level activity involving SQL Agent and SQL Agent jobs?
September 26, 2017 at 4:38 pm
DML triggers on the relevant tables in msdb would be my first guess. Jobs are stored as data in MSDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 27, 2017 at 1:27 pm
I agree, GilaMonster, triggers look like my only option.
My research shows that these tables in the msdb database are most likely the best candidates for change-data-capture triggers to capture altered data about SQL Agent jobs, should the alterer not be the SQL Server Agent login or myself:
[msdb].[dbo].[sysjobs]
[msdb].[dbo].[sysjobhistory]
[msdb].[dbo].[sysjobactivity]
[msdb].[dbo].[sysjobschedules]
[msdb].[dbo].[sysjobservers]
[msdb].[dbo].[sysjobsteps]
The "sysjobs" table is the most obvious and useful one but a sysadmin could alter data in any of the others which could hide tampering, so I thought I should probably create change-data-capture triggers for them as well.
If you have different or additional thoughts on this subject, I'd like to read them.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply