Column level Encryption

I am trying to Encrypt all column in my table. this what i got so far. but it is not doing what i want. How did you do yours?

USE Mob1;
--If there is no master key, create one now. 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    PASSWORD = 'securepassword@0712'

CREATE CERTIFICATE Biotable_certificate
   WITH SUBJECT = 'Certificate for my encryption';

CREATE SYMMETRIC KEY table_encryption_keys
    ENCRYPTION BY CERTIFICATE Biotable_certificate;

-- Create a column in which to store the encrypted data.
ALTER TABLE Bio_data.ReceiptNo 
    ADD ReceiptNo_Encrypted varbinary(128); 

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY table_encryption_keys
   DECRYPTION BY CERTIFICATE Biotable_certificate;

-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.  
UPDATE Bio_data.ReceiptNo 
SET ReceiptNo_Encrypted = EncryptByKey(Key_GUID('table_encryption_keys')
    , ReceiptNo, 1, HashBytes('SHA1', CONVERT( varbinary
    , ReceiptNo)));
more ▼

asked May 08 '12 at 10:59 AM in Default

ecomma gravatar image

460 13 18 22

What is your code doing? Meaning, are there error messages? Does it complete successfully?
May 08 '12 at 01:28 PM JohnM
What are you expecting your code to do?
May 08 '12 at 02:21 PM Blackhawk-17
I expect my code to encrypted the entire row
May 08 '12 at 02:51 PM ecomma
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

If you want all columns encrypted you need to encrypt every column as you did ReceiptNo_Encrypted.

A totally encrypted row is not generally a good design solution. Indexes are useless on encrypted columns as are Primary Keys. It also adds processing overhead to perform the cipher work.

Who are you trying to encrypt the data from? There may be alternative solutions for you.
more ▼

answered May 08 '12 at 03:00 PM

Blackhawk-17 gravatar image

11.8k 28 30 35

(comments are locked)
10|1200 characters needed characters left
I don't think there is any straight forward way of implementing encryption on entire row other than encrypting(as in post) all columns individually, but encrypting entire row may create serious performance issues.
more ▼

answered May 08 '12 at 01:45 PM

Cyborg gravatar image

10.6k 36 39 45

(comments are locked)
10|1200 characters needed characters left
Thanks Greg.Your points are noted. i cannot sacrifice my resources for have the rows encrypted.
more ▼

answered May 08 '12 at 03:19 PM

ecomma gravatar image

460 13 18 22

i will try TDE instead.
May 08 '12 at 03:20 PM ecomma
(comments are locked)
10|1200 characters needed characters left

AS Greg mentioned, alternative solutions are probably the way to go.

It is rarely wise to encrypt the entire row (the PK at least should probably not be encrypted so you can find the row you want...). Depending on the circumstance, TDE is an option, or encrypting the underlying disk outside of SQL, or encrypting just selected columns while leaving others in the clear. Also depending on the circumstance it can sometimes make sense to push the encryption duty to the client and just have SQL store the binary data for the columns that need encryption.
more ▼

answered May 08 '12 at 04:21 PM

TimothyAWiseman gravatar image

15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 08 '12 at 10:59 AM

Seen: 745 times

Last Updated: May 08 '12 at 04:21 PM