SQL SOX auditing and logging

7 Feb 2007 5:15 PM

SQL apprentice

Hi,

We are currently being audited for SOX compliance. The auditor wants us to
create log that captures all the activities from the DBA in the SA role.
Is there an easy way to generate logs for this audit that can be written to
the system event logs or flat file?
We can cause any performance delays on the SQL server...So I think profiler
might be too much for 24/7/365 logging.
Do you use any third party tool you would suggest for SOX auditing and
logging?

Any help is good!!!

Thanks,

7 Feb 2007 10:57 PM

Erland Sommarskog

SQL apprentice (mssqlwo***@yahoo.com) writes:

> We are currently being audited for SOX compliance. The auditor wants us
> to create log that captures all the activities from the DBA in the SA
> role. Is there an easy way to generate logs for this audit that can be
> written to the system event logs or flat file?
> We can cause any performance delays on the SQL server...So I think
> profiler might be too much for 24/7/365 logging.

Yes, but there is no reason to use Profiler. You can set up a server-
side trace instead.

But of course, as soon as you tell the auditor that he DBA can stop the
trace whenever he wishes, the auditor will not accept that log anyway.

And, while you can filer a trace for a loginname as "sa", I don't think
you can filter "capture anyone with sysadmin prvis". If the DBA wants to
be unlogged, he can create an account which has sysadmin privs, and
work from that when he wants to be private.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

8 Feb 2007 2:22 AM

SQL apprentice

Thank you Erland,

I know what you mean...I think I will have to look for a 3rd party logging
tool like log explorer or ecora, so SOX people can control what they want.
At the sametime, they could cause performance lag to the database.
Have you done SOX for SQL?

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns98D0F33007CAFYazorman@127.0.0.1...
> SQL apprentice (mssqlwo***@yahoo.com) writes:
> > We are currently being audited for SOX compliance. The auditor wants us
> > to create log that captures all the activities from the DBA in the SA
> > role. Is there an easy way to generate logs for this audit that can be
> > written to the system event logs or flat file?
> > We can cause any performance delays on the SQL server...So I think
> > profiler might be too much for 24/7/365 logging.
>
> Yes, but there is no reason to use Profiler. You can set up a server-
> side trace instead.
>
> But of course, as soon as you tell the auditor that he DBA can stop the
> trace whenever he wishes, the auditor will not accept that log anyway.
>
> And, while you can filer a trace for a loginname as "sa", I don't think
> you can filter "capture anyone with sysadmin prvis". If the DBA wants to
> be unlogged, he can create an account which has sysadmin privs, and
> work from that when he wants to be private.
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

9 Feb 2007 4:08 PM

Anith Sen

All monitoring mechanisms ( either home grown or third party ) incur some
overhead. However you can tune up the products you mentioned according to
your operational hours.

For instance, AuditDB from Lumigent can be set to do continuous alerting and
active log collection during non-peak hours. Idera Compliance Manager can be
set to do minimal tracing by avoiding full SQL constructs and eliminating
SELECT statement auditing.

In general, most popular auditing products in the market are configurable --
you can find the best one by trying out an eval. in your current
environment.

--
Anith