Transaction log "sniffer"

Question

Transaction log "sniffer"

Joseph Fallon

SSCommitted

Points: 1669
More actions
July 22, 2009 at 7:25 am

#156000

Hi folks,
We run an in-house accounting system. The app writes to the DB under its own SQL login.
Some admin users also set up to have write access to the database - these users log in using their Windows authentication.
In a nutshell, we need to report any changes made to the DB that aren't made via the apps SQL login.
Does anyone know of a good third party app that would do this for us ? Something that would read the transaction logs and pop up a nice report maybe ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well third party log sniffers are great , but you can set up a trace going forward for free.

Also, remember that the Application name can be faked by a developer-savvy person by simply adding it to the connection string....but the other 99% of the time, you should be good to go. all the login info used is there, so the combination is probably what you'd filter for.

here's how i would do it:

If you add a DML trace , you can query every insert/update/delete statement, and confirm that the application name used was one of the allowed values.

there's a thread here with my example of the trace i use:

http://www.sqlservercentral.com/Forums/FindPost745574.aspx

once that trace is up and running, and the included view to query the trace is in place, the query is simple:

select *

from sp_dmltrace

where Textdata like '%UPDATE%' --only looking for updated in this example

and ISNULL(ApplicationName,'') NOT IN( 'MyAppName' ,'.Net SqlClient Data Provider')

and starttime > dateadd(day,-7,getdate()) --when run on a regular reporting basis, only need what happened since last weeks report

the key is to get that trace in place so you can report against it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joseph Fallon SSCommitted Points: 1669 More actions · Answer 2

Thanks Lowell, that's mighty - I like it a lot !

I think my boss is keen on something that will work offline, reading the TRN logs.

Anyone know of any such animal ?

The major requirements would be the ability to filter out our main application's activity and reportability.

There seem to be a lot more "live" auditing apps than log readers, no ?

Many thanks folks.

Lowell SSC Guru Points: 323518 More actions · Answer 3

I need to do more research on some of the log applications and how they work, but I was always under the impression that they use a live database to compare against.

i didn't think they open up an individual transaction logs offline...for sure they have to have the last full backup , along with subsequent transaction logs,to compare it to to determine changes, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joseph Fallon SSCommitted Points: 1669 More actions · Answer 4

I think you're right there on that.

The Idera Compliance Manager product seems good - anybody have any experience with it ?

Anyways, I've made my recommendations now so we'll see what the chiefs say.

Thanks for your help Lowell