Storagepipe Is Now Thrive

GridWay Is Now Thrive

Data Protection

Final Preparation for the new GDPR Requirements

On May 25, 2018, the Global Data Protection Regulations (GDPR) will be law throughout the European Union. This means that if you do business in the EU, and subsequently store or otherwise process data about EU citizens, you are subject to the new regulations, and the significant fines associated with noncompliance. If you’re in that category, I hope this is not the first time you’re hearing of this news. If so, let this be just the tip of the iceberg of research and consultation you will undertake, rather quickly, to ensure compliance.


While a SQL Server may store most, or even all the data types specified by the GDPR in your business, this particular blog, cataloging SQL Server features that can help with compliance is inadequate to cover the entire landscape of data governance. Your company’s data should always be sensitively handled, transmitted, disclosed, used, stored, retained, audited, monitored, and erased throughout its lifecycle in your business processes.

For the many companies who do not have exposure to EU regulations, this is a great opportunity to consider the purpose, scope, and intent of the GDPR as it serves as a useful framework for good data governance related to personal and sensitive data. While we in the US don’t have the same regulatory environment (yet) and may not risk the degree of government imposed fines, we know well from the constant drumbeat of news about data breaches that the market can be just as, if not more, punishing when we fail to keep trusted data safe.

With all that said, the upshot of the GDPR is that we should be practicing sound and thorough data governance relating to personal and otherwise sensitive data. Data protection is not a new requirement, and as such Microsoft is not, for the most part, racing to deploy new features specifically designed to address it. Many of the things we must do to comply with GDPR are things we should, and can do in SQL already. That said, Microsoft consistently responds to the market and worldwide regulatory requirements by frequently releasing new or improved security, auditing, and management features.

Here is a list of capabilities and specific features available in SQL Server for data protection, including some that are brand new in the last couple of releases:

Data Modal Discovery and Documentation

  • Query system tables, such as sys.columns to discover specifically identified data categories to be protected
  • Use Extended Properties to tag, document, and potentially manage sensitive data elements

Authentication Methods and Practices

  • SQL Authentication
  • Windows / AD Authentication
  • Azure AD Authentication
  • Use these to facilitate principle of least privilege for logins that are not shared among individual users, applications, and processes

Object-level permissions

Row-level or filter-based security (new feature)

Authentication auditing (new features and older capabilities available)

  • Auditing for Azure
  • Azure SQL Threat Detection
  • Extended Events
  • Triggers and custom event logging


  • Transparent Data Encryption: encrypts the entire data file on the disk (Enterprise edition only)
  • Column-level encryption: requires decryption in the application code
  • Transport Layer Security (TLS): encrypts in transit, but not at rest, and not in clients
  • Always Encrypted: a new feature that encrypts data at rest and in transit, with only a driver update or change and configuration rather than code change required
  • See my blog from January about various encryption features and their use cases

Data Masking

  • Dynamic Data Masking protects sensitive data even from analysts and others accessing data directly in SQL using SSMS and reporting tools (new feature)

Contained Databases (new feature)

  • Limits logins to single database context instead of entire SQL Server instance

Business Continuity

  • Backup and recovery
  • Clustering
  • Log Shipping
  • Always On Availability Group
  • Azure Geo-Replication

One subject of the GDPR that will provide a challenge using today’s set of capabilities is the right to erasure. Under this regulation, personal data must be erased entirely from systems upon request. While facilitation this in SQL Server directly will require only the effort of identifying the implicated data and creating the code and processes for removing it, a far larger challenge is ensuring that the data is removed from all retained copies and backups. At this time, Microsoft has not addressed this requirement. To comply it may be necessary to use something other than native SQL backups in your DR and data retention strategies.

Microsoft has published a helpful guide Microsoft SQL and the GDPR Requirements with more information and a specific approach for complying with the GDPR. I suggest that this guide be used to mitigate data breach or misuse risks regardless of EU regulatory exposure.

Want to learn more? Contact Thrive today!