Solved

Steps to encrypt only certain columns in sql server 2008 sp2

Posted on 2011-02-18
16
432 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
  • 8
  • 7
16 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
0
 

Author Comment

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

Author Comment

by:hpradhan08
Comment Utility
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
 
LVL 75

Expert Comment

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

Author Comment

by:hpradhan08
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:hpradhan08
Comment Utility
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
Comment Utility
>>what is wrong with the steps?<<
I have no idea.  I could not execute them without errors.
0
 

Author Comment

by:hpradhan08
Comment Utility
what errors you get when you execute?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now