November 26, 2010 at 11:05 am
Hello,
(script is below)
I am new to working with sql and I haven't done anything since a db course in college. As an alternative to using C2 auditing for a system, a trace to monitor the required audit events is recommended. (C2 clogs up storage too quickly)
My question is how do I go about implementing the commands? Do I enable this at the query prompt in the management studio? I looked at using the sql profiler, but I do not see how or where to enter these commands. Thanks for any help you can provide. This is for SQL Server 2005.
The script is as follows:
CREATE PROCEDURE my_audit AS
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
DECLARE @my_audit_log NVARCHAR(128)
SET @maxfilesize = 5
-- Define custom @my_audit_log to path\filename
SET @my_audit_log = 'd:\sqlserver\audit\myauditlog.log'
EXEC @rc = SP_TRACE_CREATE @TraceID output, 6, @my_audit_log,
@maxfilesize, NULL
IF (@rc != 0) GOTO Error
-- Client side File and Table cannot be scripted.
-- Set the events:
DECLARE @on BIT
SET @on = 1
-- Logins are audited based on SQL Server instance
-- setting Audit Level stored in registry
-- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.[#]\MSSQLServer\AuditLevel
-- Audit Login System Starts/Stops
EXEC SP_TRACE_SETEVENT @TraceID, 18, 10, @on
EXEC SP_TRACE_SETEVENT @TraceID, 18, 11, @on
EXEC SP_TRACE_SETEVENT @TraceID, 18, 12, @on
EXEC SP_TRACE_SETEVENT @TraceID, 18, 14, @on
EXEC SP_TRACE_SETEVENT @TraceID, 18, 15, @on
EXEC SP_TRACE_SETEVENT @TraceID, 18, 21, @on
EXEC SP_TRACE_SETEVENT @TraceID, 18, 22, @on
EXEC SP_TRACE_SETEVENT @TraceID, 18, 23, @on
EXEC SP_TRACE_SETEVENT @TraceID, 18, 28, @on
EXEC SP_TRACE_SETEVENT @TraceID, 18, 35, @on
EXEC SP_TRACE_SETEVENT @TraceID, 18, 41, @on
--I'm omitting the rest of the trace setevent statement for brevity..
-- Set the Filters.
DECLARE @intfilter INT
DECLARE @bigintfilter bigint
-- Set the trace status to start.
EXEC SP_TRACE_SETSTATUS @TraceID, 1
-- Display trace ID for future references.
SELECT TraceID = @TraceID
GOTO Finish
Error:
SELECT ErrorCode = @rc
Finish:
GO
EXEC SP_PROCOPTION 'my_audit', 'startup', 'true'
GO
November 26, 2010 at 12:26 pm
You will actually want to go download the current version of the checklist. (I'm a contractor with military).
This script from the checklist will actually create a stored procedure that will run at the time SQL Server starts up. You just past this into a query window in SSMS and execute it against the database you want it stored in, I usually just put it in master. The script configures the procedure to startup automtaically on the startup of the SQL Server database engine, that is what this command does [EXEC SP_PROCOPTION 'my_audit', 'startup', 'true'].
If you are a facility that requires information assurance approval for servers you will want to include some additional code to what the script has. I add WITH ENCRYPTION after this statement [CREATE PROCEDURE my_audit AS] so the procedure does not show up in DG0091 finding. As well a fault this script has is using a static filename for the trace file. I have come across where the initial file exist and due to patching or scheduled reboots the trace errors on startup since it does. To get around this I just wrote up some additional code to add a random number to the filename so it does not create a static name, so far it has worked well for me:
--Setup random number to add to initial filename creation to prevent file error
DECLARE @randnum VARCHAR(2)
SELECT @randnum = FLOOR(RAND() * 100)
-- Define custom @fso_audit_log to path\filename
SET @fso_audit_log = 'd:\sqlserver\audit\fsoauditlog_' + @randnum + '.trc'
Which you will notice in the new checklist as well that they use ".log" in the filename. This is worthless in that SQL Server is going to create the file with ".trc" for trace file. I usually just change the .log to .trc so the filename does not show up like "fsoauditlog.log.trc".
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
November 26, 2010 at 12:35 pm
Shawn Melton (11/26/2010)
This script from the checklist will actually create a stored procedure that will run at the time SQL Server starts up. You just past this into a query window in SSMS and execute it against the database you want it stored in, I usually just put it in master.
If it's a startup procedure, it has to be in master.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2010 at 1:00 pm
Shawn, thx for the info and the link as well. Exactly what I was looking for.
November 26, 2010 at 1:07 pm
GilaMonster (11/26/2010)
Shawn Melton (11/26/2010)
This script from the checklist will actually create a stored procedure that will run at the time SQL Server starts up. You just past this into a query window in SSMS and execute it against the database you want it stored in, I usually just put it in master.If it's a startup procedure, it has to be in master.
Sure enough. I don't think I've ever tried to put it in another database to have figured that out the hard way. 😀 Thanks
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
November 26, 2010 at 1:11 pm
Are the log files generated significantly smaller than using C2?
November 26, 2010 at 1:23 pm
syseng617 (11/26/2010)
Are the log files generated significantly smaller than using C2?
In my environment they are, and it also puts less stress on the server since C2 auditing captures everything going on.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 17, 2011 at 1:17 pm
With reference to the statement below
I add WITH ENCRYPTION after this statement [CREATE PROCEDURE my_audit AS] so the procedure does not show up in DG0091 finding.
Everything I have read about using "with encryption" with Stored Procedures states that it does not really encrypt stored procedures, merely obfuscates them. It reports that anyone with access to the system tables can view the text. Laurentiu Cristofor of Microsoft says
Actually, "WITH ENCRYPTION" is an old clause that is only providing some simple obfuscation and should not be used in any scenario where the word security is also used. It can be easily broken to retrieve the original stored procedure code. Feel free to use it, but don't expect it to protect you from database administrators and don't expect it to work as some kind of DRM mechanism for protecting codec you are shipping with a database. The only reason this option is still available is for backward compatibility. To my knowledge there are no plans to improve it and no immediate plans to remove it either.
Again, DO NOT USE "WITH ENCRYPTION" if your use is due to any security concerns! '
It also reports that once a stored procedure using "with encryption" is compiled, then it can no longer be altered. Does this really satisfactorily address STIG DG0091?
January 31, 2014 at 11:59 am
Has anyone addressed the With Shutdown portion of the audit process?
I had a thought about placing it into the folder with the logs but I can't come up with an alternative to the With Shutdown option. Anyone with ideas as to how that could be accomplished or [How it is actually accomplished] I would be happy to hear about it. (Quantum John has some comments here: http://blogs.technet.com/b/fort_sql/archive/2009/10/29/custom-auditing.aspx - if it helps trigger some thought.)
And:
SELECT CAST(value AS INT) FROM ::FN_TRACE_GETINFO('2')
where property = 1 and (cast(value as int) & 4 = 4)
Jamie
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply