x

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. 
 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
 
more ▼

asked May 08, 2012 at 10:59 AM in Default

avatar image

ecomma
470 18 22 26

What is your code doing? Meaning, are there error messages? Does it complete successfully?

May 08, 2012 at 01:28 PM JohnM

What are you expecting your code to do?

May 08, 2012 at 02:21 PM Blackhawk-17

I expect my code to encrypted the entire row

May 08, 2012 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, 2012 at 03:00 PM

avatar image

Blackhawk-17
12k 30 35 42

(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, 2012 at 01:45 PM

avatar image

Cyborg
10.8k 37 54 51

(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, 2012 at 03:19 PM

avatar image

ecomma
470 18 22 26

i will try TDE instead.

May 08, 2012 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, 2012 at 04:21 PM

avatar image

TimothyAWiseman
15.6k 22 51 38

(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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x54

asked: May 08, 2012 at 10:59 AM

Seen: 1004 times

Last Updated: May 08, 2012 at 04:21 PM

Copyright 2016 Redgate Software. Privacy Policy