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.