September 19, 2012 at 10:17 am
I have been working with a customer to help them implement a departmental SQL Server that conforms to their Corporation's security and auditing requirements for SQL Servers that are not under their control. Suprisingly, we have been able to implement everything required, except for one very odd thing:
"Send a notification whenever a CmdExec job is scheduled."
So here's the bullet list of the situation:
1. SQL Server 2008 R2
2. C2 Auditing is already implemented
3. The c2 traces, along with contents of sys.connections and sys.sessions are remotely extracted and archived every 2 minutes from another (secure) SQL server.
4. I have implemented other "Notifications" as DBmail sent based on analysis of the most recent extracted data (from #3 above) every 2 minutes.
5. Yes, I know that "CmdExec" is a Step attribute, not a Job attribute, I am assuming that they mean any Job with a CmdExec Step in it.
6. And Yes, "scheduled" is ambiguous. I think that I could go with either "a schedule for the Job was created or enabled" or "the Job started executing", since they are not sure what they meant either (don't ask, there's no help there).
Now my problem is that I cannot figure out a reasonable way to do this. As far as I can tell, there's no SQL Trace events in C2 (or out of it for that matter) that pertain to the SQL Agent system, and no other reasonable way to figure this out is apparent to me.
At this point I am open to suggestions, though I need to try to keep it easy to implement within the sturture that we already have.
Any Ideas?
Thanks, ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 19, 2012 at 12:16 pm
It could be characterized as a hack, but adding a trigger to the relevant msdb system tables might get you what you need:
USE msdb
GO
IF OBJECT_ID(N'dbo.sysjobs_update') IS NOT NULL
DROP TRIGGER dbo.sysjobs_update;
GO
CREATE TRIGGER dbo.sysjobs_update ON sysjobs
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- send out alert email
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@domain.com',
@body = 'Test',
@subject = 'sysjobs has changed';
END
GO
After adding this I actually received multiple emails when creating a new job using SSMS even though I would have expected 0 so you may need to provide for those cases when the GUI or system procs touch the system tables multiple times or in ways that are irrelevant to your auditing goals.
Standard disclaimer: this approach may be too sketchy for some as it is likely unsupported. For auditing consider whether it should be written to fail the initiating operation if the audit operation fails. Before adopting consider the implications when it comes time to apply patches or migrate the instance.
Another option might be to take copies of the tables from 'the secure sql server' you mentioned, do a diff and email if anything has changed. However that leaves the door open for a change, then changeback, in between snaps.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 20, 2012 at 2:41 pm
Thanks for the idea, but I'm pretty sure that would void their warranty so I cannot pursue that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 20, 2012 at 2:45 pm
Extended Events session that writes to a file? The volume should be low enough that you can get away with later writing the file to a table or load up an SB queue that does more work if you need to respond immediately with a heavier set of logic.
edit: file then table/queue
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 21, 2012 at 8:43 am
opc.three (9/20/2012)
Extended Events session that writes to a file? The volume should be low enough that you can get away with later writing the file to a table or load up an SB queue that does more work if you need to respond immediately with a heavier set of logic.edit: file then table/queue
The volume's not the issue. The issue is how do you define and then capture what you are looking for?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 21, 2012 at 10:08 am
I think you would need to watch for anything that can change the enabled flag in msdb.dbo.sysschedules as well as anything that can add new rows to msdb.dbo.sysjobschedules.
I think this means watching for proc or t-sql events with sp_add_schedule, sp_update_schedule, sp_update_jobschedule (deprecated but could be called directly) and sp_attach_schedule in the text.
You might also need to watch for direct updates to the two tables which are permitted by the system but obviously discouraged in favor of using the system procs. Updating the tables would achieve the desired effect after a cache reset or service restart. A trigger would help there but I understand the concern.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply