Sean Lange (7/22/2013)

---

Why do you need to have both encrypted and clear text columns? This defeats the whole point of column level encryption. You haven't protected the sensitive information at all this way.[/

You are right on the target..That's also I am wondering , after applying folowing steps..

I have used following as a reference ..and use my account number column, in bank table in my client database,so only column, table and databases name are changed otherwise evrything is same.

USE AdventureWorks;

GO

--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 = ''

GO

CREATE CERTIFICATE HumanResources037

WITH SUBJECT = 'Employee Social Security Numbers';

GO

CREATE SYMMETRIC KEY SSN_Key_01

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE HumanResources037;

GO

USE [AdventureWorks];

GO

-- Create a column in which to store the encrypted data.

ALTER TABLE HumanResources.Employee

ADD EncryptedNationalIDNumber varbinary(128);

GO

-- Open the symmetric key with which to encrypt the data.

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric

-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.

UPDATE HumanResources.Employee

SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);

GO

-- Verify the encryption.

-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

GO

-- Now list the original ID, the encrypted ID, and the

-- decrypted ciphertext. If the decryption worked, the original

-- and the decrypted ID will match.

SELECT NationalIDNumber, EncryptedNationalIDNumber

AS 'Encrypted ID Number',

CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS 'Decrypted ID Number'

FROM HumanResources.Employee;

GO

Now , when I use select * from table name

it gives me whole table plus one encrypted column...and I don't know...why it is comming..[/b]

even in the "AdventureWorks" when I run above exact query it gives both original(clear text) and encryted text, here is the out put.(Please find the attachment)

Yes , we can surely do this but, my senior staff, ask me that once you have done encyption, why we have to create a view, to fillter restricted columns, which shows encryption is not done...properlly..

Now , what should I do...

Please let me know if need any further information, to help me.

thanks a million.