Protip: How to Setup User Activity & Server Logon Scan in StealthAUDIT for SQL

Now that you have been using StealthAUDIT for SQL for a while, you might be wondering how to squeeze more value out of the product by enhancing the information it is collecting and reporting on. 

StealthAUDIT for SQL relies on native SQL server auditing capabilities to collect and report on user activity, as well as successful or unsuccessful server or database logon activity.  As a result, you will need to adjust some additional configuration options within SQL to ensure you can collect the information you are expecting to see in your StealthAUDIT reports.

This blog will walk you through the steps required for setting up Microsoft SQL server to audit DML (Data Manipulation Language) activity so you can keep an audit trail on specific tables (e.g. those containing sensitive data) and record server level logon activity.

For Microsoft SQL Server, there are two types of auditing – server- level auditing and database-level auditing.  Each of these audit levels can be configured separately, providing flexibility when multiple databases exist within the same instance or if multiple instances reside on the same server.  Below is a summary of SQL Server Audit support in different editions of SQL Server:

Once an audit specification is configured and enabled, SQL server will write the audit events to a binary file, Windows event log, or SQL Server event log.  StealthAUDIT for SQL scans the binary audit file to capture the audit events.

Step 1 – Create an Audit Object

In Microsoft SQL Server Management Studio (SSMS), create an audit object by right-clicking on Security -> Audits and choosing “New Audit” option.  You can either choose the default audit name or give it a more meaningful name (for example Audit-Sbits_SBMSSQL2017).  Make sure that the Continue option (default) for the On-Audit Log Failure is chosen.  Next, specify the path where SQL server should write the audit log file to and choose other file attributes as appropriate.

When auditing multiple SQL Server instances and databases, consider scripting the process rather than using the GUI.  Create an audit using the GUI for one of the instances and click on the script button to generate the script and modify in SSMS and modify the script as required.

USE [master]
GO

CREATE SERVER AUDIT [Sbits_Server_Audit_Spec]
TO FILE 
(	FILEPATH = N'C:SQLAUDIT_LOG'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = '902f63eb-d960-439f-b27d-eeff21dd2e52'
)
ALTER SERVER AUDIT [Sbits_Server_Audit_Spec] WITH (STATE = ON)
GO

Recording of audit events can be synchronous or asynchronous.  If the “Continue” option is chosen for the “On Audit Log Failure”, the users interacting with the database will not be impacted. However, auditing of certain actions might be skipped.  If it is critical to audit every single action (even if it means impacting the ongoing transactions), then choose either “Fail operation” or “Shut down server” based on your needs.

You will also notice that for “Audit destination”, the only option available type is “File”.  Provide a desired path to store the audit log files and set other file attributes as applicable.  In addition to the audit actions being written to a file, it will also be written to the Windows Events Viewer and SQL Server logs.  The audit events can be read by using Windows “Event Viewer”, “Log File Viewer”, or by reading the audit file using the “fn_get_audit_file” function.  StealthAUDIT for SQL uses this function to read and capture the audit activity events.

Step 2 – Create a Server Audit Specification

To create a server audit specification, right-click on Server Audit Specifications in SSMS under Security and choose New Server Audit Specification.  If you are interested in auditing both successful and unsuccessful server logons, you need to add both of those audit actions.  Since multiple server audit specifications can be created, it is considered a best practice to group similar activities and create multiple server audit specifications.  Each of the audit specifications can be independently enabled or disabled.  By default, when a new audit specification is created, it is in the disabled state.  Again, either choose the default name or give it a meaningful name.  In the example below, I chose to name it Server_Logon_Audit since this audit specification is only monitoring successful and unsuccessful server logons.  For the Audit field, choose the Audit that was created in Step 1.  If you prefer to use scripts to create and enable server audit specifications, the script for this particular audit is shown below.

GO

CREATE SERVER AUDIT SPECIFICATION [Sbits_ServerAuditSpecification-1]
FOR SERVER AUDIT [Sbits_Server_Audit_Spec]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON)
GO

Step 3 – Create a Database Audit Specification

The final step is to create a database audit specification to audit user activity (INSERT, UPDATE, DELETE) on tables of interest (for example, tables containing sensitive data).  In SSMS, navigate to Databases à Security à Database Audit Specifications, right-click and choose New Database Audit Specification.  The script for creating this audit specification is also shown below and can be easily customized to add more objects to the existing audit or create new audits as required.

USE [SBITS]
GO

CREATE DATABASE AUDIT SPECIFICATION [Audit-Sbits_SBMSSQL2016]
FOR SERVER AUDIT [Sbits_Server_Audit_Spec]
ADD (DELETE ON OBJECT::[dbo].[CARD_NUMBERS] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[CARD_NUMBERS] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[CARD_NUMBERS] BY [public])
WITH (STATE = ON)
GO

In the above example, I have set up INSERT, DELETE and UPDATE operations auditing on the dbo.ProspectiveBuyer table which contains PII data.  The activity by any user that is part of the public role will be audited and reported upon.

Microsoft provides numerous server-level and database-level audit action groups, in addition to a user-defined audit group that can be created to audit custom actions. A detailed explanation and a list of audit action groups supplied by Microsoft can be found here.

Now that you set up the required audit specifications for your servers or databases, run the data collectors in StealthAUDIT and start reporting on it!

To learn more about how STEALTHbits can help with auditing  your SQL database operations, visit our website: https://www.stealthbits.com/stealthaudit-for-sql-product