How to protect sensitive data in SQL Server

Protecting business data is a relentless task and brings various consequences when mishandled. True costs on security breaches may result in regulatory fines – that are amplified over time, which may result due to non-compliance and also customer confidence. In the process of protecting “something”, the key component is knowing what is that “something” before protecting it, so what is sensitive data after all?

Sensitive data is a term that refers to personal and identifiable information, which also expands to business data that can be considered as private, and as such, if improperly revealed can jeopardize the privacy, social, and economic status of an individual or organization. Subjectively, sensitive data is any information that the keeper does not intend to share publicly, while regulation guidelines have an objective approach defining it via respective frameworks.

In this article, we are going to explain sensitive data protection in SQL Server, where the starting point in data protection is awareness of data existence, so in the next section, we will explain how to discover it.

Sensitive data discovery

Sensitive data protection in SQL Server starts with the discovery and determination of sensitive information. The data movement within an environment can hurt the data integrity accidentally without knowing sensitive data existence during any changes in a database or creating the new one.

Discovering where sensitive records reside in an enterprise without automation is an ambitious task, even if that can take a horrendous amount of time revealing it for multiple SQL Server versions, it is also error-prone. The easiest way to understand where sensitive information can be found is to utilize a tool like ApexSQL Mask for automatic identification and classification of all sensitive information in a database

ApexSQL Mask data masking and classification

This solution will help in identification, classification, and masking sensitive data in the SQL database. The next steps introduce how to control, secure, and monitor data access and movement that is understandably easier to perform once the data has been identified.

Tracking sensitive data access

The next step for building sensitive data protection in SQL Server is to define the policies to particularly monitor and control how the data is being approached and consumed. In the simple speech, it may incorporate whitelisting logins, applications, and services that are planned to access data regularly, and that way, easily detect any undesired data accesses, also as lifting up any extra permissions to any users with needlessly high rights of access.

Upon creating policies and rights of access, the next step is to investigate and follow all the activities in a database as a part of validation if the pre-emptive measures are correctly set and secure. Users with excessive access and those who can access databases directly are the most important to be tracked, while there are many different ways of monitoring database activity, where native auditing mechanism costs are high due to configuration and maintenance time investment, the easiest and the fastest way to track database activity is by leveraging on ApexSQL Audit.

ApexSQL Audit is a comprehensive auditing tool capable of tracking around 200 types of SQL Server events, a solution built to easily pass regular audits, and represents a great choice to track access, changes, and security on a database, objects, and data. While it is available to track everything known in a database, the configuration may be customized to follow the exact rules and policies defined beforehand in almost no-time.

ApexSQL Audit provides easy to use and understand approach while creating filter conditions under which auditing is performed. Highly granulated filters enable any specific auditing criteria to be defined, while advanced filter type brings unlimited accuracy and granularity. The below example shows simple filter conditioning where some tables with sensitive information are set to be monitored on DML manipulation (Insert, Delete, Update) and reading through queries. This type of auditing would be the necessary minimum in sensitive data protection in SQL Server:

Sensitive data protection in SQL Server - auditing configuration

Now once auditing filters are applied to track data access and changes done by a specific user via certain application against a specific set of tables as shown in the configuration summary:

Auditing configuration summary

We can get reports and alerts running automatically to demonstrate who, how, and when data is accessed and act accordingly on any suspicious event:

ApexSQL Audit auditing report

Data access report

Data change report

Database auditing can initially help in creating a baseline in expected activity behavior and help with spotting any unusual and unsuspected activity. ApexSQL Audit is a great asset to raise awareness of any activity that occurred in a database. An alerting mechanism in ApexSQL Audit provides the possibility to create alert rules to raise an alert with an e-mail notification whenever an event worthy of attention occurs.

Protect data from unwanted access

Database auditing and alerting will provide a wide range of information about database activities, looming threats, and anomaly behavior. With that capability of knowing what is happening with database and sensitive data, you can take action to avoid data breaches. There are few activities that are commonly used to protect data:

  • Data obfuscation

    Various encryption techniques are considered as a form of data obfuscation; in some cases, data encryption is less preferably accepted due to processing overhead, schema modifications, query optimization, and overall performance overhead, while in some cases it would’ve been overkill.

    Masking sensitive data in the SQL database is a perfect data obfuscation method and is highly effective with no performance or maintenance costs to mimic the original data and cover its true nature. This level of protection is a great deal for developing, testing, and analysis purposes. With ApexSQL Mask it is easy to maintain the accuracy and integrity of the data while ensuring that masked data is production-like

    masking sensitive data in SQL database

  • Controlling access based on security policies

    Accordingly to previously defined policies, allowing access to whitelisted users and blocking it for specific users and groups will save time investigating and help to easier identify any intrusive activity. It can be achieved via the definition of user access rights on SQL Server, databases, and schemas

  • Preservation

    As sensitive data protection in SQL Server includes a set of actions as an ongoing process to monitor data access and activity, it is important to maintain and update security policies, manage alerting events and prevent any suspicious activities that can hurt data integrity


Sensitive data protection in SQL Server stats with discovering that sensitive data exists, how it is being consumed and accessed, and the techniques to easier discover, track, and protect data is fundamental for data protection strategy. Without automation of discovery and data tracking, there is no effectiveness and efficiency while creating a security wall around it. Considering that data protection is an ongoing task, reaching the sweet spot is easy as discover, monitor, act.


May 15, 2020