SQL Server Data Encryption and your options for encryption

There are number of ways to approach SQL Server data encryption. For compliance or whatever the purpose.

Conclusion: using combination of several encryption methods and technique is what most enterprise SQL Servers use. (list below)

Two major encryption approaches:

1. do only items to satisfy compliance requirements

2. you probably want to implement encryption that is hardest to break (not to just satisfy auditors)

Often #1 gets done now. And #2 is taken care of over time.

The solution which requires zero changes on the application is preferred.

Things to consider when encrypting SQL Server data:

1. data & log drives 

2. where backups are stored

3. data in memory

4. when db is restored, sensitive data should not be visible

5. communication protocols with SQL Server (they are not encrypted by default)

Lets look at some possible options of encrypting SQL data:

1. Encrypting drives where SQL data is stored. 

Pluses: 

– if drive is stolen the files should be useless

– performance hit is only few percentage points on CPU. Should be close to unnoticeable.

– often SQL Server storage is RAID’ed, so stealing a drive doesn’t get you full data (but depends on RAID level)

– easy to implement

– zero downtime to turn on Windows

– this satisfies a lot of different compliance methods or whatever they called

Minuses: 

– the only use case where drive encryption helps is when hard drive is stolen. And you have to ask yourself – how likely that is. Often its a ‘No’. If you in cloud, your changes are even smaller.

– doesn’t prevent other types of theft

– if hacker is on the server already (or internal theft) – this doesn’t help much

– if hacker is on the server already, files can be copied to non-encrypted location and drive encryption useless at that point

– this doesn’t solve an issue of restoring db onto another server (say lower environments – such as development, Quality assurance or others) – sensitive data is still visible

Bottom line: 

Implement. Just know that this doesn’t not a fix for all of the scenarios. 

2. Using SQL Server feature called Transparent Data Encryption or TDE

This encrypts data at rest. If db backup or files are stolen, its impossible to restore backup without certificates and keys.

Pluses:

– much more secure

– satisfies many compliance regulations

– easy to setup

– data is encrypted at rest

Minuses:

– data in RAM is un-encrypted!

– if you lose cert – kiss your db goodbye. This is a big one. Even Microsoft support won’t be able to help. So unless you have a solid way to manage keys & certs, DO NOT  implement this. Often we see this feature get turned on by someone that doesn’t fully understand how this works (say sysadmin). And maybe sysadmin does a good job managing certificate and keys. But you have to take this further, such as having multiple copies of those certs & keys in case sysadmin goes whacko or get hit by the bus.

– Performance has a huge hit here. Because all data is now encrypted, sql wont be able to perform lot of operations as fast as before.

– compression (which is one of the really useful features that speeds a lot of things up) wont be able to be used, since everything is now encrypted. Which means another performance hit.

Bottom line:

Don’t (if you can).

3. Dynamic Data Masking feature

This works at the presentation layer. Say there is a query: 

“select SSN from employees”, it can show SSN as “XXX-XX-XXXX” instead of actual number. 

Pluses:

– Easy to implement

Minuses:

– on surface it looks like a nice feature. However, it has a lot of holes. Not going to get into them here. If you want to use dynamic data masking, I would use third party product. GreenSQL had a good one. They been purchased since, dont know what they called now. I believe GreenSQL code worked at SQL communication protocol layer, where there is a proxy in between your client and actual server. This requires changing your app to hit new host (or some DNS config changes).

Bottom line:

Don’t. This is not a popular feature. As there are few more shortcomings, that make it close to useless.

4. Column level encryption 

This allows to pick out sensitive columns one by one and encrypt. 

You still need to guard db files, the backups, etc. But if your db is stolen and restored – the data inside should be close to useless. Since every column that matters is encrypted. Also one cool thing is when data is restored to lower environments (DEV, QA, UAT, TEST, etc) – developers don’t have access to sensitive data. I would still recommend to cleanse db for lower environments. But this is good already.

Pluses

– you can chose what should be encrypt

Minuses

– It requires app changes, which may take time

– you may miss some columns

Bottom line:

Implement. This will take time, but this is how things are done, when encryption is done right. 

You can thank me later!

5. Always Encrypted

Pluses:

– data is always encrypted – in motion and at rest. SQL cant even see it, because its done at the sql driver level, SQLEngine doesnt even know what data it has.

Minuses:

– performance. if column is ‘always encrypted’, SQL will not know what data is in the column. So for example if you do “SELECT * FROM credit_card_holder WHERE street_name LIKE ‘%parkway%’ ” – SQL will not be able to filter out and only get you 1% of data. That means SQL returns full data set, and to db driver, which decrypts every name and then does the string comparison. So its same as you were looking for single person from Yellow Pages, instead of getting 1000 rows, you’d get 5 million.

– only available on SQL2016 or higher

Bottom line:

Don’t if you can. My guess is that this feature is built for a public sector, such as FBI, CIA, etc. Businesses don’t really use this.

How do best enterprises encrypt data in SQL Server?

6. Use combination of encryption methods

Here is what we see often:

a) encrypt disk – use Win feature. Hits CPU at about 1%, so no reason not to.

b) encrypt SQL backups using SQL native feature

c) encrypt at column level

d) To add another safety layer on top of column level encryption, store salt and other security details in a separate db. That way, if your PROD db is stolen, it is useless without that db2. HA!

e) Maybe use TDE on top of all this. Most likely not.

And BAM! You at the top of the game with SQL data encryption game!

Agree? Disagree? Comment below!