Understanding SQL Server Audit and Best Practices

SQL Server Audit

The SQL Server Audit object gathers individual occurrences of server or database-level actions and sets of actions for monitoring purposes. This audit operates at the SQL Server instance level, allowing for multiple audits per instance. Upon defining an audit, you designate the destination for result output.

How does SQL Server audit work?

Before beginning a SQL Server audit pay attention to the limitations and restrictions associated with database audit specifications. These specifications are non-securable objects housed within a specific database. Upon creation, a database audit specification defaults to a disabled state, requiring activation to commence auditing activities.

SQL Server Auditing

Auditing a SQL Server instance or database involves monitoring and logging system events. The SQL Server Audit object consolidates server-level or database-level actions and action groups for monitoring purposes, operating at the SQL Server instance level. Multiple audits can be configured for a single SQL Server instance. Within an audit, the Database-Level Audit Specification object is associated, enabling the creation of one database audit specification per SQL Server database per audit.

To create a server audit using SQL Server Management Studio:

1. In Object Explorer, expand the Security folder.
2. Right-click the Audits folder and select New Audit. For more information, see Create a server audit and server audit specification.
3. When you finish selecting options, select OK.

Auditing object: Ensure ‘SQL Server Audit’ is set to capture both ‘failed’ and ‘successful logins’

For AWS RDS Instances, if RDS has not been configured to write to an S3 bucket, this is a fail. Run the following T-SQL command:

The result set should contain 5 rows, one for each of the following audit_action_names:

• AUDIT_CHANGE_GROUP
• FAILED_LOGIN_GROUP
• SUCCESSFUL_LOGIN_GROUP
• SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
• FAILED_DATABASE_AUTHENTICATION_GROUP

Both the Audit and Audit specification should be enabled and the audited_result should include both success and failure.

To create a database-level audit specification or to to create a database-level audit specification, see HERE

View a SQL Server Audit Log

Permissions: Requires the CONTROL SERVER permission.

To view a SQL Server audit log

1. In Object Explorer, expand the Security
2. Expand the Audits
3. Right-click the audit log that you want to view and select View Audit Logs. This opens the Log File Viewer –server_namedialog box. For more information, see Log File Viewer F1 Help.
4. When finished, click Close.

Microsoft suggests accessing the audit log through the Log File Viewer.

Benefits of SQL Server Auditing

By opting for Audit instead of the conventional configuration within the Security tab to capture successful logins, we diminish the clutter in the ERRORLOG. This results in a more concise and manageable log for DBAs tasked with troubleshooting SQL Server issues. Moreover, the Audit object offers the capability to record to the security event log, although this requires operating system adjustments. This provides an extra avenue for storing login events, particularly when integrated with an SIEM solution.

SQL server attacks: mechanisms you must know

SQL Server Audit Vulnerabilities

Improper configuration of the audit feature can leave gaps in monitoring or expose sensitive information. For instance, failure to audit critical events or inadequate security measures for audit logs may leave systems vulnerable to exploitation.

Furthermore, misconfigured auditing settings might inadvertently capture sensitive application data or personally identifiable information (PII), introducing potential security and compliance risks if exposed.

SQL Server Audit Remediation

SQL Server Audit Remediation involves the systematic process of addressing and resolving identified issues within SQL Server audit configurations. However, manually addressing these concerns across multiple servers can be exceedingly time-consuming and prone to human error. Automated hardening solutions streamline the remediation process, enabling efficient identification and resolution of vulnerabilities at scale.

GUI Method

1. Expand the SQL Server in Object Explorer.
2. Expand the Security Folder
3. Right-click on the Audits folder and choose New Audit…
4. Specify a name for the Server Audit.
5. Specify the audit destination details and then click OK to save the Server Audit.
6. Right-click on Server Audit Specifications and choose New Server Audit Specification…
7. Name the Server Audit Specification
8. Select the just created Server Audit in the Audit drop-down selection.
9. Click the drop-down under Audit Action Type and select AUDIT_CHANGE_GROUP.

10. Click the new drop-down Audit Action Type and select FAILED_LOGIN_GROUP.

11. Click the new drop-down under Audit Action Type and select SUCCESSFUL_LOGIN_GROUP.

12. Click the new drop-down under Audit Action Type and select SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP.

13. Click the new drop-down under Audit Action Type and select FAILED_DATABASE_AUTHENTICATION_GROUP.

14. Click OK to save the Server Audit Specification.

15. Right-click on the new Server Audit Specification and select Enable Server Audit Specification.

16. Right-click on the new Server Audit and select Enable Server Audit.

T-SQL Method Execute code similar to:

NOTE: If the write destination for the Audit object is to be the security event log, see the Books Online topic "Write SQL Server Audit Events to the Security Log" and follow the appropriate steps.

SQL Server Audit Best Practices

Here are some best practices to follow when using SQL Server Audit:

1. Be selective with auditing. Balance auditing requirements with resource usage by only auditing critical events and accessing important data. Auditing everything can lead to excessive log sizes and performance impact.
2. Determine data retention policies upfront. Decide how long audit logs need to be kept to meet compliance needs. Set up a process to backup and purge logs.
3. Restrict access to audit logs and data. Audit data can contain sensitive information. Use database roles and permissions to allow access on a need-to-know basis.
4. Setup alerts and monitoring. Establish processes to collect, monitor, and review audit logs regularly. Alerts can notify on specific high risk events.
5. Follow principle of least privilege. Only enable auditing for necessary users and events. Minimize surface area for malicious actions.
6. Maintain audit integrity with SQL server Enable checksums to detect log tampering or unauthorized changes.
7. Backup the audit configuration. Keep regular backups of audit settings in case the audit system itself is compromised.
8. Segregate duties over audit data. Have separate teams own audit capture vs. audit review for checks and balances.
9. Test auditing regularly. Validate auditing is working as expected by injecting sample events and monitoring data.
10. Tuning and optimization. Tune SQL Server to balance auditing I/O and disk usage with instance performance needs.