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:
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)
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 noreply@contoso.com -MailTo me@contoso.com -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:
And the final result is like below:
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
- To learn more about SQL Server auditing, read Introduction to SQL Server Audit and Configure SQL Server Auditing.
- To make it easier to understand the PowerShell code, check out Introduction to Windows PowerShell for the SQL Server DBA.
- You can download the PowerShell function here.
