Data security is more important than ever. Some of your most important information resides within databases, so devising a sound database security and auditing strategy is a must. CSO published an article earlier this year listing the top 16 security breaches of the century based on how much risk or damage the breach caused. Out of these 16 attacks, databases were at the heart of at least 4, including the Heartland Payment Systems breach in March of 2008, the result of a SQL injection attack.
Microsoft provides great capabilities natively for monitoring the activity within your SQL Server environment, but you will need to know how to use them to truly be effective. In this post, we will take a look at 5 critical events that are integral to a proper SQL Server Audit strategy.
Understanding SQL Server Auditing
SQL Server Audits can be used to track security related events within your SQL Server Instance. For in-depth details on how to configure SQL Server Audits, you can refer to this article from Microsoft. For the purpose of this post, the configuration process can be simplified to the following three steps, each of which can be done through both SMS <acronym>SQL Management Studio</acronym> and T-SQL:
- Create a SQL Server Audit Object
- Create a SQL Server or Database Audit Specification
- Enable the SQL Server Audit
SQL Audits offer an extensive list of database and server level events that can be audited. This includes anything from a failed login to a change in database configuration.
One of the biggest challenges that organizations face is creating an appropriate audit strategy that meets compliance while also not adding any significant performance overhead to their SQL Server infrastructure. It may sound tempting to simply audit everything – this way you can’t possibly miss anything, right? Well, organizations must choose wisely what they are going to audit, because the more events being audited, the more data being stored, the slower the SQL Server is going to perform. Which specifications are the most important? Let’s take a closer look into the 5 that should be part of any SQL Audit strategy.
5 Must Have SQL Audit Specifications
This event indicates that a principal tried to log on to SQL Server and failed and is typically a MINIMUM requirement for any audit. Having this information is not only important to identify who is actively connecting to your databases, but also in raising the alarm in the case of an attempted, or even successful attack.
Role Member Changes
These events are raised whenever a login is added or removed from a fixed server or database role respectively, providing an accurate and up to date understanding of the privileged users throughout your SQL Server Instance.
These events are raised whenever any database object is created, altered or dropped. Although this can result in a large number of audit records, this is often required by most audits.
Database Principal Changes
This event is raised when principals, such as users, are created, altered, or dropped from a database. Similar to the ROLE_MEMBER_CHANGE_GROUP, this event provides an accurate understanding of who has access and where within your SQL Server Instance
In addition to carefully choosing the events to be audited, it is important to audit both successful and unsuccessful events. This will not only help you determine who is using and/or abusing their privileges, but will also allow you to track potential failed attacks.
Audit your Audits!
It can be argued that the most important audit specification to enable within your organization is the AUDIT_CHANGE_GROUP, which is raised whenever any audit is created, modified, or deleted. This is important because it ensures that no one has tampered with what is being audited, which is often a necessary requirement from auditors. It’s not uncommon for a DBA to disable auditing if they notice the SQL Server performance decreasing, and often times, these administrators will forget to re-enable these audits. Organizations need to make sure to audit their audit implementations to not only make sure admin miscalculations do not lead to increased vulnerability, but also to highlight activities performed by privileged users who knew to disable auditing prior to doing what they wanted to do.
StealthAUDIT is here to help
As part of STEALTHbits’ comprehensive Data Access Governance suite for structured and unstructured data, StealthAUDIT for SQL automates the process of understanding where SQL databases exist, who has access to them, how they obtained access, who or what is leveraging their access privileges, where sensitive information resides, and how each database has been configured.
With StealthAUDIT for SQL, organizations can:
- Identify where and what kind of sensitive data is stored within their SQL Server infrastructure
- Understand who has access to the data through detailed permission and role analysis
- Monitor the activity occurring within their SQL Server Instances through the collection of SQL Server Audit events
- Report and alert on sensitive data access and activity
With visibility into every corner of Microsoft SQL Server and the Windows Operating System it relies upon, organizations can proactively highlight and prioritize risks to sensitive data. Additionally, organizations can automate manual, time-consuming, and expensive processes associated with compliance, security, and operations to easily adhere to best practices that keep SQL Server safe and operational.