SQL Server 2016 – Always Encrypted and the GDPR

The European General Data Protection Regulation (GDPR) is coming, bringing new rules about the protection of Personally Identifiable Information (PII).

For those of us in Europe – or wishing to sell software products and solutions within Europe – this is a big deal. It’s going to mean an increased focus on data protection, and the potential for large penalties in the case of data breaches. Those penalties are also increased if we can’t show that we have taken sufficient measures to protect that data.

This affects us where we host applications and data for our clients, but also when we sell software to clients to use on-premise. They are going to want to be sure that the data in those applications is as secure as possible.

Security infrastructure and data governance are going to be of critical importance to everyone, but for software vendors it seems likely that Encryption is going to be a keyword.

In terms of the UK and Brexit, the British government has confirmed that the Brexit process will not affect the commencement of the GDPR in the UK.

The new regulations come into effect in the UK on the 25th May 2018. In the world of development lifecycles, roadmaps and deployments, that’s not a massive amount of time. So wouldn’t it be nice if there was something reasonably easy to implement that we could use to protect our clients’ data.

Transparent Data Encryption (TDE)
In SQL Server we already had Transparent Data Encryption.

TDE works by encrypting all the data for a database on disk – so if someone steals your data files, or backup files then they can’t read your data.

What TDE doesn’t do is keep data encrypted as it’s in memory or passed across the network to a requesting application. Also, if you have been granted access to the database then you will be able to read the data with no issues. That may not seem like much of a problem if you have our security locked down correctly, but usually you at least need to have a DBA who has system admin rights over the database – that DBA could abuse their vast privilege and power!

TDE is also an Enterprise edition only feature. To put that in context, if you currently have an application running on standard edition and which is fully provisioned with the 24 cores you are allowed to run on standard edition, and if you want to implement TDE, then it would cost you $5,000 per core to upgrade to Enterprise – that’s $120,000 – before you can go ahead.

Enter Always Encrypted
Always Encrypted offers a solution to these issues. You can choose to encrypt data on a column by column basis. The data is encrypted by the application before being sent across the network and remains encrypted as it is stored in SQL Server (both in memory and on disk). When it’s retrieved, it remains encrypted until it gets to the client application where it can be decrypted and used.

This is achieved by use of an Always Encrypted Certificate which is only stored on the application server, i.e. the database server has no access to it. Even under the covers SQL Server can’t read your encrypted data without the certificate.

So pesky DBA – your evil plans are foiled!

Seriously though, it’s unfortunately rather common for people to have levels of access to databases that they don’t need and shouldn’t have. Or worse, for access to granted to people through the use of the built-in Sa account – whose password is rarely changed.

To use Always Encrypted you need to be on SQL Server 2016 (or higher), and your application must be using at least version 4.6 of the .NET Framework.

From SQL 2016 SP1, Always Encrypted is a feature available in the Standard and Express editions – so that makes it free for users of those editions, not just those on Enterprise.

As I’ve said about a few of the other new features in 2016 – this isn’t rocket science – this isn’t an amazing new technology that’s going to take us to the stars. What it is though is, easy to use, effective, and minimal effort to implement. It also, once set up, doesn’t require developers to remember to do anything like sending data through a third party library to ensure encryption remains. In terms of hitting an imminent deadline for Data Protection without vast expense – this could be rather handy.

There are of course limitations, impacts and a few things in general that are worth understanding.

Initially I planned just to write a single post on this subject. It was quite straightforward to go through the process of setting up an Always Encrypted test – but once that was done I found I started to ask myself a lot of “what ifs”. I then found that by delving deeper, and understanding what was actually going on with Always Encrypted in a bit more detail at a lower level, that these questions started answering themselves, and that some of the limitations of Always Encrypted made perfect logical sense.

So we’ll have a look at some of that in the next few posts.

4 thoughts on “SQL Server 2016 – Always Encrypted and the GDPR

  1. For operational systems where access patterns are tightly constrained by the needs of the application storing the data in an encrypted form is not going to be a problem.

    Any thoughts on how does this work in a data warehouse scenario?

    1. I’ll start with a caveat, I’m not a lawyer and I haven’t read the regulation in full (261 pages, you can find it here http://data.consilium.europa.eu/doc/document/ST-5419-2016-INIT/en/pdf). Encryption isn’t the be all and end all in terms of meeting the regulation, but it is is a step that is going to help a lot. Also very important is going to be data governance and general security process. There’s a lot that isn’t new, but what is the new is the size of the statutory penalties – which are potentially huge.

      That said, I’ve had a search and I don’t believe there is any fixed required that says you HAVE TO encrypt your data. Wherever it is referenced, it is usually in the context of something we should strongly consider, but practicality and cost are allowed to be factors in making a decision. Overall though it seems to me, that it is about taking measures to make sure no one gets their hands on people’s data that we hold in trust, and the penalties may be proportionate to the efforts we have taken to protect it.

      Something else that is key, and particularly relevant for data warehousing, is data retention, and the use of data for purposes other than that for which it was originally gathered. I would think that in general, data warehousing of Personal Identifiable information will require a strong justification and may require explicit consent for the uses the data will be put to: http://gdpreu.org/the-regulation/key-concepts/consent/?gclid=CNihkfit-dMCFUe17QodW2wJDg. Again though, this isn’t really anything new, it just may be more tighlty enforced. Also I would suggest that access patterns for the warehouse will need to be as tightly controlled as those for the application.

      At the end of the day, this is going to be a job for the lawyers to work out what we are and are not allowed to do, and there will have to be an assessment of risk, no system is perfect that has people involved.

Leave a Reply