T-SQL Tuesday #167 – Encryption and Data Protection

I missed September since I was gone half the month in Europe and busy with a roadshow.

And, I missed October, since I was busy this month and lost track of this during my travels. However, I can still write, and I love the invitation from Matthew McGiffen. It’s on Encryption and Data Protection, two things I care about and about which I’ve delivered a few presentations.

As always, T-SQL Tuesday is a monthly blog party where you can write on a particular topic. It’s a great way to stimulate some learning and writing, but it’s also something you can catch up on later. I’ll likely write something on #166 in the future.

If you want to host, send me your interest on Twitter (@way0utwest), LinkedIn (/in/way0utwest), or email (sjones using the sqlservercentral domain).

Protecting Encrypted Data

One of the things I deal with regularly is how do you deal with encryption in your software pipeline. There are two concerns here:

1. How do we deploy encryption keys to production from development?
2. How do we refresh development environments with encrypted data?

It should go without saying, but I’ll say it. You can’t use the same keys in development and production because most auditors and regulatory authorities won’t see this as secure. If you have really secured development, and you are bonding developers, them maybe, but most of the time you need different keys/certificates/etc. in production than development.

This means you can’t have the key definitions (SQL Server) or asymmetric key files/certificates in version control. That makes deployment a challenge.

What I usually recommend here is that you have a separate pipeline for privileged, DBA, users who can store some of this in a separate repository and secure that.

At the same time, how do we get this data down to dev? Honestly, I wouldn’t. I’d let it come down encrypted, without the keys (those can be deleted as part of the refresh) and then add in known, secure, PII data with a dev key. The mechanics here get complex, but suffice it to say that protecting keys and keeping them out of dev is important.