The thing that makes Encryption work

For this months TSQL Tuesday Matthew McGiffen asks us some broad questions around Encryption and Data Protection. His timing is great, because that’s exactly what I gave a talk about last month at SQL Saturday South Island. My timing is not so great. It’s Thursday. I could tell you about Tuesday, but I’m not sure you would believe me. So apologies for being late Matthew, thanks for the great topic which I keep telling everyone who will listen we all need to keep front of mind.

I’m going to start off in the past, where Encryption was a big scary thing for me. I didn’t get it. It was complicated and seemed like some hidden voodoo science that I just wasn’t smart enough to understand. One thing I’ve learnt though is that not understanding the intricacies of how something works is actually not that bigger deal so long as you understand what it is used for, when it’s appropriate to use and how to go about implementing it. Embracing my inability to know everything about everything is something I actually recognize as a big career turning point for me, and if it hasn’t been for you yet then I’ve got a thing called AI that we need to have a chat about.

At any rate, a little less distantly in the past I presented at SQL Saturday SOuth Island and did demo’s inside an hour on how to do the following forms of ‘encryption’.

  • WITH ENCRYPTION object ‘encryption’
  • Connection Encryption
  • Transparent Data Encryption
  • Backup Encryption
  • Always Encrypted Column Encryption

5 forms of encryption in an hour. When to do it, why to do it, and how to do it – take my code. But that wasn’t the point of the talk. Hopefully what people took away from the talk was that there is no technical barrier to them going away and implementing encryption in their environment straight away. The features are available in all paid editions of SQL Server, the process is really well documented, and they had just witnessed someone implement them all in a handful of minutes.

The stuff that really matter is the bits around the protection. What is the impact of encryption on your database size…your backup size…your ability to use third party dedupe products…your developers process…your compliance…your security. Each one of those things is a blog post in itself (oh good…no more wondering what to blog about next month…) but the one I want to focus in on is a human element and it’s something I have had to deal with directly in the past week (See reference to Tuesday above).

As employees we are often tasked with figuring out how to get something done quickly. We have an incredible ability to figure out the ‘how’ to a problem, and we live in a glorious age where the ‘how’ knowledge is readily and generously shared by a multitude of online genius types. So when someone says ‘encrypt this column’ it’s quite possible that a moderately competent IT professional can figure out the how, make that column encrypted and tick whatever compliance box their boss was worried about that week.

What we are often not so good about is documentation and knowledge transfer, particularly of an ad hoc task that took us a couple of hours several years ago. The biggest problem I have with encryption is that people change roles, and change jobs, and fall out of trees. When any form of encryption is in place, we run the risk of ending up in a state where data is irreversibly encrypted because the certificate is lost – or we have a backup, but not a private key or a decryption password. I don’t want to make people afraid of encryption, but I do want people to be very aware that the technical implementation of encryption is not the hard part. The hard part is agreeing and documenting all the things that go round encryption.

So my call to action to you – person with TDE in your environment, and you – person with Always Encrypted implemented on a server near the end of it’s life – is to make sure more than just you know how to access the backups or certificates and know where your process is documented.

You won’t thank me for this, it will be one more long boring task to work your way through, but if you don’t do it – you, or whoever inherits your environment, may well curse the name of whoever introduced encryption into your workplace. And that would be a shame.

2 thoughts on “The thing that makes Encryption work

  1. Pingback: T-SQL Tuesday 167 – Encryption and Data Protection Roundup | Matthew McGiffen Data

  2. William McQuinn

    I’ve been documenting all the aspects of SQL Server and certificates for my office for the past few years. I’ve found and documented all sorts of tricks for getting SSL certs to bind to an instance for encryption in motion. I can go backwards and forwards in TDE using SQL generated certificates, but I’ve hit the 15208 wall trying to import a government certificate. Checked folder permissions, der encoding, tried using the pvk file passworded and unpassworded, encrypted and unencrypted, checked the length of the encryption key, and of course the spelling of the file names. I even installed both the self-signed cert coming out of SQL to compare the attributes to the trusted cert. THe only thing I can’t try is pvkconverter (not allowed on our site). Is that the only way to get a useable certificate/key pair from a pfx file? Are there any attributes I can check that may be preventing it from installing (AT_KEYEXCHANGE is the big one for SSL certs). Any advice would be much appreciated.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *