August 12, 2010 at 4:52 pm
Disclaimer: I'm new to tracing - until looking at a 300 page 'hardening' script three weeks ago my knowledge was limited to what I read on the forums here, with no practical experience.
I've adapted code to build a trace of login/logout only; I've used the Profiler to run a similar trace. I've managed to create trace files, browse trace files, and prove that all is what I get is not what I want.
What I need: a trace that can be launched automatically every time SQL Server starts (via Agent?), and runs continuously [yes, I understand the disk management issues and know they need to be monitored and handled external to the trace].
First issue:
In Profiler, I can create a trace that does EXACTLY what I want to do. But, (a) every time SQL Server is restarted, the trace has to be re-created, (b) as soon as I close Profiler, the trace ends, and (c) I cannot get a T-SQL code equivalent out of Profiler to verify against what I've written in a stored procedure.
Second issue:
I can write a stored procedure to create a trace that persists until the next restart (independent of Profiler). BUT, I cannot get it to successfully filter certain system login ids that I don't want to see. I can filter login ids created in SQL Server (both sql server ids and windows ids - mixed authentication). Unable to filter the 'SYSTEM' id, using values 1, 6, and 64 using displayed content in the trace file (is there a trailing space I can't see, possibly?).
I have to go back and forth between my desk and the secured server area in order to run my testing (mstsc.exe not working right), so I can't provide code samples easily (specific questions in response will be answered as best I can, to provide more detail).
Once this is solved, there are additional traces to run, but this is an 'easy' task to start with....TIA!;-)
August 12, 2010 at 5:39 pm
You can use sp_procoption to run a stored procedure on startup, but I'd prefer to have a SQL Agent job that frequently checks to see if the trace is running. You'd need to give it a new filename each time the trace starts.
Why can't you get a T-SQL version of the trace out of Profiler? What's wrong with File | Export | Script Trace Definition | For SQL Server 2005-2008, and then modifying it to rollover, and log to a specific disk location? Your "Second Issue" sounds like you might already be doing this, but it ought to work. I suggest posting the filters (the sp_trace_setfilter commands, but the entire trace definition would be handy!), and also a verbal description of exactly what the filter is meant to be.
Alternatively, if you're on 2008 Enterprise edition, you might want to look at SQL Server Audit. It's a much more powerful auditing solution. See http://msdn.microsoft.com/en-us/library/dd392015(v=SQL.100).aspx
August 12, 2010 at 10:05 pm
Yes, Jim, you're right. Audit would make life much easier. Unfortunately, it's painfully difficult to justify an Enterprise Edition over Standard Edition for a single server.
I'll have to see whether I tried and failed your suggestion, or just didn't find the correct path to obtain. When I get in Friday morning (midnight now, where I am), I'll work it and see.
With the (finally) growing emphasis on security and auditing, I may end up building an article about this, compiling a suggested road map for others who may have to wander down this path.
Steve
//revised at 9:31 am 13Aug2010 EDT: Also, regarding the Agent process, it might be easier to rename or archive the file rather than invent a new name for it upon restart, since the logs need to be retained and disk space is an issue.
August 13, 2010 at 1:52 pm
One last observation: I unformat the current system date-time (using substring and cast) and append to the log file name in the stored procedure, thereby making the stored procedure(s) self contained - no agent required.
Is there a downside? I'm currently using nearest second precision, in the expectation that it is very hard to bounce a service in under a second. (Certainly true on the servers we use). If the systems ran faster, I could pick up the fractional values as well.
When I build my automated process(es) to compress/encrypt and transfer the log files to historical storage, I can control by date created and the beginning of the file name to incorporate only the files I need.
Thanks for the important reminder!
August 13, 2010 at 2:09 pm
Just curious but why do you care about LOGOUTs? If your security hardening is for purposes of policy (company or vendor such as PCI or HIPPA) most only want to know initially that a user successfully logged in. Which this is a check box under Security of the server properties to have this logged to the SQL Server ERRORLOG file.
If that detailed informaiton is needed on login/logout, what object they accessed and such you might look at C2 Audit feature. This will create a trace file with information that meets Department of Defense standards (they are the reason it was added starting in SQL Server 2000).
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 13, 2010 at 7:51 pm
The C2 audit feature captures everything. That is more than we need. One of the validating aspects of capturing logout as well as login, is to understand duration of connection - we have an idle-logout setting, but if someone has found a way to violate that and still chew up resources, we'd want to know.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply