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

Question

I am developing a new web application using .NET 3.5 and SQL Server 2008, which will need to store some social security numbers. I am doing an initial reading on database encryption and this is a bit confusing.

It would be nice to encrypt the SSN using an asymmetric key, since this application, addressed to the public, will not be able to extract any of the data after it is encrypted. I realized that only the admin interface can decrypt and display data. But it looks like SQL Server is only protecting data with a symmetric key?

So what is the best way to encrypt SSN in SQL Server 2008? Bonus points if you refer to a good textbook or two.

Answer 1

You really do not want to use asymmetric encryption because it is very slow. Rather, you want to protect the symmetric key with an asymmetric key, and then save the symmetric key. Honestly, I would stick with what is on SQL Server, and not develop on my own. A really good start here http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.aspx

Answer 2

If you are encrypting data, you should ask yourself who decrypts it. If you use an asymmetric encryption system (for example, RSA), then encryption uses the public key, and decryption uses the corresponding private key; "asymmetry" is based on the fact that the private key cannot be recalculated from the public key (although both keys are mathematically connected together).

Asymmetric encryption has overhead. The first remark is that such encryption should have some random part in it, because everyone can encrypt (public key, yes, public): if the encryption process is deterministic, then anyone can encrypt all possible SSNs (there are less than billion of them, which is very small for a modern computer) and correspond to encrypted values. Therefore, there should be some random addition during encryption, and the encrypted SSN is larger than the plaintext SSN.

Known asymmetric encryption systems use mathematical structures that have their own costs. In principle, for an RSA encryption system with a “strong enough” key, the encrypted message will be at least 128 bytes long. Some encryption systems do better; adhering to the well-trodden paths of academic research, I could do it in 41 bytes or so (with El-Gamal above the elliptic curve NIST K-163). Less seems harder.

Therefore, it is not surprising that this database system did not enable such a function by default.

For your problem, you should first determine (and write) as much as possible:

• what data you want to protect.
• who enters data
• which should read the data back

and only then you should ask yourself if encryption is a suitable tool for this. Encryption is good when the alleged attacker can access raw, stored data. This means that the attacker circumvented the protection of the operating system. At this point, regardless of the operating system, the attacker also knows. If the database is hosted on a machine and there is an interface through which a decrypted SSN can be obtained, then this machine “knows” how to get the data, and the attacker also does it ... On the other hand, if the host machine operating system is considered to be quite stable, then encryption is not required at all.

Symmetric encryption in the database may be a weaker problem when an attacker subsequently receives a copy of the hard disk. The host system knows the symmetric encryption key, but it knows this only in RAM. An attacker stealing a hard drive will not have this key.

Answer 3

If you need to store SSNs and you want them to be encrypted, I recommend using a symmetric key encryption mechanism such as 3DES or AES. Let the encryption key be the expression of a phrase about what only those who are allowed to access the data know and that they must enter each time they access the data.

Example: (10+ Character Pass Phrase) -> SHA-1 => KEY.

You should not rely on the database to do encryption (although, of course, study functions such as TDE or any other host OS on which you support encryption with a full disk or file as a secondary security mechanism), rather use the built-in cryptographic .NET libraries and any programming language that you use to read and write to the database.

This gives you the advantage that you do not need to store the public and private keys or generate these keys (which is expensive by computational technology), and they are relatively large, so storage is more expensive (relatively), you also do not have to worry about that the key was compromised when an unauthorized user gains access to the machine on which your code is running (excluding MITM attacks that occur when a user password is entered). Secondly, it guarantees that when accessing them, the only way that they can be decrypted is through a user who is authorized (knows the password). Depending on your budget (time, effort, resources), you can add multi-factor authentication, in which the encryption key is obtained both from the phrase and due to the fact that authorized users will have a smart card. Thirdly, encryption and decryption of data using the symmetric key encryption algorithm will be much faster.