ISR Computing -

How to enable SQL 2005 Database Auditing

This article provides you with the steps to enable database auditing on Microsoft SQL 2005.

Configuration process for database auditing on the database-side (MS SQL) consists of the following steps:

1. Create directory for the trace files (ex: C:\DbTrace). Trace files contain following audit data:
   
   • The data that was modified
   • Start and End times of the transaction
   • Name of Application used
   • Name of Database affected
   • Host where transaction originated
   • Login name of the user
2. Create stored procedure to start trace that will capture auditing data and copy it to the trace file.
3. Configure SQL server to execute previously created stored procedure on startup.
4. Start database auditing by running previously created stored procedure.

   EXEC master.dbo.DbTraceUserActivity;
     

5. Verify trace status output by running:

   select * from ::fn_trace_getinfo (default)

   Please remember {traceid} value from the above command. It will be needed to stop the trace.
6. In case you need to stop the trace for any reason use the below code. Remember that {traceid} is set to 2.
   

   declare @TraceID int
   set @TraceID = 2
   exec sp_trace_setstatus @TraceID, 0
   exec sp_trace_setstatus @TraceID, 2
     

7. In case you need to remove/re-create the above stored procedure:
   

   USE master
   GO
   DROP PROCEDURE dbo.DbTraceUserActivity;
   GO
     

Create stored procedure to capture auditing data

/****************************************************/
use master
go

drop procedure dbo.DbTraceUserActivity;
go

EXEC xp_cmdshell 'DEL C:\DbTrace\DbTraceUserActivity*', NO_OUTPUT
go

CREATE procedure DbTraceUserActivity 
as

set nocount on

-- Create a Queue
declare @rc int
declare @maxfilesize bigint
declare @j int
declare @i int
declare @Events varchar(400)
declare @TmpCols varchar(300)
declare @TraceFileName nvarchar(245)
set @maxfilesize = 25

DECLARE @TraceID int
DECLARE @On bit

DECLARE @Event int, @Col int, @OnOff bit
DECLARE @ColStr varchar(300)

SET @Events='117,14,105,104,108,107,106,113,46,118,47,18,109,110,111,112,'
SET @TmpCols='1,3,8,10,11,12,14,15,21,22,23,28,29,34,35,38,39,40,42,'
SET @TraceFileName = 'C:\DbTrace\DbTraceUserActivity'


exec @rc = sp_trace_create @TraceID OUTPUT, 2, @TraceFileName, @maxfilesize, NULL
if (@rc != 0) goto error

IF @Events IS NOT NULL BEGIN
SET @OnOff=1
SET @i=CHARINDEX(',',@Events)
WHILE @i<>0 BEGIN
SET @Event=CAST(LEFT(@Events,@i-1) AS int)
SET @ColStr=@TmpCols
SET @j=CHARINDEX(',',@ColStr)
WHILE @j<>0 BEGIN
SET @Col=CAST(LEFT(@ColStr,@j-1) AS int)
EXEC @rc=sp_trace_setevent @TraceID=@TraceID,@EventId=@Event,@ColumnId=@Col,@on=@OnOff
SET @ColStr=SUBSTRING(@ColStr,@j+1,300)
SET @j=CHARINDEX(',',@ColStr)
END
SET @Events=SUBSTRING(@Events,@i+1,300)
SET @i=CHARINDEX(',',@Events)
END
END

exec sp_trace_setstatus @TraceID, 0

EXEC @rc=sp_trace_setfilter @TraceId=@TraceID, @columnid=10, @logical_operator=0, @comparison_operator=7, @value=N'Monitoring Software'
if (@rc != 0) goto error
EXEC @rc=sp_trace_setfilter @TraceId=@TraceID, @columnid=36, @logical_operator=1, @comparison_operator=6, @value=N'%SOMEOTHERDB%'
if (@rc != 0) goto error
EXEC @rc=sp_trace_setfilter @TraceId=@TraceID, @columnid=36, @logical_operator=1, @comparison_operator=6, @value=N'%master%'
if (@rc != 0) goto error

exec sp_trace_setstatus @TraceID, 1

goto finish

error: 
select ErrorCode=@rc

finish:
GO

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

Configure SQL server to execute previously created stored procedure on startup

USE MASTER
GO
EXEC SP_PROCOPTION DbTraceUserActivity, 'STARTUP', 'ON'
GO