Help for SQL rookie

  • 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

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Shawn, thx for the info and the link as well. Exactly what I was looking for.

  • 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

  • Are the log files generated significantly smaller than using C2?

  • 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

  • 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?

  • 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