Audit trail for web application

[Parag]

Hello,
I have been assigned the task to design the audit trail for the ASP.NET web
application. I don't know what the best practices for such audit trails are.
Our application one dedicated user name and password to perform the database
operations. I need to capture all the operations which are performed on the
database. Also I need to able to capture the operations which directly
performed on the backend directly using the tools like enterprise manager,
query analyzer, etc. And also the data for the action should be captured in
some set of tables, but not in the form of BLOB, so that if the admin want
to know what are the various things the user did during the particular
session then that should be reproduced in the form of report. Does any
anybody have any idea of how to do such task? Any tips will be helpful.

I am trying one approach with the help of triggers. But the problem with
this approach is that I am not able to store all the information need to
reproduce the same output if I try to generate report for the particular
session.

Thanks and Regards,
Parag Kulkarni,
India
Email pa************@persistent.co.in

[bradley]

This is more of a SQL Server question than an ASP.NET question. The problem
with implenting auditing at the application level or using table triggers is
that it is very programming intensive and difficult to cover every point of
database entry. Every time the data model changes, you would need to go back
and revise the audit programming. Go to MSDN and read up on SQL Server
Profiler. It can trap various events and output the log to a SQL Server
table. There are also 3rd party database auditing tools that should do
exactly what you need.

"Parag" <pa************@persistent.co.in> wrote in message
news:OR**************@TK2MSFTNGP15.phx.gbl...

Hello,
I have been assigned the task to design the audit trail for the ASP.NET web application. I don't know what the best practices for such audit trails are. Our application one dedicated user name and password to perform the database operations. I need to capture all the operations which are performed on the database. Also I need to able to capture the operations which directly
performed on the backend directly using the tools like enterprise manager,
query analyzer, etc. And also the data for the action should be captured in some set of tables, but not in the form of BLOB, so that if the admin want
to know what are the various things the user did during the particular
session then that should be reproduced in the form of report. Does any
anybody have any idea of how to do such task? Any tips will be helpful.

I am trying one approach with the help of triggers. But the problem with
this approach is that I am not able to store all the information need to
reproduce the same output if I try to generate report for the particular
session.

Thanks and Regards,
Parag Kulkarni,
India
Email pa************@persistent.co.in

[Parag]

Hi,
Thanks for the reply. Can you please tell me what are the best practices
that are followed in web application in .NET to implement the Audit trail ?
Is there any other way to implement it? It will be of greate help to me if
you could just point out what are the best practices to implement audit
trails for web applications.

Regards,
Parag

"bradley" <so*****@microsoft.com> wrote in message
news:uK**************@TK2MSFTNGP15.phx.gbl...

This is more of a SQL Server question than an ASP.NET question. The
problem
with implenting auditing at the application level or using table triggers
is
that it is very programming intensive and difficult to cover every point
of
database entry. Every time the data model changes, you would need to go
back
and revise the audit programming. Go to MSDN and read up on SQL Server
Profiler. It can trap various events and output the log to a SQL Server
table. There are also 3rd party database auditing tools that should do
exactly what you need.

"Parag" <pa************@persistent.co.in> wrote in message
news:OR**************@TK2MSFTNGP15.phx.gbl...

Hello,
I have been assigned the task to design the audit trail for the ASP.NET

web

application. I don't know what the best practices for such audit trails

are.

Our application one dedicated user name and password to perform the

database

operations. I need to capture all the operations which are performed on

the

database. Also I need to able to capture the operations which directly
performed on the backend directly using the tools like enterprise
manager,
query analyzer, etc. And also the data for the action should be captured

in

some set of tables, but not in the form of BLOB, so that if the admin
want
to know what are the various things the user did during the particular
session then that should be reproduced in the form of report. Does any
anybody have any idea of how to do such task? Any tips will be helpful.

I am trying one approach with the help of triggers. But the problem with
this approach is that I am not able to store all the information need to
reproduce the same output if I try to generate report for the particular
session.

Thanks and Regards,
Parag Kulkarni,
India
Email pa************@persistent.co.in

[bradley]

Auditing SQL Server Activity:
http://msdn.microsoft.com/library/de...urity_2ard.asp

Monitoring with SQL Profiler:
http://msdn.microsoft.com/library/de..._perf_86ib.asp
"Parag" <pa************@persistent.co.in> wrote in message
news:OQ**************@TK2MSFTNGP14.phx.gbl...

Hi,
Thanks for the reply. Can you please tell me what are the best practices that are followed in web application in .NET to implement the Audit trail ? Is there any other way to implement it? It will be of greate help to me if
you could just point out what are the best practices to implement audit
trails for web applications.

Regards,
Parag

"bradley" <so*****@microsoft.com> wrote in message
news:uK**************@TK2MSFTNGP15.phx.gbl...

This is more of a SQL Server question than an ASP.NET question. The
problem
with implenting auditing at the application level or using table triggers is
that it is very programming intensive and difficult to cover every point
of
database entry. Every time the data model changes, you would need to go
back
and revise the audit programming. Go to MSDN and read up on SQL Server
Profiler. It can trap various events and output the log to a SQL Server
table. There are also 3rd party database auditing tools that should do
exactly what you need.

"Parag" <pa************@persistent.co.in> wrote in message
news:OR**************@TK2MSFTNGP15.phx.gbl...

Hello,
I have been assigned the task to design the audit trail for the ASP.NET

web

application. I don't know what the best practices for such audit trails

are.

Our application one dedicated user name and password to perform the

database

operations. I need to capture all the operations which are performed on

the

database. Also I need to able to capture the operations which directly
performed on the backend directly using the tools like enterprise
manager,
query analyzer, etc. And also the data for the action should be captured

in

some set of tables, but not in the form of BLOB, so that if the admin
want
to know what are the various things the user did during the particular
session then that should be reproduced in the form of report. Does any
anybody have any idea of how to do such task? Any tips will be helpful.

I am trying one approach with the help of triggers. But the problem

with this approach is that I am not able to store all the information need to reproduce the same output if I try to generate report for the particular session.

Thanks and Regards,
Parag Kulkarni,
India
Email pa************@persistent.co.in

[Steve Troxell]

Parag wrote:

Hi,
Thanks for the reply. Can you please tell me what are the best practices
that are followed in web application in .NET to implement the Audit trail ?
Is there any other way to implement it? It will be of greate help to me if
you could just point out what are the best practices to implement audit
trails for web applications.

Regards,
Parag

Triggers are the only thing you have available which meets your
requirement to trap everything including backend changes made through
Enterprise Manager or Query Analyzer.

OmniAudit will do it all for every table in your database in a couple of
minutes. If your schema changes, press one button and audit triggers are
synchronized again.

http://www.krell-software.com/omniaudit

Contact me at in**@krell-software.com about an add-on which lets you
track application users independently when a single SQL Server login is
shared.

Steve Troxell
http://www.krell-software.com

[Steve Troxell]

Parag wrote:

I am trying one approach with the help of triggers. But the problem with
this approach is that I am not able to store all the information need to
reproduce the same output if I try to generate report for the particular
session.

What information were you not able to get with triggers?
OmniAudit will build audit triggers for every table in your database in
a couple of minutes. If your schema changes, press one button and audit
triggers are synchronized again.

http://www.krell-software.com/omniaudit

Contact me at in**@krell-software.com about an add-on which lets you
track application users independently when a single SQL Server login is
shared, or for any other questions about meeting your needs.

Steve Troxell
http://www.krell-software.com

[Parag]

Hi,
Thanks for the links . But My problem will not be solved using any of
them. Here is my problem statement in short :
1. I have my application data distributed across different tables in the
database in the normalized form.
2. When the user perform any CRUD( 1 unit operation) then the data that is
operated is obtained by performing the joins of multiple tables.
3. Every module of my application is programmed to perform joins as per its
needs directly on the tables.
4. I have read of "INSTEAD OF triggers" in SQL server 2000. They seem to be
useful but they will require huge code restructuring as I will have to
create a partitioned view for its use and point every thing in each module
to that view rather than the database tables directly.
So is there any other way to perform audit trail in such situation ?
Looking forward for the response.
Thanks ,
Parag Kulkarni

"bradley" <so*****@microsoft.com> wrote in message
news:eV**************@TK2MSFTNGP09.phx.gbl...

Auditing SQL Server Activity:
http://msdn.microsoft.com/library/de...urity_2ard.asp

Monitoring with SQL Profiler:
http://msdn.microsoft.com/library/de..._perf_86ib.asp
"Parag" <pa************@persistent.co.in> wrote in message
news:OQ**************@TK2MSFTNGP14.phx.gbl...

Hi,
Thanks for the reply. Can you please tell me what are the best

practices

that are followed in web application in .NET to implement the Audit trail

?

Is there any other way to implement it? It will be of greate help to me
if
you could just point out what are the best practices to implement audit
trails for web applications.

Regards,
Parag

"bradley" <so*****@microsoft.com> wrote in message
news:uK**************@TK2MSFTNGP15.phx.gbl...

> This is more of a SQL Server question than an ASP.NET question. The
> problem
> with implenting auditing at the application level or using table triggers > is
> that it is very programming intensive and difficult to cover every
> point
> of
> database entry. Every time the data model changes, you would need to go
> back
> and revise the audit programming. Go to MSDN and read up on SQL Server
> Profiler. It can trap various events and output the log to a SQL Server
> table. There are also 3rd party database auditing tools that should do
> exactly what you need.
>
> "Parag" <pa************@persistent.co.in> wrote in message
> news:OR**************@TK2MSFTNGP15.phx.gbl...
>> Hello,
>> I have been assigned the task to design the audit trail for the
>> ASP.NET
> web
>> application. I don't know what the best practices for such audit
>> trails
> are.
>> Our application one dedicated user name and password to perform the
> database
>> operations. I need to capture all the operations which are performed
>> on
> the
>> database. Also I need to able to capture the operations which directly
>> performed on the backend directly using the tools like enterprise
>> manager,
>> query analyzer, etc. And also the data for the action should be captured > in
>> some set of tables, but not in the form of BLOB, so that if the admin
>> want
>> to know what are the various things the user did during the particular
>> session then that should be reproduced in the form of report. Does any
>> anybody have any idea of how to do such task? Any tips will be
>> helpful.
>>
>> I am trying one approach with the help of triggers. But the problem with >> this approach is that I am not able to store all the information need to >> reproduce the same output if I try to generate report for the particular >> session.
>>
>>
>>
>> Thanks and Regards,
>> Parag Kulkarni,
>> India
>> Email pa************@persistent.co.in
>>
>>
>>
>
>