• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

Steps to encrypt only certain columns in sql server 2008 sp2

Hi there,
I need to encrypt certain sensitive data columns  like SS#, TaxID, BankAccount, Bank account rounting# etc. from a table, which has already data on it. Could you please provide me the checklist to encrypt those columns? Thank you in advance for your quick response.
0
hpradhan08
Asked:
hpradhan08
  • 8
  • 7
1 Solution
 
lcohanDatabase AnalystCommented:
0
 
hpradhan08Author Commented:
I've that article. I was hoping the clear steps, please.
0
 
hpradhan08Author Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
Why don't you post the code you have written and we can then point out the problem.
0
 
hpradhan08Author Commented:
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?


0
 
Anthony PerkinsCommented:
>>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

Open in new window

0
 
Anthony PerkinsCommented:
And this:
CAVEAT: My encrypted will be different.
Should have read:
CAVEAT: My encrypted values will be different.
0
 
Anthony PerkinsCommented:
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

Open in new window

0
 
hpradhan08Author Commented:
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?
0
 
Anthony PerkinsCommented:
>>what is wrong with the steps?<<
I have no idea.  I could not execute them without errors.
0
 
hpradhan08Author Commented:
what errors you get when you execute?
0
 
Anthony PerkinsCommented:
Why don't you run it on a clean database and you will see for yourself?  For one, there are no CREATE TABLE statements...
0
 
hpradhan08Author Commented:
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?
0
 
Anthony PerkinsCommented:
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'.

Open in new window

0
 
hpradhan08Author Commented:
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.

0
 
Anthony PerkinsCommented:
And I am afraid I don't know either.  I have given you detailed steps as to how to do it correctly, other than driving over to your site and doing it myself, I have honestly no idea what else I can do.

Good luck.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now