2

I have enabled Auditing in SQL Server 2008 R2 to monitor DML and DDL operations.

Is there any way to load SQLAudit files to the database? Or any other method to automatically load audit details directly to a database?

6

The following process has worked well for us.

We write our audit files to a file share. I have a Sql Server instance dedicated to processing these audit files. On this SQLAudit instance, I have a job that runs every minute and executes a stored procedure.

The stored procedure:

  • Uses Powershell to move any audit files (that are not currently being written to) to a staging fileshare. The powershell command uses the -ErrorAction SilentlyContinue parameter which allows the command to 'skip' over files that are currently being written to when copying to the staging fileshare. Eventually, the 'locked' files will become available to be copied on a future interval.
  • Now that I have available SQLAudit files to read from the staging fileshare, we're able to use sys.fn_get_audit_file to process all SQLAudit files from the staging fileshare by using wildcards.
  • After consuming the files from the staging fileshare, I use Powershell to delete the files from the staging fileshare.

Here's the stored procedure.

CREATE PROCEDURE [dbo].[SqlAuditCaptureAuditLogs]
AS
BEGIN
    SET XACT_ABORT ON

    EXEC xp_cmdshell 'powershell.exe "Move-Item \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\*.sqlaudit \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging -ErrorAction SilentlyContinue"'
        ,no_output

    INSERT INTO SQLAUDIT.[dbo].[SQLAUDIT_HISTORY] (
        event_time
        ,sequence_number
        ,action_id
        ,server_principal_name
        ,server_instance_name
        ,database_name
        ,schema_name
        ,object_name
        ,statement
        )
    SELECT event_time
        ,sequence_number
        ,action_id
        ,server_principal_name
        ,server_instance_name
        ,database_name
        ,schema_name
        ,object_name
        ,statement
    FROM sys.fn_get_audit_file('\\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.*', DEFAULT, DEFAULT)
    WHERE server_principal_name NOT IN (
            ,'mfoperational'
            ,'mfreader'
            ,'aperioapplication'
            ,'aperioreader'
            ,'aperiosync'
            ,'aperiobuild'
            ,'DataFix'
            ,'mapinfo'
            ,'GeoSpatialUpdater'
            ,'sa'
            ,'ussql1'
            ,'usweb'
            ,'uswebs'
            ,'vmview'
            ,'dba-admin'
            )

    EXEC xp_cmdshell 'powershell.exe "Remove-Item \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.* -ErrorAction SilentlyContinue"'
        ,no_output
END
  • Nice script. The lists of users you're excluding with the NOT IN, I assume those are applications you already trust, and therefore don't feel the need to audit? – BradC Jun 25 '18 at 20:35
  • @BradC - that is correct – Scott Hodgin Jun 25 '18 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.