Encrypting unique columns
I hope I will not be banned for spamming this section, haha. I have a few questions regarding encrypting data in my MSSQL database. For instance, say I have a table Person that has the following columns:
id, int (PK)
socialNumber, VARCHAR(50), UNIQUE
Just an example. I am encrypting a String with a randomly generated key and I can then decrypt it again with the same key. As a result, I store the encryption key in the table. My problem is that to get the key, I must find the appropriate row. To do this, I would search for a given social number, but the problem is that it is encrypted in the database! Therefore I am having trouble figuring out how I can get the appropriate key to decrypt the social number.
I guess a solution could be to use the same encryption key for the entire table (or database), but I do not fancy that because it seems less secure to me.
Does anyone have a suggestion on what a good solution is?
Thanks a bunch in advance! :)