Column encryption advice

Question

Column encryption advice

as_1234

SSCrazy

Points: 2842
More actions
October 18, 2017 at 7:16 am

#340889

All,
If possible I would appreciate your advice on my plan and questions.
Situation:
I need to encrypt certain columns against the risk of someone external getting a copy of the data. Mainly though getting physical access to the backup tapes or server (both are protecting but...)
I don't need to encrypt different parts of the database with different encryption. The user and application level security is sufficient for this.
I am running a SQL 2016 Standard availability group
Concerns:
Ensuring the data is secure enough
Not making it too complicated to manage and locking myself out of the data
Questions:
As you know at minimum I need a key. Asymmetric is more secure but symmetric systems to be recommended? Processing overheads shouldn't be too much of a concern on this server.
It looks like I can just use a key encrypted by a password and not use a certificate or master key? Instinctively that seems less secure but someone only needs the password for the master key or certificate to get to the lower items in the chain so I can't see where the extra security comes from?
Assuming I only need a symmetric key then all I need to be able to restore the backup to a new server is?:
The sql used to create the key
The password
The key source
The identity value
With those four I can't lock myself out of the data?
In terms of restore testing the following would ensure that I can access the data?:
Restore the database under a new name.
Drop the key
Create the key using the same sql, password, key source and identity value?
Everything is stored in the database rather than the server instance?
Sorry for asking for info that's available on line. I have read lots of tutorials I just want to make sure I understand it correctly.
Any other advice?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

if you're aim is to protect your back ups, rather than encrypting data on your Server, have you considered encrypting your backups? There was an article published on this site about it this very day! 🙂 http://www.sqlservercentral.com/blogs/all-about-sql/2017/10/09/sql-server-2017-encrypted-backups-and-compression/

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

as_1234 SSCrazy Points: 2842 More actions · Answer 2

Hello,

Thanks for your help on this (and on my other numerous questions 🙁 )

I'm also looking at that. I wanted to encrypt the live server data as well just in case.

Thanks

Andrew

jasona.work SSC Guru Points: 50083 More actions · Answer 3

What you should consider is, what are the threat vectors you're concerned about?
Backups being stolen? Look at encrypting the backups (either via the article Thom A linked, or move to Enterprise and use TDE)

The physical drives being stolen? Now you need to look into some sort of either whole disk encryption outside SQL, or TDE

Preventing someone from querying the data directly (via SSMS, Excel, Access, etc)? You're going to need to do what you're already looking at. Either Asymmetric or Symmetric key encryption, and controlling access to the key. Or look into "Always Encrypted" to protect the data that needs it.

Please note, of the above, I've only had experience with TDE and Symmetric keys created from certificates.

as_1234 SSCrazy Points: 2842 More actions · Answer 4

Hello,

Thanks for the advice. I will take a look at disk encryption as well.

Part of my reason for thinking about column level encryption is that most of the data doesn't need encrypting so if I encrypt everything (either backups or physical disks) I'm actually creating another risk in that I can't decrypt data the business needs that didn't need encrypting in the first place. As an example one of our product codes is pretty useless to anyone external, someone's postcode is of use. I'm probably at a ratio of 95% doesn't need encrypting, 5% does.

I'm not challenging what your saying, I appreciate the advice.

Thanks

Thom A SSC Guru Points: 99126 More actions · Answer 5

It's' worth noting that a Post Code unto itself isn't worth encrypting. You're not liking to identify an individual from that data. It's the combination of that and other data.

For example, having the name of your one of your customers "John Smith" doesn't identify anyone, as there are lots of John Smith's. On the other hand, if it said your customer was "Dame Judi Dench" then that is clearly identifiable.

If we go back to our customer John, on his own a name is nothing. However, if we add a few extra details in. John Smith who lives in HertfordShire. Getting closer. Ok, how about john Smith who lives in Stevenage, Hertfordshire. Closer still. Finally, what about John Smith who lives in Inskip Cresent, Stevenage, Hertfordshire. Well, now you have an individual. Street name is one I randomly found on Google Maps.

I'm not saying that all those columns need encrypting. In fact, definitely not. What I am saying, however, is that a lot of data on it's own means nothing until you combine it with other data. All that data is likely to be unencrypted, and I woulnd't recommend encrypting in at column level. That's why you need to look at other methods. Like encrypting the physical storage device, using Always Encrypted or (like Jasona mentioned) TDE.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

as_1234 SSCrazy Points: 2842 More actions · Answer 6

Hello,

Thanks. Your right about needing enough data to identify an individual. Postcode was just an example column of the columns.

I think always encrypted would give me an issue because of not being able to use like operations e.t.c. That's if I read the 'feature details' here https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine correctly? Also more things to manage on the client machines? With column level encryption the application or SSMS just need to include the relevant password for the key or cert in the query?

If your think of column level encryption being an issue due to manageability then that's a good point. I only need to encrypt three tables out of about 100 so it should be OK from that view point.

Sorry if it seems like I'm being awkward. I'm not meaning to ignore your advice, I appreciate it. I'm just meaning to clarify my understanding.

Thanks

Andrew