Enhancing SQL Server Security: Reviewing and Optimizing Audits

Audits are a crucial component of SQL server that helps to ensure the security and integrity of your data. SQL Server built in auditing functionality, in particular, provides a robust framework for tracking and logging events occurring in the database engine. However, it’s not enough to merely set up these audits; regular review and optimization is necessary to prevent bloat and wasted resources.

Understanding Your Current Audits

The first step in optimizing your SQL Server audits is understanding what’s currently in place. SQL Server provides a graphical user interface (GUI) that allows you to easily review your current audit configurations. You can find it using the picture below, or the T-SQL statement provided:

SELECT 
a.audit_id
,	a.name as audit_name
,	a.create_date
,	a.on_failure_desc
,	s.status_desc
,	(s.audit_file_size)/100000 as audit_file_size_mb
,	f.log_file_path
,	f.log_file_name
,	f.max_file_size
,	f.max_rollover_files
,	f.max_files
from sys.server_audits a
Join sys.dm_server_audit_status s 
	ON a.audit_id = s.audit_id
JOIN sys.server_file_audits f
	ON a.audit_id = f.audit_id

The script above will help you to quickly review a few key points:

• Max file size: This indicates the maximum size that an audit file can reach before a new file is created.
• Max rollover files: This is the limit on the number of rollover files that an audit can create. Combined with max file size you can create a rolling log of a certain time period for review, the time period being dependent on how fast the files are growing. Some prefer to retain this data for years. In which case you should just use the maximum files set to unlimited and manage space a different way.
• Audit destination: This shows where the audit logs are being stored. Best practice is to ensure that these logs are going to a secure location that only the security review team has access to so that they cannot be tampered with.

One major note to be aware of – if you see the option checked in the GUI or returned in the T-SQL commands I shared for ‘on_failure_desc’ set to SHUTDOWN SERVER INSTANCE like in the example above, this is the action SQL Server will take if the audit fails to LOG an action. This is especially important to consider if you are recording logs over the network to another host since it can turn a network blip into an outage.

The next script helps to review what actions the audit is looking for, and in the case of database audits, what objects are included in the audit.

--Database audit specification review script
SELECT
	a.audit_id
	,	a.name as audit_name
	,	a.on_failure_desc
	,	da.name as database_audit_specification_name
	,	d.audit_action_name
	,	o.name
	,	o.type_desc
	,	da.is_state_enabled
	,	d.audited_result
FROM sys.server_audits a
LEFT JOIN sys.database_audit_specification_details d
	ON a.audit_id = d.database_specification_id
LEFT JOIN sys.database_audit_specifications da
	ON a.audit_guid = da.audit_guid
LEFT JOIN sys.all_objects o
	ON d.major_id = o.object_id 
WHERE da.name IS NOT NULL

The next script is for server level audit specifications, and what actions are being tracked:

SELECT
	a.audit_id
	,	sp.name as server_audit_specification_name
	,	on_failure_desc
	,	sp.is_state_enabled
	,	audit_action_name
	,	audited_result
FROM sys.server_audits a
LEFT JOIN sys.server_audit_specifications sp
	ON a.audit_guid = sp.audit_guid
LEFT JOIN sys.server_audit_specification_details sd
	ON sp.server_specification_id = sd.server_specification_id
WHERE sp.name IS NOT NULL

Last step is a quick query to see the top few results in an audit file, all you need to do is change the ‘C:\EncryptedDrive\*’ to the correct folder and file name root from the first script and you should be able to review what is in the files like in the picture below.

SELECT DISTINCT event_time
	, server_instance_name
	, server_principal_name
	, b.name AS action
	, succeeded
	, database_name
	, object_name
	, [statement] 
FROM sys.fn_get_audit_file ('C:\EncryptedDrive\*',default,default) a
JOIN sys.dm_audit_actions b
	ON a.action_id = b.action_id

Now that you have to tools to identify what audits exist and what they are doing, it is in your hands to determine if every single audit you’ve found, and every action that they are tracking, is really necessary. The benefit for cutting back on the number of audits and auditable actions, as well as number of files and file sizes will be found through gains in performance, reclamation of storage, and simplification of review when the audits are really needed. Most would probably be replaceable with extended events as a lighter weight alternative, but that is a post for another time. As always, test these scripts and understand them before using them in production, and I hope this will help you improve your SQL Server security and performance!