RE: auditing database/server activities

From: Ali H 75 (AliH75_at_discussions.microsoft.com)
Date: 12/01/04 

Date: Wed, 1 Dec 2004 07:35:02 -0800

I've recently been looking at the various 3rd party auditing products
available, particularly the ApexSQL and Lumigent products (if you've got the
money then the Lumigent Entegra seems to be the best one out there, although
they all seem to be pretty reasonable). However, one thing I've noticed is
that all of these products rely on the database's recovery mode being set to
Full - i.e. If the Recovery Mode is set to Simple then auditing becomes
incomplete / unreliable.

I work for a financial organisation and so my requirement is for
tamper-proof auditing - this includes tampering by a DBA / other
administrator. Does anyone have any suggestions for how I could achieve this?
Is there a way of locking a SQL Server database into Full recovery mode? Or
perhaps if that can't be done is it possible to log changes to the recovery
mode? Or something else?

Thanks for your ideas!

"JMBickham" wrote:

> I probably should note that I am looking for a way to externally store db
> audit logs and be able to parse the data or filter for specific events and
> ids for review by a security team. Something less manual than copying trace
> files from the server to another server and going over each using profiler
> (we're talking about 30 servers here!)... but not necessarily as hands-off as
> dwh's approach with email alerting only.
>
> Thanks for any and all help!
>
> "dwh2200" からの元のメッセージ:
>
> > Fair enough. If you have the trace dump output to a table, you can get there
> > from where I left it by putting a scheduled job out there (or a trigger) that
> > can read the records and report back any information you want to have it
> > alert for via email.
> >
> > "jason" wrote:
> >
> > > dwh,
> > > with the approach you are employing, it requires you manually looking over
> > > the logs...right?
> > > I am looking for a way to set up a system that will automatically alert our
> > > DBAs of any activity we configure it to. Nor require any ongoing manually
> > > effort.
> > >
> > > "dwh2200" wrote:
> > >
> > > > I'm currently using a sql profiler trace to track changes made on the
> > > > database. Not really tracking inserts/updates/deletes, just the DDL and
> > > > security stuff. The Security Audit group of events in profiler give you most
> > > > of what you'd be interested in. For digging through transaction logs,
> > > > Lumigent's Log Explorer isn't a bad tool. For some extra $$, Entegra might
> > > > be an option as well.
> > > >
> > > >
> > > > "jason" wrote:
> > > >
> > > > > with increased concern of security these days. what are people using to
> > > > > audit the activities on a sql server database?
> > > > >
> > > > > if they use in the box tools, is the audit trail managable?
> > > > >
> > > > > are people using a 3rd party tool to do sql server auditing?
> > > > >
> > > > > thanks

Relevant Pages

  • RE: auditing database/server activities
    ... particularly the ApexSQL and Lumigent products (if you've got the ... > money then the Lumigent Entegra seems to be the best one out there, ... > Is there a way of locking a SQL Server database into Full recovery mode? ...
    (microsoft.public.sqlserver.security)
  • Re: Auditing object access from network
    ... server with domain member clients accessing the target file structure via ... I want to audit file deletions in specific directories of a file server, ... NOT BY USERS ACCESSING FILES VIA THE NETWORK. ... Set up auditing ACLs for every entity in Active Directory, ...
    (microsoft.public.win2000.security)
  • RE: filenames changing
    ... Mal is right, let's turn on file auditing, and troll the security log ... the server resides, right-click on it and click Properties. ... On the Group Policy tab, select a GPO and click Edit (if there's no GPO ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • SBS2K Server, how to change local policy, auditing on. Make Effective Setting.
    ... I ran Microsoft's MBSA 1.2.1 and noticed that auditing was not on on one ... I turned auditing on for the domain on the server, ... Effective Setting set is "Audit Account Management." ... Was I supposed to instead of editing the default domain policy in Active ...
    (microsoft.public.backoffice.smallbiz2000)
  • Re: URLSCAN.INI 0 Bytes in length
    ... I have turned on auditing now for that file. ... There are 10 websites running on this server. ... and am going to apply SP4 and see if I have any problems on it. ... > I'd suggest you check the event viewer and IIS logfiles. ...
    (microsoft.public.inetserver.iis.security)