locked
Database Access tracing/Auditing RRS feed

  • Question

  •  

    Hi all,
    I am new to this forum. For a project task, we need to trace any kind of user activity with the database, like, which user is accessing what piece of data, at what date/time from which system (IP Addr) and what he/she doing with that data (like INSERT/SELECT/UPDATE/DELETE). This is same as database auditing.
    My problem is, where to implement it, Client side or Server side? If client side then, we need to push all these data to server and if at Server side, we need to pull data. Which is economical, in terms of Traffic, speed, processing time etc? As server will be in some Intranet site, we need to assume thousands of users. So Server will be busy with normal Request/Grant work, plus need to keep this required Auditing history info also for each activity/transaction.
    Please help me in this regards. I am using SQL 2000 presently and shortly migrating to 2005 for this task.
    Friday, July 11, 2008 2:02 PM

Answers

  •   Correct me if I misunderstood your question, but you want to know where (server/client) it is recommended to generate the audit events, correct? If my assumption is correct, auditing should be done on the server, otherwise the auditing information will be inaccurate; if the auditing events were to be fired by the client, your adversary could easily circumvent audit trails (i.e. by not using your client, modifying it, mimicking it or hijacking the session to the DBMS).

     

      In SQL Server 2008 we have new auditing infrastructure that should be useful for your scenario, I would recommend reading the SQL Server (2008) Audit How-to topics, this will give you an idea of how this new feature can help you to solve your auditing needs when SQL Server 2008 is available. http://msdn.microsoft.com/en-us/library/cc280505(SQL.100).aspx

     

      BTW. We will really appreciate any feedback/additional questions on this new feature.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Friday, July 11, 2008 6:16 PM

All replies

  •   Correct me if I misunderstood your question, but you want to know where (server/client) it is recommended to generate the audit events, correct? If my assumption is correct, auditing should be done on the server, otherwise the auditing information will be inaccurate; if the auditing events were to be fired by the client, your adversary could easily circumvent audit trails (i.e. by not using your client, modifying it, mimicking it or hijacking the session to the DBMS).

     

      In SQL Server 2008 we have new auditing infrastructure that should be useful for your scenario, I would recommend reading the SQL Server (2008) Audit How-to topics, this will give you an idea of how this new feature can help you to solve your auditing needs when SQL Server 2008 is available. http://msdn.microsoft.com/en-us/library/cc280505(SQL.100).aspx

     

      BTW. We will really appreciate any feedback/additional questions on this new feature.

     

      Thanks a lot,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Friday, July 11, 2008 6:16 PM
  • Logging  transactions can be accomplished with a trigger, although the overhead and risk involved can be substantial.  The need to log SELECT falls beyond a trigger and I would have to recommend creating a trace to run from profiler.

     

    Friday, July 11, 2008 8:16 PM
  • There are many products available for SQL Server tracing that include detailed user activity including Idera's auditing product and Enzo Audit. However These solutions cannot provide IP address information since they depend on SQL Server tracing which does not provide it.

     

    Recently however SQL Server started including the IP address of a successful login (as part of the login event within those traces) - so it is now possible to use this information, but this is rather limited since no other event has this information and it is still difficult to tie which SELECT is related to which login event. You need to use the latest service pack of SQL Server 2000 to get the IP as part of the login event when tracing activity.

     

     

    Wednesday, July 16, 2008 12:18 AM