Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Auditing and Notification

By Jeffrey Yao,

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)

 

  1. 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)

  1. 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.)

  1. 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.

Total article views: 9122 | Views in the last 30 days: 11
 
Related Articles
FORUM

Event class in Trace

Event Class in Trace

FORUM

SQL Server 2005 auditing using event notifications

problem with event AUDIT_SCHEMA_OBJECT_ACCESS_EVENT - recursive behavior

FORUM

Create trace problem

Create trace problem

FORUM

PCI auditing/Tracing with RSA Envision.

PCI auditing/Tracing with RSA Envision.

FORUM

trace

trace

Tags
administration    
security    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones