In the current digital age, security controls and data privacy and protection has been an influential factor in how organizations over the globe store and protect business data, especially since the EU General Data Protection Regulation (GDPR) establishes a new bar for compliance, security, and privacy rights of the individuals.
To comply with the GDPR, significant efforts are required to understand how to manage data and protect it. This article is meant to help with addressing data compliance and management under the GDPR principles for SQL Server on-premises.
Introduction to GDPR requirements and indications
As of May 25, 2018, the GDPR took effect that sets numerous regulation acts for security, privacy rights, and compliance to obligate all organizations around the globe that collect, process and store personal data of the EU citizens.
In other words, this means security controls and auditing mechanisms for organizations that collect personally identifiable information (PII) have to be elevated to demonstrate compliance with GDPR.
Personal data in the context of this regulation may include, but it is not limited to the following:
- Name
- Address and location
- ID number
- E-mail address
- Bank account details
- Medical records
- IP address
- Cookies
- Social Media posts
Or any other piece of data that can trace an individual which is considered as private, and in case of revealing this information and jeopardize the privacy, economic, and social status of an individual. So, the main purpose of the GDPR framework is to make sure that data collectors and controllers implement control measures around the handling data process, where some specific requirements are implicated via the following articles:
-
GDPR Article 5: Principles relating to processing of personal data
- Process data transparently, lawfully, and fairly
- Process in a manner to ensure appropriate security and protection from accidental loss, destruction, or damage
- Data controllers shall be able to demonstrate compliance
-
GDPR Article 24: Responsibility of the controller
- The data controller is tasked to demonstrate that processing is performed following GDPR
-
GDPR Article 25: Data protection by design and default
- Handle data protection via pseudonymization
- Include management controls integrated into processing
- Accessibility control – how and by whom data is approached
-
GDPR Article 32: Security of processing
- Protect data via pseudonymization and encryption
- Restore the availability and access to personal data promptly, in the event of a physical or technical incident
- Empower a process for regularly testing and assessing the effectiveness of security controls
-
GDPR Article 30: Records of processing activities
- Maintain a SQL Server audit data trail of processing activities
- Log and track operations
-
GDPR Article 33: Notification of a personal data breach to the supervisory authority
- Detect data breaches and notify in supervisory authority in less than 72 hours
- Describe the consequences of the data breach and measures to address it
Getting started with the GDPR in SQL Server
In this article, we are about to look into challenges and the highlighted GDPR specifications and explain how to achieve them via ApexSQL Audit that covers the SQL Server auditing and reporting needs combining with the ApexSQL Mask that adds up to pseudonymization, data masking, and classification.
To begin with the GDPR expedition in SQL Server, it is essential to put the focus on the following fundamental steps to create a sound strategy:
- Identify personal data – discover where personal data resides and what type of data is processed
- Control and supervision– administer how personal and sensitive data is accessed and used
- Monitor and protection – employ mechanisms to detect and react to improperly data processing
- Report and review – Prepare and store documented audit information, notify supervisory authority on data breaches and constantly review data to improve data management
Identify personal data
SQL Server databases are the fundamental piece of IT infrastructure that store data within and from where data flow starts moving throughout the separate systems in an organization. This implicates that data protection within a database has to be addressed first, and the identification of personal and sensitive data is the first step in the process.
Microsoft SQL provides multiple different mechanisms to discover personal data on a database level, the most primitive approach is by querying metadata “sys.columns” to find a potential match with the column name that indicates there’s personal data stored therein. For the more advanced search and dig deeper to data level search, Full-Text Search is a comprehensive way to perform a rule-based searching and create a framework to identify personal data records.
In an enterprise organization with high infrastructure complexity, regular business procedure updates, and constant changes in data movement and growth, the native approach can be helpful to a certain degree but it is also error-prone, hard to operate and maintain in a long run. ApexSQL Audit is designated to identify sensitive data via rule-based mechanism and track data activity and accessibility throughout the SQL Server auditing process
While this level of data identification represents a pre-step to configure and implement SQL Server auditing and helps to identify data on-the-fly, the extended options to search and classify data in an automated way based on a set of rules that are easy to manage and expand as the organization’s requirements change and update
The discovery stage engages in the identification of the potential attack surface and helps with answering: “Who can access what type and data?” “How specific database areas can be accessed and inappropriately used?”. Answering these questions and acknowledge where the personal data resides and how it is being accessed, it is easier to create a map to visualize data access and exploiting points. Implementing security and control measures moving forward with this information at hand will run smoothly, which is about to be explained and detailed in the second step of this guide.
Control and supervision
When data locations are revealed, and access points acknowledged, controlling access to data is the next step in the run. It is important to practice and employ the principle of least privilege at the proper degree of granularity to control how data is being accessed, and by whom.
Authentication
Ensuring that only legitimate users can access the database, SQL Server authentication helps to validate credentials information through the process. While each authentication type has it’s own pros and cons, Windows authentication is considered as best practice hence it provides:
- central management of security policies and SQL Server principals
- password administration that requires a certain level of password complexity, password expiration, and more
- Ease of administration via centralized system (Active Directory) to define permissions and level of access windows users and groups
Authorization
Creating a proper authorization policy is does not only simplify the access controls and management, but it also provides the ability to set permissions to implement the separation of duties model.
The idea is to follow the rules of the least privilege principles and assign the least required permission for a particular user or application to perform their respective tasks. The recommended practice is to employ role-based permission granting model, therefore permissions can be granted for server-level roles to manage server security and access, also as database-level to manage database-level security. With this approach, it is easy to maintain access policies hence users will inherit permission sets from the assigned roles, which enables them to easily add and remove users to shape the level of access as the requirements change.
SQL Server auditing
As implicated in Article 5: Principles relating to processing of personal data, the data should be processed fairly and transparently, while ensuring data resilience to accidental loss, unauthorized access, and data destruction. To meet these requirements, it is essential to employ monitoring so the data processor could demonstrate the aforementioned specifics are met.
ApexSQL Audit is capable of auditing almost 200 different SQL Server events which makes it a perfect match to help with demonstrating specifics from Article 5, graph 1 both SQL Server and database level configuring should be configured to audit the least of operations for:
-
SQL Server level:
-
Database level:
ApexSQL Audit is an out-of-the-box and compliance-ready tool to help with common challenges that SQL Server auditing and compliance regulations are trending, in other words, this tool brings a lot of pre-defined configuration and reporting templates which makes the auditing process simplifier. In the context of this article requirements, security-related templates for reporting can be used to demonstrate:
-
SQL Server logon activity:
-
Unauthorized access:
Taking into account article 24: Responsibility of the controller and requirements to demonstrate the data is processed in compliance with the GDPR, data auditing configuration should include the following operations that can be easily set via a pre-defined configuration template
-
SQL Server:
-
Database:
A data controller should demonstrate and continuously invest in analyzing data to reduce invalid events and be able to describe the consequences of the events violating compliance regulatory, so the complete auditing report is a handy tool to address the SQL Server auditing data trail specifications:
In the context of the GDPR Article 25, the data must be protected by default, and that principle followed by data pseudonymization and minimalization must be addressed when auditing is performed to meet GDPR requirements. ApexSQL Audit is designed to obfuscate values and store it as such, instead of true data values, the notation of obfuscating parameters is used, here is an example:
Row-level security
This is yet another and the ultimate way to control data access but is also a management tool for data flow assessment. Row-level security (RLS) by no means decreases the surface area of data access according to specific user characteristics. For example, the account manager can read the personal data records for his assigned customers only.
Considering the “security of processing” requirement from the article 32, with employing row-level security we can ensure that the risk of “unauthorized disclosure of, or access to personal data” is reduced. For more detailed information about how to get started with RLS, consider visiting this Introduction to Row-level security in SQL Server article.
Monitor and protection
The previous two sections were focused on ways to discover personal data, how to control access to it, and demonstrate that reliable measures are in place following the requirements. In this section, we are going to talk a bit more about the data itself, how to create SQL Server audit trail, and protect data in transit and rest.
As both GDPR articles, 25 and 32 propose to include the proper level of data security by default and design, protect data via pseudonymization and encryption, and to ensure data movement and access is monitored and well documented, here are the recommended practices to consider building safeguard data strategy:
Dynamic data masking
Dynamic data masking (DDM) is a stellar approach to render unrealistic data to the non-privileged users and cover true information for any piece of data that is considered sensitive.
This technique is an effective way to mask data on the fly, whenever a user or application without privilege access and read original data, the query result set will retrieve fake data. DDM can be a handy tool to perform testing of any kind without exposing real data to a tester or an application with no access to the original data.
ApexSQL Mask is a masking and sensitive data classification tool that provides an easy and maintainable way to dynamically mask data at column and row levels. This tool leverages 220+ pre-defined data masks to either replace or hide original data, but there can always be more by adding custom mask rules to be considered throughout the process:
Database encryption
Data processors and controllers should consider implementing encryption to meet the requirements of “Security of processing” that is implicated in GDPR article 32. Microsoft offers built-in features to help to address this requirement easier via
-
Transparent database encryption
Transparent database encryption (TDE) is a mature database protection mechanism that works for securing data in rest and is a straightforward solution to encrypt the entire database. TDE helps to physically protect sensitive data and performs encryption of the flat database files, associated transactional logs, and backups without implications on performance degradation or need to adjust other applications that are associated with the SQL Server
-
Always Encrypted
Always encrypted as a feature was introduced with the SQL 2016 version which makes it a relatively new implementation of database encryption. One notable advantage of this feature is data being encrypted in both – transit and rest. This feature is designed to protect sensitive data and designate who is the data owner and can read it, and who controls the data and have no access to it, which makes it a good solution to protect data from the users with high access privileges, such as database administrators
Database activity tracking – SQL Server auditing
Tracking database activities helps to review and analyze historical data trail to identify any potential threats or suspected data violations. Even though there are several Microsoft features available to perform this task, the configuration and data retrieval is complex and high to maintain the process within enterprises due to a various number of different SQL Server versions and the high number of instances to configure at once.
ApexSQL Audit is a SQL Server auditing solution built to easily manage and configure auditing for multiple instances in an easy to use and maintain manner. Regardless of the SQL Server version, ApexSQL Audit provides the most suitable technology available for auditing via support for SQL Audit, Extended Events, and Trace auditing technologies.
With a straightforward configuration, auditing can be easily managed and applied on multiple instances and their associated databases via both – custom and predefined auditing templates like GDPR:
Personal data access auditing
It is really important to track the access against personal data records in a database, and identify as early as possible any mishandled data breaches or violations. With ApexSQL Audit sensitive data discovery and configuration can be performed quickly, as implicated in this Configure sensitive columns auditing article.
Personal data can be discovered on rule sets that apply when performing the search, on top of 50+ pre-defined classification filters that come with the installation, the criteria can be expanded with additional customized filters:
On simple command to search the sensitive data, ApexSQL Audit will automatically mark sensitive columns to track access to it :
Personal data history access review can be achieved via reporting feature in ApexSQL Audit to reconstruct the following data output likewise
Documenting SQL Server audit data trail
Demonstrating compliance is an ongoing cyclic process that asks for a diligent preparation and proper documentation that is consumed during a regular auditing review processes, for both internal and external auditing reviews.
ApexSQL Audit reporting feature supports both exporting to multiple file formats, and automation. After the custom reporting templated is created or used a GDPR-ready template for reporting, and the export document can be easily shipped to Word, PDF, Excel, or CSV file formats on-demand or via the scheduled tasks that can push an e-mail notification with the report attachments automatically.
Report and review
Reporting and review SQL Server audit data trail is important to reduce risk, maintain the GDPR which compliance involves both transparencies in creating and retaining data about database activities and personal data access.
Considering the implications of Articles 30 and 33 to maintain audit data records and report on data breaches associated authorities, ApexSQL Audit enables to generate and create reports, provides an alerting mechanism to fire an alerting notification via e-mail to security authorities, ensure that the SQL Server audit data trail is communicated proactively, consistently, and timely.
In other words, ApexSQL Audit brings both reporting and alerting features to easily reconstruct and run reports and alerts, which is thoroughly explained in this Creating sensitive data reports and alerts article. Taking into account “Detect data breaches and notify in supervisory authority in less than 72 hours” implicated in article 33, when proper alerting rules are set, the attention to any suspicious activity can be raised in real-time via e-mail notifications when certain conditions are met:
Summary
Going through a significant process of validating if the organization meets GDPR principles and sustain it in the SQL Server environment, implementing ApexSQL Audit as a SQL Server auditing solution brings the organization several steps ahead while achieving GDPR compliance. Data integrity, maintainable and easy auditing configuration, compliance-ready reports documenting and automation, proactive, and real-time alerting mechanisms are a couple of notable features to mention in the context of this regulatory guideline.
Achieving GDPR compliance following this guide in the SQL Server environment, ApexSQL Audit, and ApexSQL Mask are efficient and significant tools to help to reach that goal and cover fundamental parts in pseudonymization, data masking, personal data tracking activity, and demonstrating compliance regulatory in SQL Server.
October 2, 2020
