SQL Server Blog

Enhancing SQL Server Security: Reviewing and Optimizing Audits

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

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:

,	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
SQL Server audit

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.
SQL Server audit output

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
	,	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 
Database audit specification review script output

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

	,	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
server level audit specifications

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.

	, 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!

David Seis
Article by David Seis
David Seis is a Support Technician that joined Straight Path in the summer of 2021. His goal is to work in an area that he believes helps others, and to do it to the best of his ability. He loves to help, solve problems, and make things easy for others to understand. Because of this he is lovingly known as a “Good helper” among friends and family. Outside of work, he has a growing passion for cybersecurity, specifically helping protect those who are most vulnerable to fraud, scams, and phishing. He looks forward to learning more about each of his clients, helping minimize issues, and ensure that any problems are solved quickly.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This