Auditing in SQL Server 2005 - Need Help

We are currently looking at providing comprehensive audit support for our
corporate treasury application Vruksha. This product is being ported from SQ
L
Server 2000/VB6 to SQL Server 2005/VB.Net. Our existing audit feature in the
SQL Server 2000/VB application is limited to maintaining copies of all
records that are edited and deleted in tables that have the same structure a
s
the original data tables with additional fields to capture modifier details.
With this approach however, we have had issues with providing contextual and
meaningful audit information to the user. In the ported version, we wish to
provide a more complete audit infrastructure, with the following objectives:
1. Generate audit records for all defined events, whether they occur through
the application, SQL Server client or otherwise.
2. Capture sufficient information in audit records to establish what events
occurred, the sources of the events, and the outcomes of the events.
3. Provide the capability to include additional, more detailed information
in the audit records for audit events identified by type, location, or
subject.
4. Provide the capability to centrally manage the content of audit records
generated by individual components throughout the system.
5. Provide time stamps for use in audit record generation.
6. Protect audit information and audit tools from unauthorized access,
modification, and deletion.
7. Provide an audit reduction and report generation capability.
8. Provide the capability to automatically process audit records for events
of interest based upon selectable, event criteria.
9. Create, maintain, and protect from modification or unauthorized access or
destruction of an audit trail of accesses to the objects it protects.
10. Protect audit data so that read access to it is limited to those who are
authorized.
11. Record the following types of events: use of identification and
authentication mechanisms, introduction of objects into a user's address
space (e.g., file open, program initiation), deletion of objects, and action
s
taken by computer operators and system administrators and/or system security
officers and other security relevant events.
12. Audit any override of human-readable output markings.
13. Identify the date and time of the event, user, type of event, and
success or failure of the event. For identification and authentication
events, the origin of request (e.g., terminal ID) shall be included in the
audit record. For events that introduce an object into a user's address spac
e
and for object deletion events, the audit record shall include the name of
the object and the object's label.
14. The system administrator shall be able to selectively audit the actions
of any one or more users based on individual identity and/or object label.
15. Raise alarms whenever a threshold is reached with respect to an auditing
system resource (disk space in audit log volume) or when auditing has been
turned off (either inadvertently or deliberately).
16. Provide details of databases/schemas/tables accessed and modified by the
users and the administrator
17. Provide details of exceptional activity in certain columns (eg. Salary
column incremented by more than 20%)
With this background, we have the following questions:
1. Is it efficient to maintain auditing records in a separate audit table(s)
or maintain it as part of the same table? What has been the collective
experience of developers?
2. Is there a white paper on auditing in SQL Server 2005/.Net?
3. What is the native database support for auditing in SQL Server 2005?
Would they address our concerns?
4. What are the readily available .Net components for auditing that we may
use in this scenario?> 1. Is it efficient to maintain auditing records in a separate audit
> table(s)
> or maintain it as part of the same table? What has been the collective
> experience of developers?
You can create an Audit_Table to handle the changes of (Sales table)
for this particular table
create trigger tru_MyTable on Sales after update
as
if @.@.ROWCOUNT = 0
return
insert Audit_Table
select i.ID, d.MyColumn, i.MyColumn
from inserted i join deleted d on d.ID = i.Id
go

> 2. Is there a white paper on auditing in SQL Server 2005/.Net?
Start with BOL (Books On Line)

> 3. What is the native database support f
or auditing in SQL Server 2005? > Would they address our concerns?
Take look into the BOL
CREATE TRIGGER STOP_DDL_on_Table_and_PROC
ON ALL SERVER
FOR CREATE_DATABASE,ALTER_DATABASE,DROP_DATA
BASE
AS
PRINT 'You are not allowed to CREATE,ALTER and DROP any Databases'
ROLLBACK;
USE MASTER
GO
CREATE DATABASE TEST
GO
ALTER DATABASE DDLTRTEST MODIFY FILE
(
NAME = DDLTRTEST,
FILENAME='D:\DDLTRTEST.MDF')
GO
USE MASTER
GO
DROP DATABASE DDLTRTEST
GO

> 4. What are the readily available .Net components for auditing that we may
> use in this scenario?
Sorry, I don't know so much .NET
"Peri" <Peri@.discussions.microsoft.com> wrote in message
news:F2025A73-32AA-41B6-9FF0-2030B5B270CA@.microsoft.com...
> We are currently looking at providing comprehensive audit support for our
> corporate treasury application Vruksha. This product is being ported from
> SQL
> Server 2000/VB6 to SQL Server 2005/VB.Net. Our existing audit feature in
> the
> SQL Server 2000/VB application is limited to maintaining copies of all
> records that are edited and deleted in tables that have the same structure
> as
> the original data tables with additional fields to capture modifier
> details.
> With this approach however, we have had issues with providing contextual
> and
> meaningful audit information to the user. In the ported version, we wish
> to
> provide a more complete audit infrastructure, with the following
> objectives:
> 1. Generate audit records for all defined events, whether they occur
> through
> the application, SQL Server client or otherwise.
> 2. Capture sufficient information in audit records to establish what
> events
> occurred, the sources of the events, and the outcomes of the events.
> 3. Provide the capability to include additional, more detailed information
> in the audit records for audit events identified by type, location, or
> subject.
> 4. Provide the capability to centrally manage the content of audit records
> generated by individual components throughout the system.
> 5. Provide time stamps for use in audit record generation.
> 6. Protect audit information and audit tools from unauthorized access,
> modification, and deletion.
> 7. Provide an audit reduction and report generation capability.
> 8. Provide the capability to automatically process audit records for
> events
> of interest based upon selectable, event criteria.
> 9. Create, maintain, and protect from modification or unauthorized access
> or
> destruction of an audit trail of accesses to the objects it protects.
> 10. Protect audit data so that read access to it is limited to those who
> are
> authorized.
> 11. Record the following types of events: use of identification and
> authentication mechanisms, introduction of objects into a user's address
> space (e.g., file open, program initiation), deletion of objects, and
> actions
> taken by computer operators and system administrators and/or system
> security
> officers and other security relevant events.
> 12. Audit any override of human-readable output markings.
> 13. Identify the date and time of the event, user, type of event, and
> success or failure of the event. For identification and authentication
> events, the origin of request (e.g., terminal ID) shall be included in the
> audit record. For events that introduce an object into a user's address
> space
> and for object deletion events, the audit record shall include the name of
> the object and the object's label.
> 14. The system administrator shall be able to selectively audit the
> actions
> of any one or more users based on individual identity and/or object label.
> 15. Raise alarms whenever a threshold is reached with respect to an
> auditing
> system resource (disk space in audit log volume) or when auditing has been
> turned off (either inadvertently or deliberately).
> 16. Provide details of databases/schemas/tables accessed and modified by
> the
> users and the administrator
> 17. Provide details of exceptional activity in certain columns (eg. Salary
> column incremented by more than 20%)
> With this background, we have the following questions:
> 1. Is it efficient to maintain auditing records in a separate audit
> table(s)
> or maintain it as part of the same table? What has been the collective
> experience of developers?
> 2. Is there a white paper on auditing in SQL Server 2005/.Net?
> 3. What is the native database support for auditing in SQL Server 2005?
> Would they address our concerns?
> 4. What are the readily available .Net components for auditing that we may
> use in this scenario?
>