SQL Server - encrypting data in a database table's column

I've got a problem scenario w.r.t data cyphering or encryption/decryption in SQL Server 2005.

Scenario:

• There is a specific table in the database
• The table has a column, let's say "Credit Card"
• The requirements is that the content or dat开发者_JAVA技巧a in this column should be encrypted

Required (plausible) solution:

• Data that is inserted in this table's column should be encrypted, i.e., is unreadable to people running direct queries on the database and/or table; or only by using a specific decryption logic, which requires a KEY of some sort
• While reading the data in any application, the method-of-decryption should be easy, maybe KEY based
• The process or methodology should be easy to use
• But difficult to break

Please give me some suggestions or solution in this regards.

Thank you.

• you encrypt data with a symmetric key
• you encrypt the symmetric key with a certificate
• you encrypt the certificate with a password
• periodically you generate a new symmetric key to encrypt new data
• priodically you rotate the certificate and re-encrypt the symmetric keys with the new certifictae, dropping the old certificate

Application requests password from the user and opens the certificate in the session. It then uses DECRYPTKEYBYAUTOCERT to encrypt the data. This is the general industry standard. It protects data rest and guards against accidental media loss as well as access from a person not knowing the password.

You will find a lot of bad advise ont his topic. Any scheme that is 'automated' and does not requests the user for a a decryption password is wrong. If you want 'automated' encryption or decryption you should look into Transparent Data Encryption which protects agains accidental media loss. TDE does not protect against other users browsing the data, if they have access priviledges.

Try looking into the DecryptByKey function.