I need a way to automatically generate a daily report of user names, time of access, and type of access (login, select, insert, update, delete, etc.) to a database.
We need to audit all accesses from all accounts to find out who is accessing the payroll system. We have already defined the security to be as tight as possible - however we need to monitor all Administrator accounts to ensure they are not being used to obtain sensitive information.
I have looked for some software to read the live SQL Transaction Logs, but none of the tools I have seen can do exactly what we require. For example, I've tried Lumigent Log Explorer (http://www.lumigent.com/products/le_sql.html), but it's not automated and the report output is just garble to our payroll staff.
I have looked up all Microsoft SQL Server partners but their software is all about data recovery and data reporting. We need Security Reporting.
I have created a general SQL Profiler trace that may suffice if everything else fails, but I still have no way of automating it - it relies on Real-Time monitoring which I can't trust (admin can turn this off, do their stuff, turn it back on). Also this is dependant on someone turning it off, save the log, turn it back on, every day - this person would be the Administrator - what admin would monitor themselves?
- SQL Trace seems like a good idea, how do I read the trace log file? How do I automate a trace?
- Can we automate the SQL Profiler to read the transaction log to find all accesses to a database?
- Does anyone know what we can do to automatically audit accesses to a database? What format can the report come in (pdf, xls, dbf, html etc.)
- Recommended software to try?
- Ideas for alternatives to try?
- Any companies/developers out there who know of a tool or are developing a tool to provide this sort of report?
- Is there a spec somewhere to let us write our own software to query the live transaction log?
Thanks, I appreciate any help.
Last edited by badseeds; 08-05-04 at 21:26.
Reason: Found more information
Assuming it is worth the cost, you can investigate the C2 security options built into SQL Server 2000. They are hideously expensive in terms of disk and system load if fully implemented, but they meet the U.S. Government requirements for C2 clearance (the best possible using standard commercial hardware).
I had previously decided that C2 certification is probably not necessary in our circumstance. Plus, the SQL server does a few million transactions overnight just in updating some tables, and these tables are also accessed by Analysis Services OLAP Cubes when the cubes are updated, and when user's do a Drillthrough on the cubes (they go to the live table on the SQL Server).
We just have too many different applications that access the SQL server, and it would make the log file very large and very messy. Plus we need the performance up fairly high (the server is a single Xeon 2.4Ghz with 1 Gig of RAM).
I assessed a System Event Log Viewer (PsLogList - www.sysinternals.com) that can dump the event logs (system, application, security etc.) to wherever we want... this would probably be sufficient - if I can just get SQL Server to write to the event log those issues i'm interested in.
I tried creating a Trace and running it as a job to start running in the background, but I can't see the table updating with new records. I also tried saving to a file. It appears I need to have SQL Profiler open to run the trace.
what about using triggers? I use trigger on a table recording user logins to insert records to a worktable from which I later generate reports on user access, DML etc. Surely, performance factor must be considered first.
I remember discussing something like this idea at a previous Tech Ed, but I never really pursued the issue. The gist of the idea we were pursuing was to create a stored trace, and launch that trace via a startup procedure.
I've still got my notes from that discussion (probably on a napkin), but I don't have ready access to them at the moment. Unfortunately I'm going to be up to my eyebrows in alligators until after sometime Labor Day, so finding them isn't likely in the near future either. I still remember the discussion pretty clearly though, so if you've got questions I can probably either remember or reconstruct them fairly easily.
example of simple triigger that stores one row in login_info table every time someone logins to our accounting system (in other words update fields SY13027,SY13009 in table sy130100 to particular values)
create trigger login_trigger
declare @delmodule int, @inslogin int, @insmodule int
select @delmodule=SY13027 from deleted --logical before image
select @inslogin=SY13009, @insmodule=SY13027 from inserted --logical after image
if @delmodule=-1 and @inslogin=1 and @insmodule=-1
insert into login_info (login,fullname,company)
select SY13001, SY13002, 'CR' from inserted
Pat, I was also hunting a way to use a stored trace, I set up what I thought was enough to get one working but obviously my understanding is somewhat corrupted. I'll look into it some more before I make you wrack your brain for some answers, thanks for the offer. At least now I know it's a possibility.
mojza, the trigger sounds like an easy way to keep track of user logins, thanks for the post. Performance probably won't be affected by this since the trigger will be put on a table that should not be modified often - basically a couple times near end of month, and start of month. If I implement it I will let you know how the performance degraded.
Anyone know some great SQL Server resource sites that may aid in creating a stored trace?