SQL Server 2008 Column Encryption

I've been trying to figure out a good way to encrypt sensitive columns in my DB. I thought the built-in encryption mechanisms of SQL Server would do the trick but either I'm missing something or doing it wrong.

The original plan was to create a table with columns that were encrypted with a symmetric key, and have a view select the data from the table unencrypted. However, I was unable to figure out how to use the DecryptByKey method in the view select statement. Plus it occurred to me that the data would be unencrypted going TO and FROM the view, so unless the connection was secure then it would sorta be pointless.

Then I thought to bring all the encryption/decryption to my app. I figured that

1. If the DB was completely unable to decrypt its own data, then someone infiltrating the DB wouldn't be able to do much at all.

2. It would save the server the effort of trying to decrypt/encrypt the info, as encryption/decryption in the DB could affect performance globally instead of just on a single workstation.

So as it sits, my app has "hard-coded" IVs and Keys for each column that needs to be encrypted. It sends the encrypted info to the DB, and receives encrypted info from the DB. This is just for messing around mind you, I know 开发者_JS百科I have to put the IVs and keys somewhere else...they simply aren't safe in the app code.

I was thinking of this crazy idea:

The client app would contain a single Key and IV. The server would contain the Keys/IVs of all of the encrypted columns in a single table. However, the values of the Keys/IVs would be encrypted with the Key/IV that the client app held.

On startup, the client app would load all the Keys/IVs from the DB into memory, and decrypt them as needed to view the data selected from the server.

There could also be a relation which would join users with keys they were allowed to use. So the app would only decrypt columns that the user was authorized to see.

Do you think the idea is a win or loose? And how have some of you implemented encryption given a client-app/SQL Server scenario?

---

YOu loose. Point. No chance to use indices etc.

If you want safety, put it on a safe server and load Enterprise Edition and use database file encryption.

---

Consider putting in a middle tier to handle the encryption/decryption for you. Assuming you can put it on the server you can keep control of the bits and not worry about the client app (which may be somewhat out of your control) from being decompiled (and exposing keys).