Is your SQL Server environment ready for GDPR? Part 2

In my previous blog post of this topic, I talked about the definition of what GDPR is and also described the first two phases of Microsoft’s recommended workflow in order to be in compliance with this data regulation.

The Discovery and Manage phase was about discovering where the sensitive data was located and how it can be accessed also to create access controls to the system in compliance of the “least privilege” principle enabling only authorized access to the database system and data.

Here is the visual representation of Microsoft’s GDPR recommended workflow:

Let’s move forward defining and describing the the last two steps:


Here is where the data protection effort begins, such task requires proper security controls that are appropriate for the specific data types and usage scenarios.

In my opinion this is the most important phase in the workflow, because is where the data becomes the main focus. The application of protection solutions and monitoring mechanisms are crucial to protect the data.

SQL Server tools you can use during the protect phase:

    • Encryption, which can be applied at different levels (database, server, client)
      • Azure SQL database and Azure SQL Database Warehouse requires encryption for all connections
    • TLS, Microsoft currently supports the 1.2 version
    • TDE, this solution encrypts data at rest is often required by compliance regulations and various industry guidelines as a core requirement, as it ensures the full protection of data at the physical layer.
    • Always encrypted, allows to encrypt sensitive data inside client applications never revealing the encryption keys to the database layer. This is a very good solution when encryption at rest and on the wire is not sufficient.
    • Auditing for Azure SQL Database, tracks database activities by writing events to an audit log. It does require a storage account to save the logs for further analysis.
    • SQL Database threat detection, this is a built-in feature of Azure SQL Database which detects anomalous database activities indicating potential security threats to the database.
    • SQL Vulnerability assessment, part of SSMS starting from the 17.4 version. I have a dedicated blog post about this tool, it scans a SQL instance and its databases looking for data privacy standards.
    • SQL Server audit, tracks database activities and analyze and investigate historical activity to identify potential threats or suspected abuse and security violations.

This is the last phase of this workflow, deals with the continuous process of reviewing the controls and security of the system, to better ensure ongoing compliance with GDPR principles.

The basis for reporting relies on maintaining documentation and records, so we have a couple of options available to fulfill this requirement:

    • SQL Server audit, maintaining audit logs for all database activities ensures the existence of a persistent record of database access and processing activities at all times. These records can then be analyzed and packaged to provide evidence needed for various record-keeping requirements
    • Temporal tables, a new built-in feature starting from SQL Server 2016. System-versioned temporal tables designed to keep a full history of data changes and allow easy point in time analysis.

Once all the phases are complete, do not forget that the only way to achieving and maintaining compliance to GDPR is through regular checks of the security state of data and systems, to ensure that they meet the standards expected by the organization

Thanks for reading!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.