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

How To Audit DML Table Changes w/o Triggers?

Whenever any DML activity occurs in a database I need to audit the following:

1. The table that was changed (INS, UPD, DEL)
2. The data value of the primary key of the changed row

For example, if someone executes:

UPDATE payroll SET Salary = 50000 WHERE empid = 123

I need: "payroll" and "123"

For various business reasons I can't use triggers. It is just one of those things...

I've looked at various options on SQL Profiler and while it looks like I can tell there was an action on the "payroll" table it doesn't look likely that I'll be able to figure out that it was on primary key 123. This seems to especially be the case on the execution of a stored proc where data values are passed as @.parameters.

I know there are third-party products that analyze transaction logs with a GUI and let you export these results to a CSV file or Excel. The concern here is that I need something which is an on-going process, so the GUI and human interaction necessary to generate the file doesn't quite cut it. If I'm wrong here and there's a suggestion I'd certainly look at it.

Thanks so much!

Doug

The options are pretty much what you described. Or you could do this in your code by logging the parameters to the SP that does the modification for example. Allowing ad-hoc insert/update/delete to tables directly is not a good thing to do. In SQL Server 2005, you can use event notifications to do this easily.|||Thanks for the feedback...but I'm not sure I understand you ... I cannot use a trigger ... and the log reading tools don't seem to offer a constant flow. So the items I mention in my post won't work.

Is there any other approach?

Thanks again...I appreciate any suggestions.

Doug|||My point was that your options are limited in SQL Server 2000. I am not aware of the fulll capabilities of various 3rd party tools that work with log files directly. Did you check web site of companies like Lumigent? Another idea I was thinking about was to use replication. You could configure log reader agent to output verbose information which will include commands that are being replicated. Maybe this will help. It is hard to tell. And it almost seems like you have to modify your application if existing tools or methodologies do not meet your requirements.|||OK, this gives me a couple of leads to follow. Thank you very much for thinking this over with me, I appreciate your feedback.

All the best!

Doug