Column level Encryption

Question

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. 
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = 'securepassword@0712'
GO

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

CREATE SYMMETRIC KEY table_encryption_keys
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Biotable_certificate;
GO

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

-- 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)));
GO

Answer 1

0

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.

answered May 08 '12 at 03:00 PM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

(comments are locked)

Answer 2

0

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.

answered May 08 '12 at 01:45 PM

Cyborg
10.2k ● 31 ● 39 ● 44

(comments are locked)

Answer 3

0

Thanks Greg.Your points are noted. i cannot sacrifice my resources for have the rows encrypted.

answered May 08 '12 at 03:19 PM

ecomma
420 ● 1 ● 8 ● 13

i will try TDE instead.

May 08 '12 at 03:20 PM ecomma

(comments are locked)

Answer 4

0

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.

answered May 08 '12 at 04:21 PM

TimothyAWiseman
14.4k ● 17 ● 21 ● 29

(comments are locked)

Column level Encryption

Follow this question

Related Questions