How to Audit DMLs in SQL Server 2005

Hi,

I am looking for solutions of auditing DMLs using SQL Server 2005.
The tasks are:

1) Audit DML statements, including SELECT, INSERT, UPDATE, and DELETE. Please note, not only UPDATE, DELETE and INSERT, but also SELECT
2) Protect the audit trails from insider threats, i.e. protect the audit trails from DBAs.

The questions are:
What would be the solutions recommended by MS?
What are the options, either good or bad, to accomplish auditing DML and protecting audit trails.

Thanks a lot

The most 'robust' and secure method is utilzing one of the third party products designed for that purpose. Any method utilizing server based triggers or stored procedures 'could' be vunerable to DBA mis-use.

Here are links to most of them -there could be additional not on my list.

Audit Tools
ApexSQL Audit http://www.apexsql.com/sql_tools_audit.asp
AuditDatabase (Free Web based trigger generation) http://www.auditdatabase.com/
Lumigent Adit DB http://www.lumigent.com/products/auditdb.html
OmniAudit http://www.krell-software.com/omniaudit/index.asp
SQLLog http://www.rlpsoftware.com/mainframe.asp?contents=SQLLog.asp&mainmenu=SQLLog&submenu=Info
Upscene SQL Log Manager http://www.upscene.com/index.htm?./products/audit/mssqllm_main.htm
DB Audit Expert http://www.softtreetech.com/dbaudit/

|||Hi, Arnie,

Thanks a lot for your prompt response.

I checked out these products and found they can audit INSERT, UPDATE, and DELETE, most via triggers, but they can not audit SELECT.

Please forgive me for a few more questions in that direction:

1) Is there any built-in features of SQL Server 2005 for DML auditing, except database triggers?
2) Can SQL Profiler be used for auditing SELECT, INSERT, UPDATE and DELETE statements?
3) Is there any way to audit SELECT using SQL Server 2005?

Thanks a lot again :-)

|||

I don't think that is exactly correct. Lunigent's Audit tool reads the Transaction log -does not rely on database Triggers, AND can easily track SELECT activity.

You should check it out a bit more. Not inexpensive, but it will be less than the time cost to attempt to create anything that does the job, and whatever is custom created is guaranteed to be less 'robust'.

http://lumigent.com/products/auditdb.html

Your questions...

1. No

2. Yes, but with a measurable performance hit. And would be easily accessible by a DBA since it requires the same level of access to the server as a DBA.

3. No, not directly. SELECT statement 'could' be done through stored procedures, and INSERTs make to a logging table, but again, suseptible to DBA interference.

If you want to be able to log priviledged users (DBA, DBO, etc.) AND data reads, I don't know of any 'secure' method other than the third party products.

|||That is great to know that SQL Profiler can audit SELECT, INSERT,UPDATE and DELETE.

Assuming we ignore the requirement about protecting auditing trails for this moment:

1) Which event(s) should I audit SELECT, UPDATE, DELETE and INSERT against a table using SQL Profiler?
2) Can we audit the (before and after) values of UPDATE, DELETE and INSERT on a table in a trace log?
3) Also I am a little confused: while saying SQL Server 2005 doesn't have built-in auditing DML feature (except triggers), however, SQL Profiler does audit SELECT, UPDATE, DELETE and INSERT. Is there any thing wrong in auditing DML in SQL Profiler, except performance issue? I am not sure if we could say or use SQL Profiler to audit DML.

Yes, I double checked Lunigent's Audit tool, it does audit all DMLs.
Thanks a lot for that information.

I am very new to SQL Server, please forgive me for asking so much questions

|||

Profiler can capture the command and parameters/values that were sent to SQL Server. It does not, however, provide the results (or 'after) state due to data modification -UNLESS there is another specific data read.

Events: TSQL: StmtStarting, TSQL: StmtCompleting

Profiler wasn't designed as a 'Auditing' tool.

Profiler is a bit 'ham handed' compared to the tools specifically designed for auditing.

Profiler requires the same level of permissions as a DBA would have.

Profiler can be easily 'thwarted' by a DBA.

AS you stated your specifications, I highly recommend NOT going further with Profiler for the Auditing task.

You will be investing a lot of time and effort, and it still will not meet your requirements.

|||Thank you very much, Arnie, your comments are very helpful...
sql