What is the best way to encrypt SSNs in SQL Server 2008?

Question

I am developing a new web application using .NET 3.5 and SQL Server 2008 that will need to store some Social Security Numbers. I've been doing some initial reading on database encryption and it's a little bit confusing.

I would be nice to encrypt the SSNs using an asymmetric key, since that way the public facing application wouldn't be able to retrieve any of the data once it has been encrypted. I was figuring that only the admin interface would be able to decrypt and display the data. But it sounds like SQL Server only protects data using a symmetric key?

So, what is the best way to encrypt SSNs in SQL Server 2008? Bonus points if you link to a good tutorial or two.

Answer 1

If you encrypt data then you must ask yourself who will decrypt it. If you use an asymmetric encryption system (e.g. RSA) then encryption uses the public key and decryption uses the corresponding private key; "asymmetry" comes from the fact that the private key cannot be recomputed from the public key (although both keys are mathematically linked together).

Asymmetric encryption tends to have overhead. A first remark is that such encryption must have some random part in it, because everybody can encrypt (the public key is, yeah, public): if the encryption process is deterministic, then anybody can encrypt all possible SSNs (there are less than a billion of them, which is a really small number for a modern computer) and match the encrypted values. Hence, there must be some random added during the encryption, and the encrypted SSN is larger than the plaintext SSN.

Known asymmetric encryption systems use mathematical structures which have their own cost. Basically, for the RSA encryption system, with a "strong enough" key, an encrypted message will have length at least 128 bytes. Some encryption systems do better; while sticking to the well-trodden paths of academic research, I could do it in 41 bytes or so (with El-Gamal over the NIST K-163 elliptic curve). Smaller seems harder.

So it is no wonder that a given database system would not include such a feature by default.

For your problem, you should first define (and write), as clearly as you can:

• what is the data which you want to protect
• who inputs the data
• who is supposed to read the data back

and then only should you ask yourself whether encryption is a proper tool for that. Encryption is good when the envisioned attacker can get his hands on the raw, stored data. This means that the attacker has bypassed the operating system protections. At that point, whatever the operating system knows, the attacker also knows. If the database is hosted on a machine and there is an interface through which the decrypted SSN can be obtained, then that machine "knows" how to obtain the data, and so does the attacker... On the other hand, if the host machine operating system is considered resilient enough, then encryption does not seem to be needed at all.

Symmetric encryption on the database may address a weaker problem, in which the attacker gets a copy of the harddisk afterwards. The host system knows the symmetric encryption key, but it knows it in RAM only. An attacker stealing the harddisk will not have that key.

Answer 2

You really don't want to use asymmetric encryption because it's very slow. Rather you'll want to protect a symmetric key with an asymmetric key, and then keep the symmetric key handy. To be honest, I would stick with what's in SQL Server rather than designing things yourself. A really good start is here http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.aspx