question

ecomma avatar image
ecomma asked

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
encryption
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
What is your code doing? Meaning, are there error messages? Does it complete successfully?
0 Likes 0 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
What are you expecting your code to do?
0 Likes 0 ·
ecomma avatar image ecomma commented ·
I expect my code to encrypted the entire row
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ecomma avatar image
ecomma answered
Thanks Greg.Your points are noted. i cannot sacrifice my resources for have the rows encrypted.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ecomma avatar image ecomma commented ·
i will try TDE instead.
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.