Encryption in the Database

[rgarrison]

(This thread is associated with Part 10 of the SqlCredit series.)

See "Encryption Summary" in the article. Here is the list again:

1. Don’t index encrypted values.
2. Encrypt/DecryptByCert is definitely the wrong choice for transactional data. It can be used for small bits of data.
3. Encrypt/DecryptByKey performs very well on transactional data. There is some cost in both CPU utilization and duration of the operation.
4. Both EncryptByCert and EncryptByKey have string length limitations. See part 6 for EncryptByCert and part 8 for EncryptByKey.
5. To support both unencryption and efficient lookups, combine a column that stores the data encrypted with a column that stores the data hashed.
6. varbinary(MAX) is fine for a quick spike, but for real code, be thoughtful about what size varbinary you use.
7. Encrypting data at the database layer can be done without affecting the calling code.
8. Encrypt only the data that needs to be encrypted. Much of what is stored in transactional databases is not sensitive.

What do you think?

One question asked in the article is

Should the data be passed plain-text to the database and encrypted as it is stored, or should it be passed to the database as an encrypted value?