How to Enable SQL Server Auditing and View Audit Logs

What is SQL Server Auditing?

SQL Server auditing involves tracking and reviewing all events that occur on the database engine. It lets you create server audits containing server audit events and database audits for database level events.

There are several levels of auditing for SQL Server, depending on government or standards requirements for your installation. SQL Server Audit provides the tools and processes you need to enable, store, and view audits on various server and database objects. The result of the SQL Server Audit report is stored in event logs or files which can be reviewed as required to detect potential threats and vulnerabilities.

SQL Server Auditing Features

The first step is to determine what you want to audit based on the needs of your organization. For example, you might want to audit user logins, server configuration, schema changes and data modifications. After this, you have to choose which security auditing features to use. Useful features include the following:

• C2 Auditing
• Common Criteria compliance
• Login Auditing
• SQL Server Auditing
• SQL Trace
• Extended Events
• Change Data Capture
• DML, DDL, and Logon Triggers

Any one or a combination of the above features can be chosen based on the requirements of your organization.

Among these, C2 auditing and Common Criteria compliance are the most widely used international standards for SQL auditing.

C2 Auditing records information beyond SQL servers. For example, it would include who triggered the event and in which database, the server name, the event type, and the outcome of the event.

Common Criteria compliance enables residual information protection, the ability to view login statistics, and the table-level DENY taking precedence over the column-level GRANT.

Login Auditing monitors the SQL Server login activities and writes both failed and successful logins to an error log. SQL Trace is an event-driven monitoring tool that captures user activity. Change data capture records all the insert, update, and delete activities in the server table. DML, DDL, and Logon Triggers are used for auditing and regulating database operations.

How to Enable SQL Server Auditing

1. Open the SQL Server Management Studio.
2. Connect to the database engine for which you want to enable C2 auditing. In the Connect to Server dialog box, make sure that Server Type is set to Database Engine and then click Connect.
3. In the Object Explorer panel, right-click your SQL Server instance and select Properties.
4. In the Server Properties window, click Security under Select a Page.



5. On the Security page, you can configure login monitoring. By default, only failed logins are recorded but you can choose to audit Successful logins only, or Both failed and successful logins.
6. Check Enable C2 audit tracing under Options.
7. If you want to enable C2 Common Criteria Compliance auditing, check the Enable Common Criteria compliance option.

Note

Common Criteria (CC) Compliance is a flexible standard that can be implemented with different Evaluation Assurance Levels (EALs), from 1 to 7. Higher EALs have a more demanding verification process. When you check Enable Common Criteria compliance in SQL Server, you are enabling CC Compliance EAL1. It is possible to configure SQL Server manually for EAL4+.

Enabling CC Compliance changes SQL Server behavior. For example, the table-level DENY permissions will take precedence over column-level GRANT, and both successful and failed logins will be audited. In addition, Residual Information Protection (RIP) is enabled, which overwrites memory allocations with a pattern of bits before they are used by a new resource.

8. Click OK.
9. Based on the selected options, you might be prompted to restart SQL Server. If you get a message to restart, click OK in the warning dialog. If you have enabled C2 Common Criteria Compliance, reboot the server. Otherwise, right-click your SQL Server instance in Object Explorer again and select Restart from the menu. In the warning dialog, click Yes to confirm that you want to restart SQL Server.

How to Create a Server Audit Object

The following steps explain how to create a server-level SQL Server audit object:

1. From the SQL Server Management Studio, in the Object Explorer panel on the left, expand Security.
2. Right-click Audits and select New Audit. This will create a new SQL Server Audit object for server-level auditing.
3. In the Create Audit window, give the audit settings a name in the Audit name
4. Specify what should happen if SQL Server auditing fails using the On Audit Log Failure. You can choose Continue or choose to shut down the server. If you select Fail operation, database operations that are not audited will continue to work.



5. In the Audit destination dropdown menu, you can choose to write the SQL audit trail to a file or to audit events in the Windows Security log or Application event log. If you choose a file, you must specify a path for the file.
6. Click OK.
7. You will now find the new audit configuration in Object Explorer below Audits. Right-click the new audit configuration and select Enable Audit from the menu.
8. Click Close in the Enable Audit dialog.

How to Create a Database Audit Object

To create a SQL Server audit object for database-level auditing, you need to have created at least one server-level audit object first.

1. Expand Databases in Object Explorer and expand the database on which you want to configure auditing.
2. Expand the Security folder, right click Database Audit Specifications and select New Database Audit Specification from the menu.



3. Under the Actions section of the dialog box, use the dropdown menus to configure one or more audit action types, selecting the statements you want to audit (for example DELETE or INSERT), the object class on which the action is performed, and so on.
4. When finished, click OK and then enable the audit object by right-clicking it and selecting Enable Database Audit Specification.

How to View SQL Audit Logs

C2 Audit SQL Server audit logs are stored in the default data directory of the SQL Server instance. Each log file can be a maximum of 200 megabytes and a new file is automatically created when the limit is reached.

A native solution that is recommended to view SQL Server audit logs is called Log File Viewer. To use it, do the following:

1. In SQL Server Management Studio, under the Object Explorer panel, expand Security. Right-click the audit object that you want to view and select View Audit Logs.
2. In the Log File Viewer, the logs will be displayed on the right side. Regardless of whether the logs are written to a file or to the Windows Event Log, Log File Viewer will display the logs.
3. At the top of Log File Viewer, you can click Filter to customize which log entries are displayed. SQL Server file logs are saved in .sqlaudit format and are not readable, so Log File Explorer allows you to click Export to save logs to a comma-delimited .log file format.

How Lepide Auditor Helps Audit SQL Server

The Lepide SQL Server Auditor provides you with a simple means of seeing who, what, where and when changes are made to your SQL servers and automatically sends you real-time or threshold-based alerts and detailed reports when needed. It even helps you keep track of the overall health of your SQL Servers.

To Enable SQL Server Auditing using the Lepide Solution

The native way to enable auditing is time consuming as it involves several steps as described previously in this article. Enabling SQL server auditing using the Lepide Solution is a very straightforward one step process:

• From the settings screen, under Actions select Start Audit:

SQL Server auditing will now be enabled

With the Lepide Solution implemented into your organization, all aspects of SQL server can be audited with ease. All changes made to the configuration of SQL users, permissions, logins, triggers, databases, and other SQL elements are audited. The raw logs are aggregated into meaningful audit data, saved long-term in a SQL Server database, and displayed in predefined audit reports. You can customize these reports to create a long audit-trail to understand how a change was made and to track a specific critical change.

Lepide SQL Server Reports

Here are two examples of SQL Server Reports from the Lepide SQL Server Auditor. This first is the All Database Object Modification Report and the second is the All Server Object Modification Report.

The information these reports show includes Who made a change, When it was made, the specific Operation carried out and Where from.

The reports can be filtered, grouped, saved and exported.