Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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