Encryption is more about the implementation of the algorithm than the algorithm itself. My recent research has made me come to the conclusion that AES 256 is good choice for Symmetric key algorithm. I have veered away from 3DES because of some claims of its weakness.
Lets say we need to store SSN, and retrieve SSN, and there is no way around it. I need to to run a credit report and then need it again to file some kind of government form on a regular basis. The business unit says I can’t just keep asking for this data so I have to store it.
I’m going to make an assumption here that we want to keep this data safe, not just because of some compliance requirement… but because we care about our customers data. First lets follow the data along it’s theoretical path with some potential exposures. Think of a GPS application where you enter your destination and it uses your current location to define a route. This is what I call the data route.
Defining the Data Route
Call center asks customer over the phone SSN
prism! Just metadata you say? Ok phew.
calls are recorded for quality control
Call center agent or customer enters the SSN into the browser based form
browser saves form data for your convenience
Browser packages this up and sends it over the wire to the server
browser history(you wouldn’t put this in a URL param but just in case…)
intrusion detection systems
Web Server application processes the data
data read from unprotected memory by another process
code persists data to disk(copy paste)
hibernation file is snagged with data from memory
server low on memory and pages data to disk(copy paste)
debugging tools decompile code
Web Server sends data over the wire to the database
parameters are stored with query plans
Database persists the data
Select * from ssn
copy & paste .mdf
data is pumped out to reporting applications
data is given to developers for debugging
prod is copied to Quality Assurance servers
SAN mirrors data offsite
Rouge DBA or other Admin
Database Backs-up the data over the wire to a file share
all that same network goodness
copy/paste the file share
Ok, scared now… lets talk about encryption
To simplify the matter, there are three pieces needed to encrypt/decrypt
1. The Data (a single cell)
2. The Key
3. The Algorithm
Lets make a small distinction right off the bat. These three things live in memory at the time of the encryption/decryption. That doesn’t mean they have to be persisted to disk in that same location. Here is a good discussion on the matter of key storage http://stackoverflow.com/questions/723653/storing-encryption-keys-best-practices You can shred up your key and store it in several locations but just know you have added several single points of failure. Single points of failure can cause data loss, not just for the hacker but for your business.
The goal of our encryption is to prevent those who have gained access to the data, still not be able to read the data. It’s an additional layer to prevent as many of the vulnerabilities above as we can. From a security standpoint, encrypting at the earliest possible point in the data route would considered a best pratice. Unfortunately those methods are not always feasable. Your users would hate you if you implemented a custom keyboard.
Three potential solutions
Option A: We write code to do it on the web servers.
Another pretty thorough sample. http://www.obviex.com/samples/Encryption.aspx
This option has more pros than cons. We are pretty far up the data route so if we chose SSL to protect the data over the internet, we could then encrypt the SSN on the web server and the rest of the route would be protected. Encryption is CPU intensive so a web server that scales out well is a good choice for this process. One con is that web servers are usually exposed to a larger audience of malicious type folks.
Option B: We change SQL Statements and do it on the SQL Server.
This requires changing queries to include a passphrase as a parameter. I’m not a fan since the encryption key is persisted stored on the same host as the data. It does add the benefit of not allowing db_datareader access to plaintext data.
Option C: We make minor changes and utilize TDE on the SQL Server.
This is an option that may allow an auditor to check some proverbial box. It can add anywhere from 5-25% overhead and encrypts the entire database as it rests on disk. db_datareader can still read plaintext SSNs so not much protection. If an attacker copied the .mdf, they generally have access to copy the keys too. It might help with the backup security vulnerability.
Consider a hacker has compromised one of your customers SSNs from, I don’t know… maybe the government of South Carolina. With the name and SSN can the hacker reverse engineer your algorithm to figure out the key and compromise the rest of your customers data?
Key rotation is a good idea. If you realize your key has been compromised, you will want a well documented way to change that key. You may need to decrypt and re-encrypt all of your data with the new key.
Algorithms are usually public knowledge and built into high level languages such as Java and .NET. Salt is a good way to mask the algorithm and key.
Application layer encryption add a vast amount of protection to the data on the database server. DBAs no-longer have dnclip enabled for all of your customers data. This provides separation of duties and separation of the three key pieces of the puzzle.