Automate the Import of SQL Server Audit Files into SQL Server Using PowerShell

By:   |   Updated: 2015-02-02   |   Comments (4)   |   Related: > Auditing and Compliance


Problem

When security and auditing are crucial for your business, the IT security team would be very optimistic that you have configured the SQL Server Auditing option to monitor users actions on the production databases.  The SQL Audit files are then delivered automatically to the IT security team, but when they open these SQLAUDIT files, they find only rubbish data.  This is normal, as you need to SELECT using the fn_get_audit_file function to read these logs.

Mmm… There are numerous large files to search through for a specific action, so how could we make this easier?

Solution

If you try to open a SQLAUDIT file, you will find meaningless data as below:

If you try to open the SQLAUDIT file, you will find meaningless data

When you try to select the data using the fn_get_audit_file function using SQL Server Management Studio, the result will be like below which is much better.  The problem is that this could be time consuming to look at each file individually.

SELECT * FROM fn_get_audit_file('FileName_WithFullPath', default, default) 
Try to select the data from the fn_get_audit_file function using the SQL Management Studio.

Creating a SQL Server Audit Repository

Having a separate SQL Server, database and table to restore the audit files will make these audit logs readable for the IT security department.  In our case, a large number of production SQL Servers will be audited. We therefore created a separate SQL Server, a separate database for each audited server and the below table in each database.

CREATE TABLE [dbo].[Audit_rows](
 [event_time] [datetime2](7) NULL,
 [sequence_number] [int] NULL,
 [action_id] [varchar](4) NULL,
 [succeeded] [bit] NOT NULL,
 [permission_bitmask] [bigint] NOT NULL,
 [is_column_permission] [bit] NOT NULL,
 [session_id] [smallint] NOT NULL,
 [server_principal_id] [int] NULL,
 [database_principal_id] [int] NULL,
 [target_server_principal_id] [int] NULL,
 [target_database_principal_id] [int] NULL,
 [object_id] [bigint] NULL,
 [class_type] [varchar](10) NULL,
 [session_server_principal_name] [nvarchar](100) NULL,
 [server_principal_name] [nvarchar](100) NULL,
 [server_principal_sid] [nvarchar](100) NULL,
 [database_principal_name] [nvarchar](100) NULL,
 [target_server_principal_name] [nvarchar](100) NULL,
 [target_server_principal_sid] [nvarchar](100) NULL,
 [target_database_principal_name] [nvarchar](100) NULL,
 [server_instance_name] [nvarchar](100) NULL,
 [database_name] [nvarchar](100) NULL,
 [schema_name] [nvarchar](100) NULL,
 [object_name] [nvarchar](100) NULL,
 [statement] [nvarchar](max) NULL,
 [additional_information] [nvarchar](500) NULL,
 [file_name] [nvarchar](500) NULL,
 [audit_file_offset] [bigint] NULL,
 [user_defined_event_id] [int] NULL,
 [user_defined_information] [nvarchar](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now we need an automated way to restore these audit files to that table, rather than running the below query on each file every time we need to restore the data, such as below:

INSERT INTO db_name.dbo.Audit_rows
SELECT * FROM fn_get_audit_file('FileName_WithFullPath', default, default) 

Automating Data Loading of SQL Server Audit Files

Here we preferred using Windows PowerShell to write a function that loops through all SQLAUDIT files in a specific folder, loading the data and moving the file to another folder and optionally sending a success email. This function is written in a readable way with full detailed logs and comments.  You can download a copy of the complete PowerShell script here.

A full description for the function’s parameters, examples and functionality can be viewed by running the get-help PowerShell command as follows:

get-help .\Invoke-CorpSQLAuditRestore.ps1 –full

Calling the Invoke-CorpSQLAuditRestore function in PowerShell is easy, you need to specify the parameters as in the two examples below (included in the function's help):

Process all audit files inside folder "C:\Source"

.\Invoke-CorpSQLAuditRestore.ps1 -ScriptFilesPath .\ -SourceDir C:\Source\ -DestDir C:\Dest\ -SQL_srv_name SRV1 -SQL_db_name db1

Process all audit files inside folder "C:\Source" and send an email

 .\Invoke-CorpSQLAuditRestore.ps1 -ScriptFilesPath .\ -SourceDir C:\Source\ -DestDir C:\Dest\ -SQL_srv_name SRV1 -SQL_db_name db1 -SendMail:$true -MailFrom [email protected]  -MailTo [email protected]  -MailServer smtp.contoso.com 

Example Run

Let’s try to apply the first example from the above on a folder with 4 SQLAUDIT files, a progress bar should appear showing the progress percentage along with the procedure description that looks like the below:

Apply the first example from the above on a folder with 4 SQLAUDIT files.

And the final result is like below:

This PowerShell script could be run upon demand or scheduled via windows scheduled task

This PowerShell script could be run on demand or scheduled via a Windows Scheduled Task. Last but not least, make sure that the user that runs this script has access to the database server and to the source and destination folders.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2015-02-02

Comments For This Article




Friday, December 29, 2023 - 6:43:31 AM - Murali Krishna Back To Top (91818)
Table structure: (Tested on SQL Server 2019)

CREATE TABLE [dbo].[Audit_rows] (
[event_time] [datetime2](7) NOT NULL,
[sequence_number] [int] NOT NULL,
[action_id] [varchar](4) NULL,
[succeeded] [bit] NOT NULL,
[permission_bitmask] [varbinary](16) NOT NULL,
[is_column_permission] [bit] NOT NULL,
[session_id] [smallint] NOT NULL,
[server_principal_id] [int] NOT NULL,
[database_principal_id] [int] NOT NULL,
[target_server_principal_id] [int] NOT NULL,
[target_database_principal_id] [int] NOT NULL,
[object_id] [int] NOT NULL,
[class_type] [varchar](2) NULL,
[session_server_principal_name] [nvarchar](128) NULL,
[server_principal_name] [nvarchar](128) NULL,
[server_principal_sid] [varbinary](85) NULL,
[database_principal_name] [nvarchar](128) NULL,
[target_server_principal_name] [nvarchar](128) NULL,
[target_server_principal_sid] [varbinary](85) NULL,
[target_database_principal_name] [nvarchar](128) NULL,
[server_instance_name] [nvarchar](128) NULL,
[database_name] [nvarchar](128) NULL,
[schema_name] [nvarchar](128) NULL,
[object_name] [nvarchar](128) NULL,
[statement] [nvarchar](4000) NULL,
[additional_information] [nvarchar](4000) NULL,
[file_name] [nvarchar](260) NOT NULL,
[audit_file_offset] [bigint] NOT NULL,
[user_defined_event_id] [smallint] NOT NULL,
[user_defined_information] [nvarchar](4000) NULL,
[audit_schema_version] [int] NOT NULL,
[sequence_group_id] [varbinary](85) NULL,
[transaction_id] [bigint] NOT NULL,
[client_ip] [nvarchar](128) NULL,
[application_name] [nvarchar](128) NULL,
[duration_milliseconds] [bigint] NOT NULL,
[response_rows] [bigint] NOT NULL,
[affected_rows] [bigint] NOT NULL,
[connection_id] [uniqueidentifier] NULL,
[data_sensitivity_information] [nvarchar](4000) NULL,
[host_name] [nvarchar](128) NULL
) ON [PRIMARY]
GO

Wednesday, November 8, 2017 - 3:17:26 PM - Scott Back To Top (69449)

The powershell and all works great!

 

The create table statement needs some updating:

[server_principal_sid] [nvarchar](100) NULL,

should be

[server_principal_sid] [varbinary] (max) NULL,

The following columns should be added:

 ,[audit_schema_version] [int]  NULL

 ,[sequence_group_id] [varbinary] (max)  NULL

 ,[transaction_id] [bigint] NULL

 


Wednesday, October 21, 2015 - 8:35:08 AM - Ahmad Yaseen Back To Top (38950)

Thanks Tyler for your comment.

Please note that any file that is still being written will be opened by the SQL Engine and the script will not be able to read it.

Best Regards,

Ahmad

 

 


Tuesday, October 20, 2015 - 5:13:40 PM - Tyler Back To Top (38948)

What does the script do if the Audit File is still being written too?















get free sql tips
agree to terms