Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Notify when CmdExec Job Steps Scheduled..? Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 10:17 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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, ...


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1361484
Posted Wednesday, September 19, 2012 12:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1361555
Posted Thursday, September 20, 2012 2:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
Thanks for the idea, but I'm pretty sure that would void their warranty so I cannot pursue that.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1362326
Posted Thursday, September 20, 2012 2:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1362328
Posted Friday, September 21, 2012 8:43 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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?


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1362724
Posted Friday, September 21, 2012 10:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1362819
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse