May 30, 2012 at 8:50 am
Hello all,
I have a database that is storing SSNs...SO while in development I have set up encryption on that column...However my programmer was running into problems with the encrypted field, therefore since we are just in development, he needs to get a model set up so we figured it would be easier to remove the encryption from the column and he fixes his bugs in the app, and then once we have a working app, we will go back and tackle the encryption issue...
But I cannot figure out how to take encryption off of that column? I know when you select the columns, you select them using DECRYPTBYKEY function, but I want to completely take out the encryption or disable it somehow...
Can anyone help me out?
Thanks
May 30, 2012 at 9:05 am
Encryption on a column can't be 'turned off.' It's the values in the field that are encrypted, not the column itself. You are probably storing the encrypted SSNs in a VARBINARY column. I would create a temporary column and populate it with unencrypted SSNs for your testing phase.
For a permanent solution, you may want to consider a decryption stored proc that your developer can call when the decrypted values are needed.
Do you know if the encryption was done using EncryptByPassPhrase or EncryptByKey?
_________________________________
seth delconte
http://sqlkeys.com
May 30, 2012 at 9:17 am
It was set by ENCRYPTBYKEY...
Yes I stored it as a VARBINARY...
Here is how I set it up:
OPEN SYMMETRIC KEY Key_01
DECRYPTION BY CERTIFICATE DatabaseA01
UPDATE dbo.TableA
SET SSN = ENCRYPTBYKEY(KEY_GUID('Key_01'), SSN)
CLOSE SYMMETRIC KEY Key_01
So whenever a field is added, and you select that table, the SSN value is encrypted...
And I know to select it where that field is decrypted, you run the select like this:
OPEN SYMMETRIC KEY Key_01
DECRYPTION BY CERTIFICATE DatabaseA01
GO
SELECT CONVERT(VARCHAR(15), DECRYPTBYKEY(SSN))
FROM dbo.TableA
CLOSE SYMMETRIC KEY Key_01
So there is not a way to disable that so when you insert a record, it goes in as a regular number? So what happens in a situation where you design a db and set up a column for encryption, and then later down the road, they say that the encryption is no longer needed and needs to be taken out?
May 30, 2012 at 9:35 am
asm1212 (5/30/2012)
It was set by ENCRYPTBYKEY...So there is not a way to disable that so when you insert a record, it goes in as a regular number? So what happens in a situation where you design a db and set up a column for encryption, and then later down the road, they say that the encryption is no longer needed and needs to be taken out?
In that case you have to rewrite the code that works with that column.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 30, 2012 at 9:38 am
This statement simply encrypts EXISTING SSNs:
OPEN SYMMETRIC KEY Key_01
DECRYPTION BY CERTIFICATE DatabaseA01
UPDATE dbo.TableA
SET SSN = ENCRYPTBYKEY(KEY_GUID('Key_01'), SSN)
CLOSE SYMMETRIC KEY Key_01
If new records are being encrypted as they are added, you probably have an insert trigger in place doing the encryption. The contents of the trigger probably look something like this:
OPEN SYMMETRIC KEY Key_01
DECRYPTION BY CERTIFICATE DatabaseA01
insert into dbo.TableA values(EncryptByKey(Key_GUID('Key_01'), INSERTED.SSN) )
CLOSE SYMMETRIC KEY Key_01
To disable the automatic encryption via the trigger, just disable the trigger.
To decrypt the existing values that are in the column, run the update again, this time decrypting, instead of encrypting:
OPEN SYMMETRIC KEY Key_01
DECRYPTION BY CERTIFICATE DatabaseA01
UPDATE dbo.TableA
SET SSN = DECRYPTBYKEY(KEY_GUID('Key_01'), SSN)
CLOSE SYMMETRIC KEY Key_01
The column data type will need to be changed to allow INT or VARCHAR values to be inserted now, unless you want to just convert the values to varbinary as new records are inserted.
_________________________________
seth delconte
http://sqlkeys.com
May 30, 2012 at 10:16 am
Seth has a good explanation, though I don't know I'd assume a trigger is being used. A stored proc can do this well.
The issue is you can't turn off encryption and insert ints. You need to change the data type, so this isn't an easy thing to do. You can add a second column as a varchar or int and then store the unencrypted values there. When you decide to start using encryption, you can make that a blank (or zero) value for all rows. However your queries will change as the location (field) of the data changes.
May 30, 2012 at 12:09 pm
seth delconte (5/30/2012)
This statement simply encrypts EXISTING SSNs:
OPEN SYMMETRIC KEY Key_01DECRYPTION BY CERTIFICATE DatabaseA01
UPDATE dbo.TableA
SET SSN = ENCRYPTBYKEY(KEY_GUID('Key_01'), SSN)
CLOSE SYMMETRIC KEY Key_01
If new records are being encrypted as they are added, you probably have an insert trigger in place doing the encryption. The contents of the trigger probably look something like this:
OPEN SYMMETRIC KEY Key_01DECRYPTION BY CERTIFICATE DatabaseA01
insert into dbo.TableA values(EncryptByKey(Key_GUID('Key_01'), INSERTED.SSN) )
CLOSE SYMMETRIC KEY Key_01
To disable the automatic encryption via the trigger, just disable the trigger.
To decrypt the existing values that are in the column, run the update again, this time decrypting, instead of encrypting:
OPEN SYMMETRIC KEY Key_01DECRYPTION BY CERTIFICATE DatabaseA01
UPDATE dbo.TableA
SET SSN = DECRYPTBYKEY(KEY_GUID('Key_01'), SSN)
CLOSE SYMMETRIC KEY Key_01
The column data type will need to be changed to allow INT or VARCHAR values to be inserted now, unless you want to just convert the values to varbinary as new records are inserted.
Ok I ran the above statement when I was trying to figure out how to decrypt the existing values before I posted on here...But I kept getting an error...however, I changed the data type to an int but I still am getting that error message when I execute that statement:
Argument data type uniqueidentifier is invalid for argument 1 of DecryptByKey function.
So my quick fix is to drop and recreate the table and not to worry about the encryption until we are ready to go live! Then we tackle that issue...
May 30, 2012 at 12:16 pm
Ok I ran the above statement when I was trying to figure out how to decrypt the existing values before I posted on here...But I kept getting an error...however, I changed the data type to an int but I still am getting that error message when I execute that statement:
Argument data type uniqueidentifier is invalid for argument 1 of DecryptByKey function.
So my quick fix is to drop and recreate the table and not to worry about the encryption until we are ready to go live! Then we tackle that issue...
Well, yes, wouldn't the decrypted value of SSN be of type INT or VARCHAR? You'd have to convert it before updating:
OPEN SYMMETRIC KEY Key_01
DECRYPTION BY CERTIFICATE DatabaseA01
UPDATE dbo.TableA
SET SSN = DECRYPTBYKEY(KEY_GUID('Key_01'), CONVERT(VARBINARY(8000),SSN))
CLOSE SYMMETRIC KEY Key_01
_________________________________
seth delconte
http://sqlkeys.com
May 30, 2012 at 12:20 pm
Actually, it seems like your immediate error is resulting from not converting the result of the KEY_GUID() function. Convert the value to VARCHAR:
OPEN SYMMETRIC KEY Key_01
DECRYPTION BY CERTIFICATE DatabaseA01
UPDATE dbo.TableA
SET SSN = DECRYPTBYKEY(CONVERT(VARCHAR,KEY_GUID('Key_01')), CONVERT(VARBINARY(8000),SSN))
CLOSE SYMMETRIC KEY Key_01
_________________________________
seth delconte
http://sqlkeys.com
May 30, 2012 at 12:29 pm
Apparently I need a crash course on encryption...This gets really confusing...
I am not sure how I will move forward but I will somehow someway! lol
Thanks to all that have responded and tried helping me with this issue!
May 30, 2012 at 12:57 pm
Pick up Michael Coles' book on Pro SQL Server 2008 Encryption
May 30, 2012 at 1:08 pm
asm1212 (5/30/2012)
Apparently I need a crash course on encryption...This gets really confusing...I am not sure how I will move forward but I will somehow someway! lol
Thanks to all that have responded and tried helping me with this issue!
It's not as complex as it looks. MSDN has a complete library of documentation on every function and clause like this: http://msdn.microsoft.com/en-us/library/ms181860.aspx.
I would usually just google something like "decryptbykey msdn" to find it.
_________________________________
seth delconte
http://sqlkeys.com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply