/*
 * SOXRef TI-208316
 * Create Sox table for filtering the SOX-trace
 *
 */
use master
 
Create table dbo.T_SoxFilterObjects (
IdNr int not null identity(1,1) primary key,
ColumnId int not null , -- See BOL sp_trace_setfilter \ @column_id 
ComparisonOperator int not null, -- See BOL sp_trace_setfilter \ @comparison_operator 
ComparisonValue NVarchar(128) not null,  
ActiveYN bit not null default 1,
Remark varchar(256) not null, 
UserCrea varchar(30) not null default suser_sname(),
TsCrea datetime not null default getdate(),
UserUpdate varchar(30) not null default suser_sname(),
TsUpdate datetime not null default getdate()
) 
go
ALTER TABLE dbo.T_SoxFilterObjects 
  ADD CONSTRAINT IX_T_SoxFilterObjects UNIQUE NONCLUSTERED 
     ComparisonValue )
go
Create trigger tr_SoxFilterObjects_Upd on
  dbo.T_SoxFilterObjects
for UPDATE
AS 
BEGIN
 SET NOCOUNT ON 
 
 IF TRIGGER_NESTLEVEL
  (Object_Id('tr_SoxExcludedObjects_Upd')) > 1  RETURN
 
 UPDATE O
  set UserUpdate = suser_sname(),
            TsUpdate = getdate()
 
  from dbo.T_SoxFilterObjects O
  Inner join Inserted I
            On O.IdNr = I.IdNr
END
go
-- 10 = ApplicationName
Insert into dbo.T_SoxFilterObjects ( ColumnId,
  ComparisonOperator, ComparisonValue, Remark ) values ( 10, 7, N'SQL
  Profiler', 'Default to exclude SQLProfiler application (SOX TI-208106)')
Insert into dbo.T_SoxFilterObjects ( ColumnId,
  ComparisonOperator, ComparisonValue, Remark ) values ( 10, 7, N'SQLAgent%',
  'SQLAgent jobs do not need to be monitored (SOX TI-208106)')
Insert into dbo.T_SoxFilterObjects ( ColumnId,
  ComparisonOperator, ComparisonValue, Remark ) values ( 10, 7,
  N'SQLServerScripting65982ABA-51E9-429E-BFE9-7C8D369C4B9D%', 'Scheduled
  scripting application (SOX TI-208106)')
Insert into dbo.T_SoxFilterObjects ( ColumnId,
  ComparisonOperator, ComparisonValue, Remark ) values ( 10, 7, N'SQLServerInfoCB5D555D-554C-44F1-B775-EF8B1FC9BF82%',
  'Scheduled SQLStatistics application (SOX TI-208106)')
/*
 * SOXRef TI-208312
 * Create Sox proc for determining trace file location
*
*/
use master
go
if exists (select * from dbo.sysobjects where id =
  object_id(N'[dbo].[sp_Proprietary_GetSOXTraceFileName]') and
  OBJECTPROPERTY(id, N'IsProcedure') = 1)
            drop procedure [dbo].[sp_Proprietary_GetSOXTraceFileName]
GO
 
Create Procedure sp_Proprietary_GetSOXTraceFileName
     @TraceFileName nvarchar(245) OUTPUT
as 
begin
set nocount on
/*
  -- test execution
  Declare @TraceFileName nvarchar(245) 
  execsp_DBA_GetSOXTraceFileName @TraceFileName OUTPUT
  print @TraceFileName
*/
 
SET @TraceFileName = ''
/*
 * Get SQLServer Errorlog path
*/
Create table #tmpRegValues ([Value] varchar(50), [Data] varchar(1000))
insert into #tmpRegValues 
exec master..xp_instance_regenumvalues
  N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
  
 
Select @TraceFileName = substring(Data, 3,datalength(Data)
  - charindex('\',reverse(Data)) - 2) + '\MyCompanySOX' + '_' +
  replace(@@servername,'\','_') + '_' +
  replace(replace(replace(convert(char(16),getdate(),121),'-',''),'
  ','_'),':','') + '_'
 from #tmpRegValues 
 where Data like '-e%'
 
DROP TABLE #tmpRegValues
 
end
/*
 * SOXRef TI-208315
 * Create Sox trace
 *
 */
use master
go
 
if (select OBJECTPROPERTY (
  object_id('sp_Proprietary_StartSOXTrace') , 'ExecIsStartup' )  as
  'IsStartup') = 1
  begin
     execsp_procoption  @ProcName = 'sp_Proprietary_StartSOXTrace' 
                ,  @OptionName = 'startup' 
                ,  @OptionValue =  'false' 
  end
 
go
 
if exists (select * from dbo.sysobjects where id =
  object_id(N'sp_Proprietary_StartSOXTrace') and OBJECTPROPERTY(id,
  N'IsProcedure') = 1)
  BEGIN
            drop procedure sp_Proprietary_StartSOXTrace
  END
GO
 
 
Create proc sp_Proprietary_StartSOXTrace
as
begin
set nocount on
if exists ( SELECT * 
            FROM :: fn_trace_getinfo(default)   A 
            where cast(A.[property] as varchar(15)) = '2' 
            and cast(A.[value] as varchar(300)) like '%\MYCompanySOX%'
            and exists ( select * 
                          FROM ::fn_trace_getinfo(default)  S
                          where S.[traceid] = A.[traceid]
                          and cast(S.[property] as varchar(15)) = '5'
                          and cast(S.[value] as varchar(300)) = '1' )
                        )
BEGIN
   raiserror ('Proprietary SOX traces already
  running.',10,10) with log
   return(-1)
END
 
declare @TraceID int
Set @TraceID = 0
 
-- Create a Queue
declare @rc int
declare @maxfilesize bigint
-- MB
set @maxfilesize = 50 -- Mb
 
Declare @TraceFileName nvarchar(245)
/*
 * Get SQLServer Errorlog path
*/
 
exec sp_Proprietary_GetSOXTraceFileName @TraceFileName
  OUTPUT
 
if datalength(@TraceFileName) > 230 
  begin
            Declare @lengte integer
            Set @lengte = datalength(@TraceFileName) 
            raiserror('DBA SOX tracefilename to long !!
  (%d) - (%s)',16,12, @lengte , @TraceFileName ) WITH LOG 
            goto error
 
  end
 
exec @rc = sp_trace_create @traceid = @TraceID OUTPUT, @options = 2
            , @tracefile = @TraceFileName
            , @maxfilesize = @maxfilesize
            , @stoptime = NULL
            --,  @filecount = 
                                                
if (@rc <> 0) goto error
-- Client side File and Table cannot be scripted
 
Declare @tmpTraceEventId table (EventId int not null)
Insert into @tmpTraceEventId values(10) -- 10 =  RPC:Completed
Insert into @tmpTraceEventId values(12) -- 12 =  SQL:BatchCompleeted
Insert into @tmpTraceEventId values(13) -- 13 =  SQL:BatchStarting
Insert into @tmpTraceEventId values(14) -- 14 = Login
Insert into @tmpTraceEventId values(15) -- 15 = Logout
Insert into @tmpTraceEventId values(17) -- 17 =  ExistingConnection
Insert into @tmpTraceEventId values(18) -- 18 = Audit Server Starts and Stops
Insert into @tmpTraceEventId values(20) -- 20 = Audit Login Failed
Insert into @tmpTraceEventId values(61) -- 61 = OLE DB  Errors
Insert into @tmpTraceEventId values(102) -- 102 = Audit  Statement GDR Event
Insert into @tmpTraceEventId values(103) -- 103 = Audit  Object GDR Event
Insert into @tmpTraceEventId values(104) -- 104 = Audit  AddLogin Event
Insert into @tmpTraceEventId values(105) -- 105 = Audit  Login GDR Event
Insert into @tmpTraceEventId values(106) -- 106 = Audit  Login Change Property Event
Insert into @tmpTraceEventId values(107) -- 107 = Audit  Login Change Password Event
Insert into @tmpTraceEventId values(108) -- 108 = Audit  Add Login to Server Role Event
Insert into @tmpTraceEventId values(109) -- 109 = Audit  Add DB User Event
Insert into @tmpTraceEventId values(110) -- 110 = Audit  Add Member to DB Role Event
Insert into @tmpTraceEventId values(111) -- 111 = Audit  Add Role Event
Insert into @tmpTraceEventId values(112) -- 112 = Audit  App Role Change Password Event
Insert into @tmpTraceEventId values(113) -- 113 = Audit  Statement Permission Event
Insert into @tmpTraceEventId values(114) -- 114 = Audit  Schema Object Access Event
Insert into @tmpTraceEventId values(115) -- 115 = Audit  Backup/Restore Event
Insert into @tmpTraceEventId values(116) -- 116 = Audit  DBCC Event
Insert into @tmpTraceEventId values(117) -- 117 = Audit  Change Audit Event
Insert into @tmpTraceEventId values(118) -- 118 = Audit  Object Derived Permission Event
 
-- Set the events
declare @on bit
Declare @eventid integer
set @on = 1
 
DECLARE csrTrc INSENSITIVE CURSOR 
  for Select EventId 
            from @tmpTraceEventId 
            order by EventId
            for read only
 
OPEN csrTrc
FETCH NEXT FROM csrTrc INTO @eventid
 
WHILE @@FETCH_STATUS = 0
BEGIN
     exec sp_trace_setevent @TraceID, @eventid, 1, @on  -- textdata
     execsp_trace_setevent @TraceID, @eventid, 3, @on  -- DbId
     execsp_trace_setevent @TraceID, @eventid, 6, @on  -- NTUsername
     execsp_trace_setevent @TraceID, @eventid, 7, @on  -- NTDomainname
     execsp_trace_setevent @TraceID, @eventid, 8, @on  -- ClientHostname
     execsp_trace_setevent @TraceID, @eventid, 10, @on  -- Applicationname
     execsp_trace_setevent @TraceID, @eventid, 11, @on  -- SQLSecurityLoginName
     execsp_trace_setevent @TraceID, @eventid, 12, @on  -- SPID
     execsp_trace_setevent @TraceID, @eventid, 13, @on  -- Duration
     execsp_trace_setevent @TraceID, @eventid, 14, @on  -- Starttime
     execsp_trace_setevent @TraceID, @eventid, 15, @on  -- Endtime
     -- R/W/C not for sox
     -- exec sp_trace_setevent @TraceID, @eventid, 16, @on  -- Reads
     -- exec sp_trace_setevent @TraceID, @eventid, 17, @on  -- Writes
     -- exec sp_trace_setevent @TraceID, @eventid, 18, @on  -- CPU
     execsp_trace_setevent @TraceID, @eventid, 19, @on  
     -- Represents the bitmap of permissions; used by Security Auditing
     execsp_trace_setevent @TraceID, @eventid, 23, @on  
     -- Success of the permissions usage attempt; used for auditing
     execsp_trace_setevent @TraceID, @eventid, 26, @on  -- Servername
     execsp_trace_setevent @TraceID, @eventid, 31, @on  -- Error 
     execsp_trace_setevent @TraceID, @eventid, 34, @on  -- ObjectName Name of object accessed
     execsp_trace_setevent @TraceID, @eventid, 35, @on  -- Name of the database specified 
     -- in the USE database statement
     execsp_trace_setevent @TraceID, @eventid, 40, @on  -- DatabaseUsername
     execsp_trace_setevent @TraceID, @eventid, 41, @on  -- LoginSID
     execsp_trace_setevent @TraceID, @eventid, 42, @on  -- TargetLoginName
     execsp_trace_setevent @TraceID, @eventid, 43, @on  -- TargetLoginSID

     -- extra from C2-audit
     execsp_trace_setevent @TraceID, @eventid, 21, @on  -- EventSubClass 
     execsp_trace_setevent @TraceID, @eventid, 27, @on  -- EventClass 
     execsp_trace_setevent @TraceID, @eventid, 37, @on  -- ObjectOwner 
     execsp_trace_setevent @TraceID, @eventid, 38, @on  -- TargetRoleName
     execsp_trace_setevent @TraceID, @eventid, 39, @on  -- TargetUserName 
     execsp_trace_setevent @TraceID, @eventid, 44, @on  -- ColumnPermissionsSet 

     -- Read next row
    FETCH NEXT FROM csrTrc INTO @eventid
END

-- Cursor afsluiten
CLOSE csrTrc
DEALLOCATE csrTrc

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
Declare @ColumnId int
Declare @ComparisonOperator int
Declare @ComparisonValue NVarchar(128)

DECLARE csrTrcXcl INSENSITIVE CURSOR 
  for Select  ColumnId, ComparisonOperator,
  ComparisonValue
            from dbo.T_SoxFilterObjects
            where ActiveYN = 1
            for read only
 
OPEN csrTrcXcl
FETCH NEXT FROM csrTrcXcl INTO @ColumnId,
  @ComparisonOperator, @ComparisonValue
 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Only AND filters are implemented
     execsp_trace_setfilter @TraceID, @ColumnId,
  0, @ComparisonOperator, @ComparisonValue
            -- Read next row
            FETCH NEXT FROM csrTrcXcl INTO @ColumnId,
  @ComparisonOperator, @ComparisonValue
END
-- Cursor afsluiten
CLOSE csrTrcXcl
DEALLOCATE csrTrcXcl
 
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, @status = 1
 
-- display trace id for future references
-- select TraceID=@TraceID
print '-- '
Print '-- TraceID for this trace [' + cast(@TraceID as
  varchar(10)) + '] --'
Print '-- ************************' + REPLICATE('*',
  datalength(cast(@TraceID as varchar(10)))) + '* --'
 
-- show Traces info
SELECT * FROM :: fn_trace_getinfo(default) 
 
goto finish
 
error: 
select ErrorCode=@rc
return (-1)
 
finish: 
 
end
 
go
 
-- Set as startup proc  http://support.microsoft.com/default.aspx/kb/817178
exec sp_procoption  @ProcName = 
  'sp_Proprietary_StartSOXTrace' 
    ,  @OptionName = 'startup' 
    ,  @OptionValue =  'true' 
 
 
Print 'First Execution of sp_Proprietary_StartSOXTrace'
exec sp_Proprietary_StartSOXTrace
 
go