WordPress.com



This document contains all code used for demonstrating the auditing operation using Service Broker’s External Activator Service.

The first section contains the SQL script used to create the objects in the database, the second section contains the External Activator Service’s configuration file and the third section contains the Auditing Application’s code.

1. Database objects creation script

USE [master]

GO

-- 1. Databases

-- Database which contains the table to be audited

CREATE DATABASE OLTP_DB

GO

-- Database which contains the table which stores the audit information

CREATE DATABASE AUDIT_DB

GO

-- 2. Tables

-- Create table to be audited

USE OLTP_DB

GO

CREATE TABLE INVOICES (ID INT PRIMARY KEY CLUSTERED, AMOUNT MONEY NOT NULL)

GO

-- Create tables to store the audit info

USE AUDIT_DB

GO

CREATE TABLE INVOICES_AUDIT

(

ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

AUDIT_DATE DATETIME NOT NULL,

AUDIT_USER SYSNAME NOT NULL,

AUDIT_TYPE CHAR(1) NOT NULL CHECK (AUDIT_TYPE IN ('I', 'U', 'D')), -- For each type of change

INVOICE_ID INT NOT NULL

)

CREATE TABLE INVOICES_AUDIT_DETAILS

(

ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

INVOICES_AUDIT_ID INT NOT NULL REFERENCES INVOICES_AUDIT(ID),

AUDITED_FIELD SYSNAME NOT NULL, -- For storing the column than was audited

VALUE_BEFORE NVARCHAR(MAX), -- For storing the value in the column before the change

VALUE_AFTER NVARCHAR(MAX) -- For storing the value in the column after the change

)

-- 3. Service Broker Objects

USE OLTP_DB

GO

-- Turn on Service Broker

-- IMPORTANT - Open transactions in the database will be rolled back

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE is_broker_enabled = 0)

ALTER DATABASE OLTP_DB SET NEW_BROKER WITH ROLLBACK IMMEDIATE

GO

-- Create Message Type of the Audit Message

CREATE MESSAGE TYPE AUDIT

VALIDATION = WELL_FORMED_XML

GO

-- Create Message Type of the Audit Message's Response

CREATE MESSAGE TYPE AUDIT_RESPONSE

VALIDATION = NONE

GO

-- Create Contract for the Conversation

CREATE CONTRACT AUDIT_CONTRACT

(AUDIT SENT BY INITIATOR,

AUDIT_RESPONSE SENT BY TARGET )

GO

-- Stored Procedure for ending the Conversation

CREATE PROCEDURE USP_END_AUDIT_CONVERSATION

AS

BEGIN

SET NOCOUNT ON

DECLARE @handle UNIQUEIDENTIFIER

WAITFOR (RECEIVE TOP (1)

@handle = CONVERSATION_HANDLE

FROM AUDIT_RESPONSES -- Queue which stores the responses

)

,TIMEOUT 10000; -- 10 seconds

IF @handle IS NULL

RETURN -- In case of a Timeout

END CONVERSATION @handle;

END

GO

-- Queue to store the Audit Messages

CREATE QUEUE AUDIT_MESSAGES

WITH STATUS = ON,

RETENTION = OFF

ON [PRIMARY]

GO

-- Queue to store Responses

CREATE QUEUE AUDIT_RESPONSES

WITH

STATUS = ON,

RETENTION = OFF,

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = USP_END_AUDIT_CONVERSATION,

MAX_QUEUE_READERS = 10, -- Up to 10 instances of USP_END_AUDIT_CONVERSATION can be kicked off

-- depending on the load (Internal Activation)

EXECUTE AS SELF) -- The stored procedure will be executed as the current user

ON [PRIMARY]

GO

-- Initiator Service

CREATE SERVICE INITIATOR_SERVICE

ON QUEUE AUDIT_RESPONSES

([AUDIT_CONTRACT]) -- Message Contract

GO

-- Target Service

CREATE SERVICE TARGET_SERVICE

ON QUEUE AUDIT_MESSAGES

([AUDIT_CONTRACT]) -- Message Contract

GO

-- Queue for the External Activator Service to monitor

-- When messages arrive in this Queue, the External Activator Service knows there is work to be done

CREATE QUEUE EXTERNAL_ACTIVATION

WITH

STATUS = ON,

RETENTION = OFF

ON [PRIMARY]

GO

-- Service for External Activation

CREATE SERVICE EXTERNAL_ACTIVATION_SERVICE

ON QUEUE EXTERNAL_ACTIVATION

([])

-- System-defined contract for Event Notifications

GO

-- External Activation Event

CREATE EVENT NOTIFICATION AUDIT_EVENT

ON QUEUE AUDIT_MESSAGES -- When messages arrive in this Queue, the Event will be raised

FOR QUEUE_ACTIVATION -- The type of Event

TO SERVICE 'EXTERNAL_ACTIVATION_SERVICE' , 'current database'

GO

-- Stored Procedure which will be used to send the Audit Message to the Queue

-- Error handling has been avoided to keep the sample code brief

CREATE PROCEDURE USP_SEND_AUDIT_MESSAGE(@data XML)

AS

BEGIN

DECLARE @handle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @handle

FROM SERVICE INITIATOR_SERVICE

TO SERVICE 'TARGET_SERVICE'

ON CONTRACT AUDIT_CONTRACT

WITH ENCRYPTION = OFF; -- Messages are not encrypted

SEND ON CONVERSATION @handle

MESSAGE TYPE AUDIT

(@data)

END

GO

-- Trigger to build the audit message using the modified data

CREATE TRIGGER [dbo].[TG_INVOICES]

ON [dbo].[INVOICES]

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

SET NOCOUNT ON

DECLARE @data XML, @table sysname

-- The table name is kept static to avoid any performance overhead associated with reading from

-- the metadata, however if overhead is not a concern, this can be done by reading from sys.triggers

SELECT @table = N'INVOICES'

-- Build audit message

SET @data =

'

'+ @table + '

' + SUSER_NAME() + '

' + CAST(GETDATE() AS VARCHAR) + '

' + ISNULL((SELECT * FROM INSERTED FOR XML RAW), '') + '

' + ISNULL((SELECT * FROM DELETED FOR XML RAW), '') + '

';

EXEC USP_SEND_AUDIT_MESSAGE @data

END

GO

2. External Activator Service’s Configuration File

External Activation Service

server=AJIT-LAPTOP;database=OLTP_DB;Application Name=External Activator;Integrated Security=true;

AJIT-LAPTOP

OLTP_DB

dbo

AUDIT_MESSAGES

c:\audit\AuditApp.exe

c:\audit

All Levels

All Modules

All Entities

Verbose

3. Auditing Application

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports System.Diagnostics

Imports System.IO

Imports System.Security.Cryptography

Imports System.Text

Imports System.Xml

Class Audit

'''

''' This sample code makes use of hardcoded values here. For a production system, these values must be passed in as command line arguments.

''' The values have been hardcoded to enable the reader to get the sample application configured quickly.

'''

Private Shared oltp_server = "AJIT-LAPTOP" 'Remember to change the value here (connection will be made using Integrated Security)

Private Shared oltp_database = "OLTP_DB"

Private Shared audit_server = "AJIT-LAPTOP" 'Remember to change the value here (connection will be made using Integrated Security)

Private Shared audit_database = "AUDIT_DB"

Private Shared message_queue_schema = "dbo"

Private Shared message_queue_name = "AUDIT_MESSAGES"

'''

''' Application name used when connecting to Sql Server.

'''

Private Const ApplicationName As String = "AuditApp"

'''

''' Defines how many milliseconds the RECEIVE statement should wait for messages.

'''

Private Const WaitforTimeout As Integer = 5000

'''

''' Predefined Sql Server message type name for end dialog messages.

'''

Private Const EndDialogMessageType As String = ""

'''

''' Predefined Sql Server message type name for conversation error messages.

'''

Private Const ErrorMessageType As String = ""

'''

''' Wraps the given Sql Server sysname in brackets and escapes any closing brackets already present in the name.

'''

Private Shared Function BracketizeName(sysname As String) As String

Return String.Format("[{0}]", sysname.Replace("]", "]]"))

End Function

'''

''' Services request messages by doing all the necessary computation.

'''

Private Shared Function ProcessMessage(payload As XmlDocument) As String

'Note that the code here has been written to only process the INVOICES table.

'For a production application, a generic processing function should be written

'All operations should ideally be done as part of a single transaction

'The logic caters to 1 record being changed at a time. If multiple records are created, altered or deleted in batches,

'then the logic should be changed to have an enumerator for records within the INSERTED and DELETED nodes

If payload.Item("AUDIT_MESSAGE").GetElementsByTagName("TABLE").Item(0).InnerText() = "INVOICES" Then

Dim audit_user As String = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("AUDIT_USER").Item(0).InnerText()

Dim audit_datetime As String = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("AUDIT_DATETIME").Item(0).InnerText()

'Decipher the type of operation based on the presence/absence of data from the INSERTED and DELETED tables

Dim audit_operation As String = String.Empty

If payload.Item("AUDIT_MESSAGE").GetElementsByTagName("DELETED").Item(0).HasChildNodes() = False Then

audit_operation = "I" 'Insert

ElseIf payload.Item("AUDIT_MESSAGE").GetElementsByTagName("INSERTED").Item(0).HasChildNodes() = False Then

audit_operation = "D" 'Delete

Else

audit_operation = "U" 'Update

End If

Dim invoice_id As String = String.Empty

Dim audited_field As String = String.Empty

Dim value_before As String = String.Empty

Dim value_after As String = String.Empty

If audit_operation = "I" Or audit_operation = "U" Then

invoice_id = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("INSERTED").Item(0).ChildNodes(0).Attributes("ID").Value()

Else

invoice_id = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("DELETED").Item(0).ChildNodes(0).Attributes("ID").Value()

End If

'Build connection string for the audit database

Dim csb As New SqlConnectionStringBuilder()

csb.ApplicationName = ApplicationName

csb.DataSource = audit_server

csb.InitialCatalog = audit_database

csb.IntegratedSecurity = True

'Create the audit records

Dim audit_id As String = String.Empty

Using sqlconn As New SqlConnection(csb.ToString())

sqlconn.Open()

Using sql As SqlCommand = sqlconn.CreateCommand()

mandText = "INSERT [INVOICES_AUDIT]([AUDIT_DATE],[AUDIT_USER],[AUDIT_TYPE],[INVOICE_ID]) VALUES (@date, @user, @type, @invoice); SELECT SCOPE_IDENTITY();"

sql.Parameters.Add(New SqlParameter("@date", audit_datetime))

sql.Parameters.Add(New SqlParameter("@user", audit_user))

sql.Parameters.Add(New SqlParameter("@type", audit_operation))

sql.Parameters.Add(New SqlParameter("@invoice", invoice_id))

audit_id = sql.ExecuteScalar()

End Using

'Build list of columns (For a production system, it is better to query the metadata to cope with changing table structures)

Dim list(1) As String

list(0) = "ID"

list(1) = "AMOUNT"

For Each column In list

audited_field = column

If audit_operation = "I" Then

value_before = ""

value_after = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("INSERTED").Item(0).ChildNodes(0).Attributes(column).Value()

ElseIf audit_operation = "D" Then

value_before = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("DELETED").Item(0).ChildNodes(0).Attributes(column).Value()

value_after = ""

Else 'Update

value_before = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("DELETED").Item(0).ChildNodes(0).Attributes(column).Value()

value_after = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("INSERTED").Item(0).ChildNodes(0).Attributes(column).Value()

End If

Using sql As SqlCommand = sqlconn.CreateCommand()

mandText = "INSERT [INVOICES_AUDIT_DETAILS]([INVOICES_AUDIT_ID],[AUDITED_FIELD],[VALUE_BEFORE],[VALUE_AFTER]) VALUES (@id, @field, @value_before, @value_after)"

sql.Parameters.Add(New SqlParameter("@id", audit_id))

sql.Parameters.Add(New SqlParameter("@field", audited_field))

sql.Parameters.Add(New SqlParameter("@value_before", value_before))

sql.Parameters.Add(New SqlParameter("@value_after", value_after))

sql.ExecuteNonQuery()

End Using

Next

End Using

End If

'Send response back so that conversation can be closed

Return "Audit Message processed!"

End Function

Public Shared Sub Main()

Dim csb As New SqlConnectionStringBuilder()

csb.ApplicationName = ApplicationName

csb.DataSource = oltp_server

csb.InitialCatalog = oltp_database

csb.IntegratedSecurity = True

' MARS is necessary to be able to send response messages.

csb.MultipleActiveResultSets = True

Try

Using sqlConnection As New SqlConnection(csb.ToString())

sqlConnection.Open()

Dim messageReceived As Boolean

Do

messageReceived = False

Using transaction As SqlTransaction = sqlConnection.BeginTransaction()

Dim receiveCommand As SqlCommand = sqlConnection.CreateCommand()

receiveCommand.Transaction = transaction

' Even if message_body is always XML, don't cast to XML inside the RECEIVE statement as this

' may cause issues with activation. Convert/cast to XML after the RECEIVE is done.

'

mandText = String.Format("WAITFOR (RECEIVE TOP(1) conversation_handle, message_type_name, message_body FROM {0}.{1}), TIMEOUT {2}", BracketizeName(message_queue_schema), BracketizeName(message_queue_name), WaitforTimeout)

Using reader As SqlDataReader = receiveCommand.ExecuteReader()

If reader.Read() Then

messageReceived = True

Dim conversationHandle As Guid = reader.GetGuid(0)

Dim messageTypeName As String = reader.GetString(1)

Dim messageBody As SqlBinary = reader.GetSqlBinary(2)

If messageTypeName = EndDialogMessageType OrElse messageTypeName = ErrorMessageType Then

If messageTypeName = ErrorMessageType Then

'Handle the error message

End If

Dim endConversationCommand As SqlCommand = sqlConnection.CreateCommand()

endConversationCommand.Transaction = transaction

mandText = "END CONVERSATION @handle"

endConversationCommand.Parameters.Add(New SqlParameter("@handle", conversationHandle))

endConversationCommand.ExecuteNonQuery()

Else

Dim payload As New XmlDocument()

payload.LoadXml(System.Text.Encoding.Unicode.GetString(messageBody.Value).Substring(1)) 'First char is eliminated as it is invalid

Dim responsePayload As String = ProcessMessage(payload)

Dim sendCommand As SqlCommand = sqlConnection.CreateCommand()

sendCommand.Transaction = transaction

'Note that the message type is hardcoded here. This should not be done for a production application.

mandText = "SEND ON CONVERSATION @handle MESSAGE TYPE [AUDIT_RESPONSE] (@body)"

sendCommand.Parameters.Add(New SqlParameter("@handle", conversationHandle))

sendCommand.Parameters.Add(New SqlParameter("@body", responsePayload))

sendCommand.ExecuteNonQuery()

End If

End If

End Using

mit()

End Using

Loop While messageReceived

End Using

Catch e As Exception

' Whenever anything fails, make sure the failure is visible in the event log.

'

If Not EventLog.SourceExists(ApplicationName) Then

EventLog.CreateEventSource(ApplicationName, ApplicationName)

End If

EventLog.WriteEntry(ApplicationName, e.ToString(), EventLogEntryType.[Error])

Throw

End Try

End Sub

End Class

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download