Enabling C2 Auditing and Common Criteria Compliance

Have you ever wondered why auditing SQL servers is an important practice?? Let's understand this with the help of a scenario.

Consider a financial institution like an insurance company that uses the SQL server to store customer details. One of its employees, who is serving a notice period and is looking forward to joining a competitor firm, logs into the server during a non-business hour.There's a possibility of information misuse, since the log in happened at the wee hours.

Your SQL server would have logged this activity. However, there's a high chance of this outlier event going unnoticed in the overwhelming log data. This shows us the importance of continuously auditing the log data and looking for information that could be a potential threat.

There are different techniques of auditing the SQL server activities such as manual auditing, SQL server audit, SQL server triggers, SQL server transaction logs, etc.

The foremost step in auditing is to specify which events should be audited. For instance, you may audit user logins, data modifications, schema changes, etc. The next step is to choose ways in which you perform the auditing. Some of the methods are:

• C2 Auditing
• Common Compliance Criteria
• SQL Server Auditing
• SQL Trace
• Extended Events
• Change Data Capture
• SQL Triggers

In this article, we'll be discussing how to enable the C2 auditing and Common Compliance Criteria.

C2 auditing is a globally accepted standard to audit events like user logins, stored procedures, and the modification of objects. One of the major challenges posed by C2 auditing option is that it generates huge volumes of data and doesn't provide you with the option to apply a filter on what is to be audited.

Steps to enable the C2 auditing in SQL Server 2017

• In Object Explorer, expand Management.
• Expand SQL Server Logs, right-click any log file, and select View SQL Server Log.

ManageEngine's EventLog Analyzer, a comprehensive log management tool, audits both SQL server and SQL database. It provides out-of-box reports, real-time alerts and even an intuitive dashboard. You can drill down to the logs, filter reports, customize alerts, perform log searches, and archive logs for powerful and effective management of SQL Servers. Click here to know to more.

• Open the SQL Server Management Studio.
• Connect to the database engine for which you want to enable C2 auditing. In the Connect to Server dialog, ensure that the Server type is set to Database Engine and then click Connect.
• In the Object Explorer panel on the left, right-click your SQL Server instance at the top and select Properties from the menu.
• In the Server Properties window, click Security under Select a page.
• On the Security page, you can configure login monitoring. By default, only failed logins are recorded. You can modify this settings to audit just successful logins, or both failed and successful logins.
• Select Enable C2 audit tracing under Options.
• If you want to enable C2 Common Criteria Compliance auditing, select Enable Common Criteria compliance.
• Click OK.
• Based on the selected options, you might be prompted to restart the SQL Server. If you get this message, click OK in the warning dialog. If you have enabled C2 Common Criteria Compliance, 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.

Common Criteria Compliance

Common Criteria (CC) Compliance is a recent standard that overthrows the C2 auditing. It was developed by the European Union. You can enable this Common Criteria Compliance option in the Enterprise and the Datacenter editions of SQL Server 2008 R2 and later. The problem with this component is that it can impact the performance if your server doesn't have the sufficient specifications to cope with the extra overhead.

The CC Compliance is a versatile standard that can be implemented with different Evaluation Assurance Levels (EALs) ranging from 1 to 7. Higher EALs have a more demanding verification process. When you enable the CC compliance in SQL Server, you are enabling, you are enabling CC Compliance EAL1. You can configure the SQL Server manually for EAL4+.

Enabling CC Compliance can change the behavior of the SQL Server. For instance, the table-level ALLOW permissions will take precedence over column-level DENY permissions, and both successful and failed logins will be audited. Since the Residual Information Protection (RIP) is enabled, it will over-write the memory allocations with a pattern of bits before they are used by a new resource.

To obtain complete control over your SQL server, auditing is a must. But, this can be a tedious and time-consuming activity, considering the number of logs that get generated over a period.

Wouldn't it be great, if a single solution can analyze SQL Server logs and provide you real-time reports? EventLog Analyzer, a comprehensive log management tool, can help you with out of box reports such as SQL Server Auditing Report, SQL Server Trend Report, SQL Server DDL Auditing Report, SQL Server Advanced Auditing Report, and more.