August 11, 2009 at 4:13 pm
BOL states the following about the option SHUTDOWN_ON_ERROR: "Specifies that if the trace cannot be written to the file for whatever reason, SQL Server shuts down. This option is useful when performing security audit traces.
When running a server-side trace, is there any way that the trace file could not be written to with the exception of:
1) disk drive where trace file resides runs out of space
2) max_file_size of trace file is exceeded and there is no rollover option
While the trace is running, I have tried deleting the file, changing the name, changing the file permissions, and changing permissions and name on the folder in which the file resides. None of these stop SQL Server from writing to the file.
I'm also wondering if anyone knows how shutting down SQL Server if the file cannot be written to is *useful* in performing audit traces.
I ask these questions because my company's security regulation requires that if we do not use C2 auditing, a server-side trace must utilize the SHUTDOWN_ON_ERROR option. But I'm nervous about the idea of SQL Servers shutdowns happening outside of our control.
August 14, 2009 at 10:13 am
Considering nobody has responded, I'm wondering if the question was phrased poorly, placed in the wrong forum, or simply not interesting. But for what it's worth, I did find a way to cause a SQL Server shutdown on a trace with the shutdown_on_error property.
First I created a server-side trace file (call it mytrace.trc) with option 6 (rollover 2 + shutdown_on_error 4). I set the maxfile size = 1 MB. Then I added the SP:statement event to the trace and started it. Afterwards, I created a text file in the same directory called mytrace_1.trc. Finally I began running sql queries until the trace file filled up to 1MB. Once it hit that point, it tried to "rollover" and create the next file in the trace sequence (mytrace_1.trc) but couldn't since it already existed. At that point, SQL Server stopped. I don't think this was something that would happen in real life but still it was kind of interesting.
After working with server-side traces and C2 auditing, I think the shutdown_on_error property exists simply so you can create audit traces that behave in the same way as C2 auditing but without all the overhead.
August 17, 2009 at 10:17 am
You're probably right about logging to a file. not much can go wrong there.
I suppose if you log to a table instead of a file anything that would prevent that would cause the server to shutdown.
~BOT
Craig Outcalt
June 7, 2014 at 12:17 pm
This is an old post. I agree about the nervousness. I am wondering what other conditions might cause the auditing to shutdown the server? If I change any part of the audit when it is in Shutdown mode, (and this is in production), I have to assume the server is going to shut down.
Jamie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply