SQL Server Auditing and Notification

Introduction

There are lots of articles about how to do auditing, but there are few discussions about how to use the auditing results in a real time environment, for example, an employee salary table has a delete operation during non-working hours, a responsible DBA should be notified for this unusual operation asap.

In this article, I propose a new security mechanism based on the auditing trace of SQL Server 2000. There are two distinct advantages to this security model:

1. Any monitored events can be reported to the concerned parties as soon as possible.
2. All responding actions can be centered in a same place.

 Security Mechanism Description

There are four major components in building this security model

1. An auditing trace is established to monitor any concerned events
2. A job is setup to export the trace file to a trace table every fixed period
3. An alert is created with notification functions set
4. A trigger on the trace table will fire out notifications (email, pager) to concerned people by raising a low severity error.

The process flow is as follows:

An auditing trace will record any concerned events to a trace file, and the job will from time to time import the trace file to a table, on which there is a trigger. The trigger will detect whether there is any specific events occurring to specific targets, if yes, raise error, which trigger the alert to be fired and the necessary notification will be issued as a result.  

Implementation details

1. Creating an auditing trace

a)      Start the profiler, create a trace that can monitor your interested events, as shown in Fig 1.

  Fig 1: Create a trace for your interested events

Also it is better to filter out any events generated by SQL Alert engine as shown in Fig 2.

Fig 2: Filter out events generated by SQL Agent

b)      Go to menu File -> Script trace -> SQL Server 2000, you will save an sql file. It is similar to the following script:

/****************************************************/

/* Created by: SQL Profiler                         */

/* Date: 02/28/2004  11:35:51 AM         */

/****************************************************/

create procedure usp_MyTrace as /* this line is added by myself to create a stored proc */

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL

-- note: change “InsertFileNameHere” to your local file with full path, e.g. “c:\test.trc”

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 6, @on

…………………………..

………………………….

error:

select ErrorCode=@rc

finish:

go

Open this SQL Profiler created file and add a line of code (create proc usp_MyTrace as ) in the beginning of the code, so you will get a stored procedure.

After the procedure is created, generate the stored procedure in master database if you want to use sp_procoption to make it run automatically every time SQL Server is started.

                Exec sp_procoption ‘usp_MyTrace’, ‘startup’, true  

  Otherwise, you can generate the stored proc in your own database and use a job to schedule this procedure execution.

        c)  Create a table with similar columns in the trace, plus an identity column (can be omitted, but I prefer to have it for clarity reason only)

Create table TraceTable

( RowID int identity primary key, TextData nvarchar(4096), Reads int, Writes int, StartTime datetime, LoginName sysname, NTUserName sysname)

2. Creating a job

The job contains only one sql statement

insert into TraceTale (textdata, Reads, Writes, StartTime, LoginName, NTUserName)

select left(textdata, 4096), Reads, Writes, StartTime, LoginName, NTUserName  from ::fn_trace_gettable('c:\test.trc', default)

3. Creating an alert

In my case, I created an alert with an error number 50005 and made the notification responding to this alert. The notification is to send email to an operator. (You can page the operator too if your system supports the function.)

4. Creating a trigger

In my case, I try to detect whether there is a read statement to a specific table

create trigger tr_Read_ on xx

for insert

as

begin

                if update (TextData)

                begin

                                if exists (select * from inserted

                                                   where TextData  like ‘%select%EmployeeSalary%’)

                                raiserror (50005, 10, 1)

                end

end

Summary

In this paper, a new auditing solution is proposed in an environment that quick response is critical. The solution design is based on a combined use of SQL Server’s various components, such as trace file, trigger, job and alerts. It has an advantage when used in monitoring multiple database environments so a DBA can have a centered place to monitor all those sensitive operations. It can be considered as a mimic implementation of “select” trigger.