Configuring the SQL Server auditing feature:
Database and Server level audit

Data security should be a key concern for organizations and users. Credential theft and privileged account escalation leads to data breaches. The SQL server audit feature protects your data from cyberattacks. Essentially, SQL server auditing is a legacy server audit feature that helps organizations comply with regulations such as the GDPR, HIPAA, SOX, and PCI-DSS. It monitors user account permissions misuse in SQL server database operations and alerts IT administrators to potential risks.

This article discusses setting up and using the SQL server auditing feature in the MS SQL server management studio. In addition, it focuses on how the SQL server audit feature helps the IT admins monitor all the data at the server and database level.

Requirements to perform the SQL server audit:

• A SQL server audit object and
• Either the server audit specification, or database audit specification.

For better control over auditing, it's advisable to go with one SQL server audit along with database audit specifications. This enables the IT admin to deactivate the database activity separately from deactivating the individual audit specifications.

How do you set up and use the SQL server auditing feature?

SQL server auditing can be created using the SQL server management studio GUI feature, or by using Transaction SQL commands.

Setting up a SQL server audit:

• Open the Microsoft SQL Server Management Studio.
• Expand the Object explorer sidebar -> Security -> Audits.
• Right-click and choose New Audit.

• In the Create Audit window,
  1. Give this audit action a name, and select the queue delay in milliseconds. By default, it's 1,000 milliseconds, but you can increase or decrease this value based on your preference.
  2. Following an audit log failure, you can opt to continue the audit, or shut down the server to fail the operation.
  3. In the audit destination field, you have three options to store the audit information: In the File, Security log, or Application log. Windows Event Viewer and security event logs are used to monitor the application and security events.
  4. If the chosen option is a file, select the file path and designate the size as unlimited.
  5. Finally, click OK.

• Now you can see the audit file name you created in the audits folder. By default it is disabled. Right-click on the audit file and select Enable Audit. A success message will pop up to confirm this action.

To enable a database-level audit:

Go the Databases menu, choose which database to audit, select Security -> Database audit specifications.

• Right-click and select new Database Audit Specifications.
• Now you can see the name of the database audit you created in the database audit specification listing.
• The audit, as well as the database audit that you created are, by default, disabled.
• Right-click the audit you created and then choose Enable Audit. A success message pop-up appears to confirm this action.

You have successfully set up database-level auditing.

When an intruder changes the database schema by performing data definition language (DDL) or data manipulation language (DML) operations, such as select, insert, update, delete, or drop table, all these database operations are carefully captured. To view all the auditing information, use this SQL query,

SELECT*FROM sys.fn_get_audit_file
{
< Give the chosen path of your file here >
}
GO

Server Audit Specifications

The Server-level audit specification monitors server operations, such as logon and logoff changes.

To enable the Server level audit:

• Navigate to Security -> Server audit specifications -> Right-click on Server audit specifications.
• Select the name for the audit you previously created from the dropdown listing.
• Choose the audit action type you prefer from the listing that displays: SUCCESSFUL_LOGIN GROUP, FAILED_LOGIN_GROUP, LOGOUT_GROUP, AUDIT_CHANGE_GROUP, and DATABASE_CHANGE_GROUP.
• Finally, click OK.

Next, you need to enable the audit by right-clicking on the defined server audit name and selecting the Server audit specification. Once you have performed the authentication events, check the audit logs to confirm whether all events are captured correctly or not.

Database Audit Specifications

The database-level auditing involves monitoring the database activities such as DML and DDL operations.

To configure the database audit specification, the user needs to have GRANT ALTER ANY DATABASE AUDIT, ALTER, OR Control permission selected in the audited database

After auditing the SQL server, the audit result shows the audit action_id, commands executed, changes in records, and other session details that prompted monitoring of the suspicious activity from the specific machine on the server.

Where should you store this audit information?

Securing and restricting the audit files access is a vital part of the SQL server auditing process. Ensure the audit files are kept safely on a separate server and restrict all other users, other than IT admin, from having access to it. Essentially, the audit information is stored in an application, security log, or in a text file. The auditing output is a log file, through which we can identify and track the potential risk to rectify the issue with ease. There are millions of other security log entries in a configured server, so it is not easy to filter them out. However, SQL server auditing events are stored along with the event ID. Event Viewer's filter and find feature help you to search for more specific log details through the event ID. Essentially, you need to preserve the audit log file securely for compliance use later.

How can auditing help find the root cause?

By performing the SQL server audit, all details of who made what changes in the data objects and from where are captured accurately, along with login attempts, application, and host information. This provides IT admins with better insights into all server activities so they can take prompt action to safeguard the server from external threats. A SQL server audit helps you proactively mitigate the risk of security breaches in the server. You should regularly audit the server to ensure all your organization's data is safe and secure from cyberattackers.