Category Archives: Vulnerabilities

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!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.

Is your SQL Server environment ready for GDPR? Part 1

Unless you have been living under a rock lately, you have not heard about GDPR. This has been hot topic in any technology website, blogs and social media network for the last past 2 weeks and even more today because this data regulation goes effective today (05/25).

In this post, I will do my best to condense the information about this topic and really focus on what is import, is your SQL Server environment ready for GDPR?

So, what is GDPR? It is a data regulation fundamentally about protecting and enabling the privacy rights of individuals it also establishes strict global privacy requirements governing how personal data is managed and protected, while respecting individual choice.

Personal data in scope of the regulation can include, but is not limited to, the following:

  • Name
  • Identification number
  • Email address
  • Online user identifier
  • Social media posts
  • Physical, physiological, or genetic information
  • Medical information
  • Location
  • Bank details
  • IP address
  • Cookies

So, if you think how this is going to affect my SQL Server environment or how I shall prepare …. don’t you worry Microsoft has it covered. I personally think Microsoft has done a wonderful job strengthening the security features of SQL Server since the last two versions (2016, 2017), there is multiple white papers, webinars or free online training you can check and also learn more about these new security features as always encrypted, dynamic data masking, row level security.

Because GDPR is a serious matter that involves larger companies across the world, Microsoft created some kind of workflow you may want to adop to make the GDPR compliance easy to follow up in your company. Here is the visual representation:

SQL Server provides multiple out of the box solutions that can be implemented to perform each of these recommended processes by Microsoft, so let’s take a look.

This step is about locating sensitive information and identify if that information qualifies as personal data according GDPR, you must answer the to the following questions:

  • Which servers and/or databases contain personal data? Which columns or rows can be marked as containing personal data?
  • Who has access to what elements of data in the database system? What elements and features of the database system can be accessed and potentially exploited to gain access to the sensitive data?
  • Where does the data go when it leaves the database?

SQL Server tools you can use during the discover phase:

    • SSMS – Vulnerability assessment, check my previous blog post about this SSMS new feature.
    • Querying sys.columns to identify column names which potentially contain personal information, you can also consider using Full text search to expand your search
    • Check if possible to disable some surface area features as XP_CMDSHELL, CLR, Filestream, Cross DB Ownership Chaining, OLE AUTOMATION, External Scripts, Ad-hoc Distributed Queries .

Once personal information is located and gaps in policies of data governance are identified during the discover phase, now its time to implement mechanisms to minimize risks from unauthorized access to data or data loss.

SQL Server tools you can use during the manage phase:

    • SQL Server authentication, there are two modes: Windows authentication mode and mixed mode. Windows authentication is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows, this is the best practice.
    • Create roles to define object level permissions, granting permissions to roles rather than users simplifies security administration. Permissions assigned to roles are inherited by all members of the role, and users can be added or removed to a role to include them in a permission set.
    • Use server-level roles for managing server-level access and security, and database roles for managing database level access.
    • Azure SQL Database firewall, at logical instance and database level. This way only authorized connections have access to the database, and align with the GDPR requirements.
    • Dynamic data masking, to limit sensitive information exposure by masking the data to non-privileged users or applications.
    • Row level security, to restrict access according to specific user entitlements.

Please stay tuned for the part 2 of this post, thanks for reading!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.