Microsoft SQL-based technologies to plan your journey to GDPR compliance

The General Data Protection Regulation (GDPR) is fundamentally about protecting and enabling the privacy of individuals. The GDPR establishes strict global privacy requirements governing how personal data is managed and protected, while respecting individual’s choice. How this can be applied to Microsoft SQL Server environments? There are several technological solutions built-in to Microsoft SQL-based technologies that can help us on our journey to GDPR compliance.

Microsoft recommends that organizations begin their journey to GDPR compliance by focusing on four key steps:

• Discover
• Manage
• Protect
• Report

Source

Discover:

This phase is to identify what personal data is being managed and where it resides. Also, to understand the attack surface area.

We can use following tools for discovery and classification:

1. SQL Vulnerability Assessment – added in the SSMS 17.4
   Vulnerability Assessment is supported for SQL Server 2012 and later, and can also be run on Azure SQL Database
2. SQL Data Discovery & Classification – added in the SSMS 17.5
   Data Discovery & Classification is supported for SQL Server 2008 and later.

Manage:

You can manage the access to personal data using the Microsoft SQL built in Authentication and Authorization mechanisms. Use Windows authentication on SQL Servers as it enables centralised management of SQL Server principals.

It is important because the GDPR Recital 39 talks about ensuring the security of personal data, “including for preventing unauthorized access to or use of personal data and the equipment used for the processing.”

Use role based security model as it provides flexibility to define permissions at a high level of granularity in Microsoft SQL, thus greatly reducing the attack surface area of the database system.

This covers the GDPR Article 25(2) – “Data protection by design and by default” limiting access to data by ensuring that personal data are not accessible without the individual’s intervention to an indefinite number of natural persons.

We can limit access to sensitive data using the following built-in features of Microsoft SQL Server.

1. Dynamic Data Masking:
   DDM is supported, starting from SQL Server 2016 (all editions) and for Azure SQL Database. It is easy to use, as it does not explicitly require any applications changes.Implementing this covers GDPR Article 25(1)— “appropriate technical and organizational measures, such as pseudonymization, which are designed to implement data-protection principles, such as data minimization, in an effective manner…”
2. Row-level security:
   RLS is supported, starting from SQL Server 2016 (all editions) and for Azure SQL Database.
   It covers the GDPR Article 32(2)—“Security of processing” that talks about “unauthorized disclosure of, or access to personal data.”

Protect

We can deploy security controls to prevent, detect, and respond to vulnerabilities and data breaches using following Microsoft SQL-based technologies.

1. Encrypt in Motion
   Transport Layer Security ensures that data is encrypted in transit, to and from the database.
   It addresses (GDPR Article 32(2), “Security of processing”). Protecting data during transmission in this context—to avoid possible leakage and minimize these risks.
2. Encrypt at Rest
   Use Transparent Data Encryption, SQL Server Enterprise Edition feature.
   This relates to the GDPR Article 32(2)—“Security of processing” that states “risks that are presented by processing, in particular from accidental or unlawful destruction, loss, alteration, [or] unauthorized disclosure of” data. In this case, the protection is at the level of the physical device—for example via copying the physical data out to another server.
3. Always Encrypted
   It addresses GDPR Article 9 and Article 32 (2)when dealing with highly sensitive data by encrypting it on the server side and even in-memory.
4. SQL Server Audit
   This is related to GDPR Article 30(1)—“Records of processing activities.”The GDPR requires that “Each controller … shall maintain a record of processing activities under its responsibility.”
5. SQL Server Always On
   This is related to GDPR Article 32(1)—“Security of processing” that talks about the business continuity “the ability to restore the availability and access to personal data in a timely manner in the event of a physical or technical incident”

Report

This phase talks about keeping required documentation, manage data requests, and provide breach notifications.

Microsoft SQL Auditing capabilities can serve as an essential component for fulfilling these requirements. Temporal Tables can also assist with reporting procedures. System-versioned temporal tables, in SQL Server 2016, designed to keep a full history of data changes and allow easy point in time analysis.

This addresses (GDPR Article 35(7)— Data protection impact assessment) “the measures envisaged to address the risks, including safeguards, security measures and mechanisms to ensure the protection of personal data.”

We can regularly review the security state of data and systems, to ensure they meet the standards expected by the organization by using Operations Management Suite (OMS).