May 21, 2012 at 4:21 am
Hi All,
I have been recently requested to help for encrypting token and payment data in some in way in the payment table (SQL 2005 DB)
Is there a default way in SQL Server in which we can configure the database columns as encrypted (or hashed) so that automatically the database encrypts/decrypts (or hashes) it when inserting and/or reading from a table?
Thank you in advance.
Regards,
Suresh
Regards,
Suresh Arumugam
May 21, 2012 at 4:30 am
I don't think there is any in-build way. You will need to use "instead of" triggers when inserting and updating data. Check this one, it has some good samples to get started:
http://blogs.technet.com/b/keithcombs/archive/2005/11/24/415079.aspx
May 22, 2012 at 6:40 am
The database only holds the data, it does not do the encryption/decryption. Specialized functions accomplish that in column-level encryption (also in TDE, just at a different spot, and invisible to the user), and are called by the user to run those functions. You run the function to get the encrypted value, then save that value to the table. When reading, you take the encrypted value in the table and decrypt it for use.
With CLE (column-level encryption), you have to use the varbinary data type for any column to be encrypted. Because of this, this column cannot be indexed. That is one of the biggest issues with CLE. There are ways to compensate, but that is a big issue with it.
See this thread for some notes I posted on column-level encryption, with some scripts to get you started.
http://www.sqlservercentral.com/Forums/Topic1152176-391-1.aspx
The good thing about CLE is that it works in Express Edition, from 2005 and up.
Hope it helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply