RE: auditing database/server activities

From: JMBickham (JMBickham_at_discussions.microsoft.com)
Date: 12/06/04 

Date: Sun, 5 Dec 2004 18:37:02 -0800

What you may want to first look at are your permissions and login ids. Basic
stuff... make DBA's and SA's use ids that have limited permissions.

Have your audit logs sent externally to a "central audit" DB. Only give
Security team memebers access to this DB. Do your security auditing from
this DB.

Here's a response I got about doing the above...
Server side traces (i.e. without using the Profiler GUI) can only write to
trace files. You can load trace files into a sql table (for example on a
central "audit" server) using the sytem function fn_trace_gettable. See BOL
for details 

-- 
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
Just a few thoughts as I've been working with the same issues...
"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

Relevant Pages

  • IBM, AMD and Novell Team on Linux Offering for Informix Dynamic Server
    ... IBM, AMD and Novell Team on Linux Offering for Informix Dynamic Server ... code-named "Cheetah." ... The new Linux offering will combine IDS Cheetah, ...
    (comp.databases.informix)
  • Re: Running profiler on busy 8-cpu Sql Server
    ... I always start with a light trace and add more detail as I go along to ... SQL Server MVP ... > I think it's also worth saying that you should introduce Profiler ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Appeal for Help. NOT Code Red But Is It?
    ... our server immediately responds back to the prober ... What is happening is that the IDS is becomming confused about who the ... each worm that is still on its way from the attacker. ... > and outbound port was 2913. ...
    (Incidents)
  • Re: Repost - Unable to authenticate a VPN
    ... Thanks I did get the trace.. ... Microsoft Small Business Server Support ... >> the connection manager client.. ...
    (microsoft.public.windows.server.sbs)
  • Re: URGENT deadlock question
    ... > Profiler to view a trace, SQL Server dumps rowsets across the network to ... > SQL Server doesn't feel like it can keep up with all the activity. ... > server-side trace to a trace file guarantees you'll see everything. ...
    (microsoft.public.sqlserver.server)