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