Steps to encrypt only certain columns in sql server 2008 sp2

How to: Encrypt a Column of Data

http://msdn.microsoft.com/en-us/library/ms179331.aspx

I've that article. I was hoping the clear steps, please.

I used that article to encrypt a column of data. When I want to see the decrypted data after opening the symmentic key with certificate the decrypted data returned all chinese character... could you please help?

Why don't you post the code you have written and we can then point out the problem.

Sure.

IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = 'XXXXX'
GO


CREATE CERTIFICATE Distributor101
   WITH SUBJECT = 'DistributorVindorInfo'
GO

CREATE SYMMETRIC KEY SSN_Key_101
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE Distributor101
GO

1. ssn
ALTER TABLE dbo.Distributor
    ADD EncryptedSSN varbinary(128);
GO

2. TaxID
ALTER TABLE dbo.Distributor
    ADD EncryptedTaxID varbinary(128);
GO

3. BankAccount
ALTER TABLE dbo.Distributor
    ADD EncryptedBankAccount varbinary(128);
GO


4. BankAccountRoutingNumber
ALTER TABLE dbo.Distributor
    ADD EncryptedBankAccountRoutingNumber varbinary(128);
GO

OPEN SYMMETRIC KEY SSN_Key_101
   DECRYPTION BY CERTIFICATE Distributor101

-- 1. ssn
UPDATE dbo.Distributor
SET EncryptedSSN = EncryptByKey(Key_GUID('SSN_Key_101'), SSN);
GO

-- 2 TaxID
UPDATE dbo.Distributor
SET EncryptedTaxID = EncryptByKey(Key_GUID('SSN_Key_101'), TaxID);
GO


-- 3 BankAccount
UPDATE dbo.Distributor
SET EncryptedBankAccount = EncryptByKey(Key_GUID('SSN_Key_101'), BankAccount);
GO

-- 4 BankAccountRoutingNumber
UPDATE dbo.Distributor
SET EncryptedBankAccountRoutingNumber = EncryptByKey(Key_GUID('SSN_Key_101'),  BankAccountRoutingNumber);
GO
-- Verify the encryption.

OPEN SYMMETRIC KEY SSN_Key_101
   DECRYPTION BY CERTIFICATE Distributor101

-- Now list the original ID, the encrypted ID
SELECT SSN, EncryptedSSN
    AS 'Encrypted ssnNumber',
    CONVERT(nvarchar, DecryptByKey(EncryptedSSN))
    AS 'Decrypted ID Number'
    FROM dbo.Distributor;
GO



SELECT TaxID, EncryptedTaxID
    AS 'EncryptedTaxID',
    CONVERT(nvarchar, DecryptByKey(EncryptedTaxID))
    AS 'Decrypted TaxID'
    FROM dbo.Distributor;
GO

SELECT BankAccount, EncryptedBankAccount
    AS 'EncryptedBankAccount',
    CONVERT(nvarchar, DecryptByKey(EncryptedBankAccount))
    AS 'Decrypted BankAccount'
    FROM dbo.Distributor;
GO

SELECT BankAccountRoutingNumber, EncryptedBankAccountRoutingNumber
    AS 'EncryptedBankAccount',
    CONVERT(nvarchar, DecryptByKey(EncryptedBankAccountRoutingNumber))
    AS 'Decrypted BankAccountRoutingNumber'
    FROM dbo.Distributor;
GO

what is wrong with these steps?

>>what is wrong with these steps?<<
I don't know, I could not get your script to run without errors.  

Try it using the code below:

CAVEAT: My encrypted will be different.

CREATE TABLE MyTable (
		ID integer IDENTITY(1,1) NOT NULL,
		SSN char(9),
		SSN_Encrypted varbinary(128)
		)

SET NOCOUNT ON

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXX'
CREATE CERTIFICATE Distributor101 WITH SUBJECT = 'DistributorVindorInfo'
CREATE SYMMETRIC KEY SSN_Key_101 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Distributor101

INSERT	MyTable(SSN)
VALUES	(123456789), 
	(987654321)

OPEN SYMMETRIC KEY SSN_Key_101 DECRYPTION BY CERTIFICATE Distributor101

UPDATE MyTable
SET SSN_Encrypted = EncryptByKey(Key_GUID('SSN_Key_101'), SSN)

SELECT	ID, 
	SSN, 
	SSN_Encrypted, 
	CONVERT(char(9), DecryptByKey(SSN_Encrypted)) DecryptedSSN
FROM	MyTable	

CLOSE SYMMETRIC KEY SSN_Key_101

DROP TABLE MyTable

DROP SYMMETRIC KEY SSN_Key_101 
DROP CERTIFICATE Distributor101 
DROP MASTER KEY

Output:
ID	SSN		SSN_Encrypted															DecryptedSSN
1	123456789 	0x00D5195BA7E28748B0C330A745CFB46E0100000062E28FA72717FCA16EF34E1B700546CB0339AB134292B7B6F731CB7E274AAA7C353484638152B895271070D56D9FDCF1	123456789
2	987654321 	0x00D5195BA7E28748B0C330A745CFB46E010000002E3C2EDB7112D993EEA1EE0449AFA9F927A036226311C6D7126C018332DAFD695C080F58AE6055E683E8DBE59B918201	987654321

Select allOpen in new window

And this:
CAVEAT: My encrypted will be different.
Should have read:
CAVEAT: My encrypted values will be different.

Also, take a look at the example below. Notice how the same values in different rows have different encrypted values.  This is the reason why an encrypted column makes a very poor candidate for indexing.

CREATE TABLE MyTable (
		ID integer IDENTITY(1,1) NOT NULL,
		SSN char(9),
		SSN_Encrypted varbinary(128)
		)

SET NOCOUNT ON

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXX'
CREATE CERTIFICATE Distributor101 WITH SUBJECT = 'DistributorVindorInfo'
CREATE SYMMETRIC KEY SSN_Key_101 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Distributor101

INSERT	MyTable(SSN)
VALUES	(123456789), 
	(987654321),
	(987654321)

OPEN SYMMETRIC KEY SSN_Key_101 DECRYPTION BY CERTIFICATE Distributor101

UPDATE MyTable
SET SSN_Encrypted = EncryptByKey(Key_GUID('SSN_Key_101'), SSN)

SELECT	ID, 
	SSN, 
	SSN_Encrypted, 
	CONVERT(char(9), DecryptByKey(SSN_Encrypted)) DecryptedSSN
FROM	MyTable	

CLOSE SYMMETRIC KEY SSN_Key_101

DROP TABLE MyTable

DROP SYMMETRIC KEY SSN_Key_101 
DROP CERTIFICATE Distributor101 
DROP MASTER KEY

Select allOpen in new window

Your sample code works. But it doesn't work on my those 4 columns. I need to troubleshoot and resolve... the decrypted value shows the following charters instead of the decryupted values:


¿¿¿¿6
¿¿¿¿5
¿¿¿¿3
¿¿¿¿9
¿¿¿¿2
¿¿¿¿7
¿¿¿¿0
¿¿¿¿0
¿¿¿¿5
¿¿¿¿8
¿¿¿¿2
¿¿¿¿8
¿¿¿¿1
¿¿¿¿2
¿¿¿¿3
¿¿¿¿4

what is wrong with the steps?

>>what is wrong with the steps?<<
I have no idea.  I could not execute them without errors.

what errors you get when you execute?

Why don't you run it on a clean database and you will see for yourself?  For one, there are no CREATE TABLE statements...

I did it multiple times in different clean db with the existing data  and got the same results. since I'm working on existing table, I don't need to create it... Please Genius guide me through how I encrypt the existing table in multiple columns.

Also, can we use where clause in encrypted data? If yes, could you please show me the example?

We are not communicating.  You asked me what errors I got.  I told you that if you were to execute it in a new database as I am you would see what errors I am getting.  I specifically told you that you were missing the CREATE TABLE your response ws that you were using "existing data" and "working on existing table".  I don't have that luxury.  Since you are not prepared to create a new database so that you can see the errors, I have once again done so and the errors can be seen below.

>>Please Genius <<
Please spare me the accolades, I don't need them.  Most people here call me by my name: Anthony

>>guide me through how I encrypt the existing table in multiple columns.<<
I did.  I showed you step by step how to do it.  I produced the valid output.

>>Also, can we use where clause in encrypted data? If yes, could you please show me the example? <<
I am not sure what you mean.  You can certainly do this in the example I posted earlier:

SELECT      ID,
      SSN,
      SSN_Encrypted,
      CONVERT(char(9), DecryptByKey(SSN_Encrypted)) DecryptedSSN
FROM      MyTable
WHERE      CONVERT(char(9), DecryptByKey(SSN_Encrypted)) = '123456789'

But you will have lousy performance as it cannot use any index.

Msg 4902, Level 16, State 1, Line 3
Cannot find the object "dbo.Distributor" because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 3
Cannot find the object "dbo.Distributor" because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 3
Cannot find the object "dbo.Distributor" because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 4
Cannot find the object "dbo.Distributor" because it does not exist or you do not have permissions.
Msg 208, Level 16, State 1, Line 6
Invalid object name 'dbo.Distributor'.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.Distributor'.
Msg 208, Level 16, State 1, Line 4
Invalid object name 'dbo.Distributor'.
Msg 208, Level 16, State 1, Line 3
Invalid object name 'dbo.Distributor'.
Msg 208, Level 16, State 1, Line 7
Invalid object name 'dbo.Distributor'.
Msg 208, Level 16, State 1, Line 4
Invalid object name 'dbo.Distributor'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Distributor'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Distributor'.

Select allOpen in new window

Hi Anthony,
Thank you for your understandable error. Yes, I created brand new database with the distributor table with those columns and use the same steps to encrypt/decrypt the values. I got the same results. I didn't understand why.