RE: auditing database/server activities

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

Date: Sat, 11 Dec 2004 04:43:02 -0800

Entegra can only monitor data modifications (including DBA activity) if the
transaction log is turned on - an unscrupulous DBA could switch the database
to Simple mode, at which point Entegra (like all the audit products that use
the transaction log) is no longer able to reliably audit (because
transactions are cleared from the transaction log as soon as they are
commited to the database, before Entegra can record them).

To be fair to Entegra though, it does offer the ability to shut down the
server if this happens - I'm just not sure I'd be keen on having my single
most important database shut down automatically during business hours!

"jason" wrote:

> Ali,
> are you sure that entegra won't monitor dba activity?
>
> "Ali H 75" wrote:
>
> > 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