Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Steps to encrypt only certain columns in sql server 2008 sp2

Posted on 2011-02-18
16
Medium Priority
?
483 Views
Last Modified: 2012-05-11
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
Comment
Question by:hpradhan08
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 34929858
0
 

Author Comment

by:hpradhan08
ID: 34930378
I've that article. I was hoping the clear steps, please.
0
 

Author Comment

by:hpradhan08
ID: 34954008
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34957290
Why don't you post the code you have written and we can then point out the problem.
0
 

Author Comment

by:hpradhan08
ID: 34957477
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34957582
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34957587
And this:
CAVEAT: My encrypted will be different.
Should have read:
CAVEAT: My encrypted values will be different.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34957595
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
 

Author Comment

by:hpradhan08
ID: 34957640
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34957709
>>what is wrong with the steps?<<
I have no idea.  I could not execute them without errors.
0
 

Author Comment

by:hpradhan08
ID: 34957714
what errors you get when you execute?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34958043
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
 

Author Comment

by:hpradhan08
ID: 34958065
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34960868
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
 

Author Comment

by:hpradhan08
ID: 34961107
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 34963191
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question