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:
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.
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.
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.
Zoho Corporation Pvt. Ltd. All rights reserved.