EncryptByKey Script HELP!!

[Steffi1013]

I need help with the syntax for encrypting columns for two tables. The scenario that I have is two tables with CCnum field in them which needs to be encrypted. For certain process I insert the credit card number into the 1st table and then move the data into the second table; but in other processes I just insert the credit card number into the second table and do nothing with the first table.

So both columns in each table needs to be encrypted, but what I am finding is that I have to decrypt the data by putting it in a #temp table from the first table and THEN doing an insert and encrypting it for the second table. Because if I don't decrypt the data from the first table, it is double encrypted in the second table and I am unable to decrypt it.

Is there an easier way that I am just not seeing? By the way I am using certificates and then symetric keys to do the encrypt.

 

[ptheriault]

Here is an example I got from BOL.

USE Orders
GO
--First, add a column to the Orders table to hold the encrypted card number
ALTER TABLE dbo.Orders
ADD EncryptedCardNumber VARBINARY(128)
GO
--Create the database master key for the Orders database
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='<YOUR PASSWORD HERE>'
GO

USE Orders
GO

--Use the certificate to create a symmetric key named CreditCardKey
CREATE SYMMETRIC KEY CreditCardKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = '<PASSPHRASE HERE>'


go
--Open the key 
OPEN SYMMETRIC KEY CreditCardKey
DECRYPTION BY PASSWORD = '<PASSPHRASE HERE>'
GO
--Look at the data before encryption
SELECT CreditCard,EncryptedCardNumber FROM dbo.Orders
GO

--Encrypt the credit card number using the symmetric key
UPDATE dbo.Orders
SET EncryptedCardNumber=EncryptByKey(KEY_GUID('CreditCardKey'),CreditCard)
go


--Since this is a new query, must open the symmetric key used to encrypt the data
OPEN SYMMETRIC KEY CreditCardKey
DECRYPTION BY PASSWORD = '<PASSPHRASE HERE>'
GO
--Make sure you can retrieve the decrypted credit card
SELECT CreditCard as [Original Card],
EncryptedCardNumber as [Encrypted Card],
CONVERT(nchar(16),DecryptByKey(EncryptedCardNumber)) as [Decrypted Card]
FROM dbo.Orders

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Steffi1013]

That is pretty much what I am doing now except I am having the issue with the 2nd table. I find that I have to decrypt it into a temp table from the first table and then insert it and encrypt it in the 2nd table and I want to know if there is a better way of doing this???

 

[ptheriault]

I don't see why you need to use the temp table? I've actually used this same process to encrypt passwords without any issues. What do you get for an error that makes you think you have to use a temp table?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

If you don't need to do any processing with the data when moving it from table1 to table2 why decrypt it. You should be able to select from table1 and to the insert/update to table2. Unless I totally not getting what the problem is.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Steffi1013]

My problem with just copying it from table1 to table2 is that I need to explicitly encrypt table2 also since it is my first insertion point for some of my processes and since it is also the table in which I copy data to from other processes.

For example... in my EO process I insert records which include credit card numbers into the "Order" table and then copy some of the details including the credit card number into the "Processing" table. But then in my DS process I insert credit card numbers into my "Processing" table and never touch the "Order" table.

So in both tables I need the column encrypted but from what I see, it must be two different keys ...

Am I missing something in my understanding???