SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by GSquared / Audit Trails and Logging Part I / Latest Posts

RE: Audit Trails and Logging Part I

John Rowan — Fri, 13 Jun 2008 09:51:46 GMT

Good one Gus, well presented. Looking forward to reading the second article.

RE: Audit Trails and Logging Part I

GSquared — Tue, 10 Jun 2008 11:28:17 GMT

[quote][b]fmuntean (6/9/2008)[/b][hr]Hi,I just want to point you to a free tool for monitoring the SQL Log files.It is Log Parser currently in v2.2 form Microsoft.It is using a query engine and can read all kinds of log files and event logs.It can output to the SQL database so you can build your own reporting on top.SQL server uses the event log too for sending info so it is not enough to look into the logs but also into the Event log (I ussually start from there).It would be interesting if you investigate the tool against those $1000 bucks tools.Florin[/quote]I was actually looking at that product a little while ago. From what I could tell, it could parse the event file, error log, etc., but I can't tell from the web page whether it will read an SQL Server transaction log. Have you tried that? Does it work well for it?

RE: Audit Trails and Logging Part I

Jeff Moden — Tue, 10 Jun 2008 04:38:45 GMT

Good tip! Thanks!

RE: Audit Trails and Logging Part I

fmuntean — Mon, 09 Jun 2008 20:59:36 GMT

Hi,I just want to point you to a free tool for monitoring the SQL Log files.It is Log Parser currently in v2.2 form Microsoft.It is using a query engine and can read all kinds of log files and event logs.It can output to the SQL database so you can build your own reporting on top.SQL server uses the event log too for sending info so it is not enough to look into the logs but also into the Event log (I ussually start from there).It would be interesting if you investigate the tool against those $1000 bucks tools.Florin

RE: Audit Trails and Logging Part I

Thomas Keller — Mon, 09 Jun 2008 15:00:47 GMT

I've been using trigger-based Audit Trails since SQL6, and every customer I've set them up for has loved them. Although there have been some use cases for workflow reporting and undo, the primary purpose has been tracking who-changed-what-when. Although you have classified this as Blamethrowing, it actually serves many other purposes. For instance, a user asks why their scheduled search did not return an expected result when expected, and I can show them that a) they added a criteria after the matching event had happened, and b) how to figure that out without asking me next time. It also answers questions about how a record got into a certain strange state, when there can be many users using multiple applications... and it can thereby turn up cases that need to be handled better in code.I agree that the audit trail tables can be tampered with by anyone with permission, but so far I haven't had to satisfy a legal requirement... if I did, I would just keep the log files as a backup in case there was any question.Also, although there seems to be a consensus that triggers need to be hard-coded to the table structure for speed, and thus rebuilt when the schema changes, I have worked out a way to quickly loop through existing columns, and only save changes, eliminating updates to the same value. I will be interested to see where your next article stands on this.

RE: Audit Trails and Logging Part I

DavidSimpson — Mon, 09 Jun 2008 14:37:54 GMT

[quote]I guess I'm not sure what you mean by trace files. Are you talking about having some piece of the database/application write data to a separate file and use that for logging?[/quote]I was referring to the SQL Server trace files created by sp_trace_create, sp_trace_setevent, etc which is the method SQL Profiler uses. David

RE: Audit Trails and Logging Part I

RBarryYoung — Mon, 09 Jun 2008 14:18:59 GMT

Nice article, Gus.

RE: Audit Trails and Logging Part I

GSquared — Mon, 09 Jun 2008 14:18:44 GMT

[quote][b]David (6/9/2008)[/b][hr]Very good article but you should add trace files to your option for auditing. I'm currently using Idera's tool which uses this method and been pretty happy with the results. There are some bugs and work around needed but no software is perfect.This process is obviously bit more invasive then reading a log file but from what I've seen the impact is not noticeable... especially since the actual manipulation of the trace files should be on a separate server. One possible downside though, is that you will not have the actual data manipulated like you would with a log reader or trigger, instead you would just have the DML statement. On the positive side, you can audit login activity and SELECT statements.David[/quote]I guess I'm not sure what you mean by trace files. Are you talking about having some piece of the database/application write data to a separate file and use that for logging?If so, yeah, that would be another means of active logging. I didn't actually think of having the logging take place outside of the database, but something like that was brought up by another person as part of a means of preventing SQL injection attacks from getting into the database, and tracking the attempted attacks. It certainly is an option, but not having used it, I don't think I can write more on that subject myself.

RE: Audit Trails and Logging Part I

GSquared — Mon, 09 Jun 2008 14:15:26 GMT

[quote][b]SQL Noob (6/9/2008)[/b][hr]article missed prevention and catching fraud as a reason for logging. we have triggers and management has caught people giving freebies to friends in the audit tables.[/quote]I would count that as a combination Reports/Blamethrower audit.

RE: Audit Trails and Logging Part I

alen teplitsky — Mon, 09 Jun 2008 11:27:55 GMT

article missed prevention and catching fraud as a reason for logging. we have triggers and management has caught people giving freebies to friends in the audit tables.

RE: Audit Trails and Logging Part I

DavidSimpson — Mon, 09 Jun 2008 10:58:31 GMT

Very good article but you should add trace files to your option for auditing. I'm currently using Idera's tool which uses this method and been pretty happy with the results. There are some bugs and work around needed but no software is perfect.This process is obviously bit more invasive then reading a log file but from what I've seen the impact is not noticeable... especially since the actual manipulation of the trace files should be on a separate server. One possible downside though, is that you will not have the actual data manipulated like you would with a log reader or trigger, instead you would just have the DML statement. On the positive side, you can audit login activity and SELECT statements.David

RE: Audit Trails and Logging Part I

Mike Dougherty — Mon, 09 Jun 2008 10:20:52 GMT

[quote][b]GSquared (6/9/2008)[/b][hr]Mike, I would actually classify what you're describing as a form of active auditing. You created a log other than the SQL server transaction log.[/quote]yeah... I didn't want to wait for Part II, sorry. :)

RE: Audit Trails and Logging Part I

GSquared — Mon, 09 Jun 2008 09:57:43 GMT

Mike, I would actually classify what you're describing as a form of active auditing. You created a log other than the SQL server transaction log.Yeah, in any sort of active logging, logging too much and not being able to use it for anything is a common problem. As is huge amounts of disk-use.For what you were doing, preventing code from ever reaching the database in the first place, passive auditing definitely wouldn't do it. Neither would trigger-based SQL auditing. That's when you have to have the front-end or some other tier do the logging for you.

RE: Audit Trails and Logging Part I

Mike Dougherty — Mon, 09 Jun 2008 08:08:21 GMT

We implemented a passive log while trying to audit SQL injection attempts from our website. We couldn't even begin to consider server-based solutions because the command batch itself was potentially poisonous. I wrote an access interface for the ADO connection, then forced every web transaction to go through that interface. It give me a chance to do basic heuristics before sending the command to SQL (and dumping obvious attack attempts before it gets anywhere near the data). It also writes each transaction to a folder based on page request. After a week it was interesting to review which folders contained the most transactions - you don't realize how much conversation the webserver has with the SQL server until you have a several gigabyte folder to illustrate that fact for you. In the post-mortem of an event, we tried searching the nearly one million <1k files. It never finished and we never found what we were searching for. Just like backups with untested restores, it's a good idea to test your audit strategy for realistic usability. Ultimately we turned off most of the 'normal' logging due to the extreme space requirement, but we continue to record those transactions that fail the heuristic. This was useful to recover a false-positive match due to a too-aggressive regex. Another useful point about auditing is to know your baseline so you can more quickly assess out-of-norm behaviors.</soapbox> now back to work....

RE: Audit Trails and Logging Part I

GSquared — Mon, 09 Jun 2008 06:37:06 GMT

[quote][b]margo (6/9/2008)[/b][hr]A nice summary, always useful to check what we're doing with what others are saying.One thing I would add to the downside of "passive" audits... the cost of keeping the log files around. One auditor suggested we keep our sql log files for a rolling 15 months (1 year plus a quarter or something like that). We did some quick math on the disk requirements and were well over a couple terabytes.True, disk space is becoming a rather inexpensive commodity, but still, it has to be considered. And heaven help you if you need to back sure that is backed up as well and now your doubling your disk requirements and cost, etc.fwiw.[/quote]Active logging (next article, should be tomorrow if I'm not mistaken), with some control over what gets stored, might be a better solution in that case. Either way, it's going to take disk space if you want logging.

RE: Audit Trails and Logging Part I

SuperDBA-207096 — Mon, 09 Jun 2008 06:05:45 GMT

Excellent work! I really enjoyed this one, it's timely as we've been discussing different approaches to auditing.

RE: Audit Trails and Logging Part I

margo — Mon, 09 Jun 2008 05:19:04 GMT

A nice summary, always useful to check what we're doing with what others are saying.One thing I would add to the downside of "passive" audits... the cost of keeping the log files around. One auditor suggested we keep our sql log files for a rolling 15 months (1 year plus a quarter or something like that). We did some quick math on the disk requirements and were well over a couple terabytes.True, disk space is becoming a rather inexpensive commodity, but still, it has to be considered. And heaven help you if you need to back sure that is backed up as well and now your doubling your disk requirements and cost, etc.fwiw.

RE: Audit Trails and Logging Part I

Cbo Spikes — Mon, 09 Jun 2008 04:23:05 GMT

Good article. I use audit trails for management reporting as well as automatically generated emails of certain type of changes. I currently use procs in jobs that run every minute or so, collecting the entire changed row into a seperate table. I then run a another job to analyze the changed rows that have been collected. This analysis produces specific "events" that are of intrest to users. Those events are stored in a seperate table. I then report and send notifications from there.This works well so far but I'll be looking for your next round of articles for any better ideas!

RE: Audit Trails and Logging Part I

Anipaul — Mon, 09 Jun 2008 00:35:11 GMT

Excellent one... nicely explained. :)

RE: Audit Trails and Logging Part I

Jeff Moden — Mon, 09 Jun 2008 00:19:36 GMT

Nicely done, Gus... Can't wait for the others!

Audit Trails and Logging Part I

GSquared — Sun, 08 Jun 2008 22:13:25 GMT

Comments posted to this topic are about the item [B]Audit Trails and Logging Part I[/B]