Database auditing – control and monitor sensitive data access in SQL Server (4262762)

In the current cyberage, data protection and database auditing have been the number one priority for any organization. Control assessment procedures and techniques nowadays are heavily dependent on the compliance regulatory frameworks and their respective requirements.

This article is meant to dig deeper into this challenge and help with preparing proper techniques and control measures while monitoring access the activity in SQL Server databases via this 3 step notation:

• Discover – find where sensitive data resides in MS SQL databases
• Manage – employ control access measures
• Monitor – track database activity and monitor data access

Discover

The discovery process is arguably the most important step to achieve aspired results. Sensitive data as a term might be relative depending on the information type or data origin, but no matter the cause, any information can be considered as sensitive as long as the data holder wouldn’t like to share it.

With that said, compliance frameworks strive to describe and selectively explain what information type is considered as sensitive. Therefore, for many, sensitive data refers to personally identifiable information, but it can also refer to any business data when mishandled can hazard the privacy, economic, or social status of an individual or company.

In this database auditing guide, we are about to explain how to find sensitive data in the MS SQL database. An efficient search means to pin down data attributes that make it sensitive first. Microsoft SQL allows the employment of native mechanisms to perform a search on databases, starting with the most primitive by querying metadata on “sys.columns” to find matching results on the column names, but also the empowered Full-Text Search to perform a rule-based search on data-level. Full-Text Search, as a mechanism, allows more in-depth search analysis and helps to identify information by combining multiple rules at once, which makes it a strong candidate to perform this step.

With constant data movement and procedures updates in complex IT infrastructures, native solutions can help identify sensitive data to a certain degree, but it is also time-consuming and error-prone process. ApexSQL Audit is a database auditing tool that provides the capability to search for sensitive data easily under multiple rules at once for any database in the SQL Server environment.

One can choose from 50+ pre-defined criteria rules to easily identify sensitive data but can also expand the search criteria via creating completely new or adjusting the pre-defined templates:

Once the specific attributes are pinned and included in the search criteria, the database exploration begins. As a pre-configuration step to track sensitive data access in ApexSQL Audit, searching for sensitive data based on a defined rule set is employed via a quick and easy action set, likewise demonstrated below:

Now, when we know where sensitive data resides in the SQL Server environment, questions like “What tables store sensitive information? How do we control sensitive data access?” can be easily addressed. With this database auditing solution, revealing exploiting points in data access leads to easier access control management.

Manage

Control data access

Protecting sensitive information, and control who can access the data is a high priority. Once the access points are understood, defining how the data is accessed and how to control unauthorized access are control measures to consider next.

So, the principle of least privilege, a method to assign the least required permissions for SQL Server principles accessing sensitive information, is a practice to follow. The next two paragraphs are meant to shed more light on how to achieve it, followed by employing a database auditing mechanism to track activity and recognize any anomaly behavior.

Role-based authorization

It is important to make a plan that will provide both: access management and simplicity in defining authorization policies. With the role-based permission defining the approach, security policies are granted against specific roles, and therefore separation of duties is achieved while assuring granting policies are easily managed. Simple as it is, a certain user or group of users can be associated with a specific role, which is a map of security policies that picture the data access possibilities for associated users against the database.

Row-level security

After the database level policies are in place, row-level security is the next step in following the stellar security strategy. Row-level security allows one to enhance and polish the separation of duties model to a row-level degree. In simple words, it is a customized set of rules to define data access on the row level.

Let’s assume we have two different users associated with the financial database. Bank employees can access data by divisions, Employee_1 is associated with the EMEA region, while Employee_2 is associated with the AMER region, both of them can interpret query to view data in the same manner, but the results are different for each. Here is an example overview to help to understand the RLS works:

Controlling unauthorized access

Failed login attempts are commonly preceding unauthorized access, consider the fact that regularly mistyped credential information can trigger a faulting login attempt, we recommend to employ a database auditing mechanism so we can inspect what we expect.

Auditing failed login attempts

Regardless of the failed login attempt reasons, ApexSQL Audit provides a pre-defined reporting template to use and reconstruct complete information on failed logins against the audited SQL instance:

Taking into the consideration that activities with malicious intentions against sensitive information in a database bring high-risk consequences, it is highly recommended to employ an alerting mechanism to raise awareness whenever an unexpected event has occurred in a database. Therefore, ApexSQL Audit provides that capability with a comprehensive and real-time alerting via e-mail notifications whenever a specific event occurs.

In the example below, we configured alert rule to bring attention if any login other than “ApexSQLAuditDemo and sa” perform any database operation against “ApexSQLAuditDemo” database, or whenever a failed login attempt is recorded against the SQL Server:

Dynamic data masking

Dynamic data masking, as a feature, is available in Microsoft SQL Server 2016 and higher versions. The main purpose of this feature is to limit sensitive data exposure to unauthorized and non-privileged users, and therefore protect sensitive original information while serving the fake data instead.

It is complementary to database auditing, and row-level security, which means this feature is meant to be used while combining all three of them to achieve a higher level of data protection and data access controls. With ApexSQL Mask, which is a masking and data classification tool, dynamic data masking can be easily managed and define to whom original data will be exposed via easy to use and learn user interface:

To learn more about dynamic data masking in ApexSQL Mask, please consult How to mask SQL Server data using Dynamic data masking article.

Monitor – employ database auditing

Once the sensitive data is discovered, the control measures applied to follow the principle of least privileges and control data access, employing an auditing solution comes as the icing on the cake. Audit trail helps to inspect and understand database activity to prove if control measures are set up correctly or if there is still an area to improve it.

Creating a database audit trail can be achieved via multiple different SQL Server techniques, that are explained in this SQL Server database auditing techniques guide, and for this article, we will be focusing the out-of-the-box solution to track sensitive data access via ApexSQL Audit, as a proper and easy to configure auditing tool for this task.

Although sensitive data auditing has been already introduced in the Discover section of this article, we fell short to broadly explain how to perform the configuration steps and demonstrate the auditing trail details. Therefore, here is a quick guide on how to track sensitive data access and create reports.

Configure database auditing

SQL Server database holding sensitive data should be added to the auditing list first, and the Query operations are audited on the database so the data access against sensitive rows can be captured:

The next step is to switch to the Sensitive columns pane and perform the following steps to configure sensitive data access auditing on the rows holding such information:

1. Go to sensitive columns pane
2. Define search criteria
3. Run search
4. Columns that matched search criteria are automatically marked as sensitive and pre-selected in a result grid, add or remove certain columns
5. Hit apply changes

With this, sensitive data configuration is complete, and the audited data will be available in the audit trail reports.

Creating a sensitive data access report

The main purpose of employing database auditing is to create and run audit reports. ApexSQL Audit provides a various number of pre-defined reporting templates, including Sensitive columns auditing to reconstruct the audit log information relating sensitive data access only:

Sensitive column reports filters can be further customized to reconstruct auditing information per desired specifications, including this special control to select any specific column:

Once the filter customization is complete, the audit trail report can be quickly previewed in the application overview grid. The same output can be also exported into multiple file-formats on-demand, or via the scheduled jobs:

Creating sensitive data access alerts

Having a mechanism to work around the clock and validate database activity is the long arm of control measures to help identify any uncertainty in the environment. With the alerting mechanism in the database auditing solution, any control violation can be identified proactively and consistently. ApexSQL Audit provides this capability via real-time alerting to raise awareness about any specific event via e-mail notification.

Specific columns access alert can be easily set via the dedicated alert type for Sensitive data access alert:

ApexSQL Audit provides the capability to identify any specification and let you know about it. So, let’s set up an alert rule to trigger a notification when any user apart from a group of users should make transactions against certain columns:

With this, we are about to complete the circle of controlling and monitoring sensitive data access in the SQL Server environment. Employing database auditing to log, and review data, or raise alerts will significantly improve revision experience. To learn more about details specifics on how to create and run reports or alerts, consult this Creating sensitive data reports and alerts article.

Summary

Building a safe and sound strategy for control assessment and sensitive data management requires enough planning ahead. This guide was intended to help throughout the complete cycle while identifying the core techniques and how database auditing can help cover each pillar of this strategy. Over the course, every organization is challenged with changes in data movement and how it is processed, managed, and controlled. This cyclic approach is meant to address the challenge by repeating this act with positive consequences, Discover – Manage – Monitor.