Author: tames.mctigue

Database servers store massive amounts of data, often including sensitive information. It is not uncommon for there to be databases holding millions of rows of data, where a small subset of rows are considered critical or sensitive. This could be anything from a Social Security number to an EventSentry entry of a security event. Being notified when existing data in your database changes is crucial for log data, and can be accomplished by using triggers with Microsoft SQL Server.

For those of you not familiar with triggers, a database trigger executes code in response to events on a table or database. Triggers are essentially hooks into a table, and they usually execute SQL statements as a response to another SQL statement.

Since we love the windows event log, we’ll take advantage of SQL Server’s ability for triggers to log an event to the event log when a row in a table is modified. This allows us to not only log that activity, but also get notified immediately when suspicious or important activity occurs in the EventSentry database.

In EventSentry, we have a table named ESEventlogMain that stores Windows event information. This table constantly gets new data inserted into it, and it often gets purged as well to manage the size of the database. However, there is no reason this data should ever be modified. If it is, then we know that something is amiss and we want to trigger an event in the event log. It is also useful to know what account made that change.

The first step is to create the message in SQL. You can use this SQL statement to create it:

sp_addmessage 80000, 10, ‘Data Integrity Alert: %s’, @with_log = TRUE

The first argument is a unique SQL server message ID that should be 50001 or higher, you can delete it again using sp_dropmessage. The number 10 is the severity level, but you can read more about the different options for sp_addmessage here.

Now we create the trigger that will use this message:

CREATE TRIGGER Trigger_ESEventlogMain_Modified ON
ESEventlogMain
FOR UPDATE
AS

IF UPDATE(eventmessage) OR UPDATE(eventid) OR UPDATE(eventtime) OR UPDATE(eventcomputer)
BEGIN

     DECLARE @Msg VARCHAR(8000)
     DECLARE @EventNumber INT
     DECLARE @EventID INT
     DECLARE @Computer VARCHAR(255)
     DECLARE @EventMessageOld VARCHAR(8000)
     DECLARE @EventMessageNew VARCHAR(8000)

     SET @EventNumber = (SELECT eventnumber from deleted)
     SET @EventID = (SELECT eventid from deleted)
     SET @Computer = (SELECT A.eventcomputer from ESEventlogComputer as A, deleted as B WHERE A.id = B.eventcomputer)
     SET @EventMessageOld = (SELECT eventmessage from deleted)
     SET @EventMessageNew = (SELECT eventmessage from inserted)

     SET @Msg = ‘ESEventlogMain modified by ‘ + CONVERT(VARCHAR(20), USER_NAME(USER_ID())) + ‘ at ‘ + CONVERT(VARCHAR(20), GETDATE()) + ‘. Computer: ‘ + @Computer + ‘, Event ID: ‘ + CONVERT(VARCHAR(8), @EventID) + ‘, Event Number: ‘ + CONVERT(VARCHAR(16), @EventNumber) + ‘, EventMessage (old) =’ + @EventMessageOld + ‘, EventMessage (new) = ‘ + @EventMessageNew

     RAISERROR( 80000, 10, 1, @Msg)
END

This creates a trigger which will generate an event when the eventmessage column in the ESEventlogMain table is modified. You can remove the “IF UPDATE(eventmessage) …” clause (as well as the BEGIN & END statements) if you want to be notified of any changes to that table, this might however create some noise since acknowledging events will also perform an UPDATE on this table.

FYI: “deleted” and “inserted” are keywords that refer to either the old
record that was updated (=deleted) or the new data (=inserted).

As you can see from the screen shot above, the message text from a logoff event was renamed to “Trigger Test”. So now that the event is in the event log, we can set up a filter in EventSentry to alert us:

Events generated from triggers always have the event id 17061, so it’s a good idea to restrict the filter further using the “Content Filter” field. From now on, when the ESEventlogMain table is modified, we will get an entry in the event log as well as an email.

Read more