March 26, 2015 at 6:19 pm
Mods, please move this question to a better forum if warranted.
We ran our LOB software on SQL 2000 for many years. In "upgrading" to a supported version, we migrated to SQL 2012 for the back end. Numerous fields are now encrypted, albeit with only one symmetric key. I am slowly learning SQL encryption and decryption and have been able to fix most of our internally-developed objects.
For reasons best known to our consultant, he did not migrate at least one field that is now encrypted, but instead left it null. Granted it's his responsibility to fix this, but I need to know how our new DB works.
I dumped the data from our old server, imported it into a temporary table, and am pulling my hair out trying to update the new one.
OPEN SYMMETRIC KEY NewKEY
DECRYPTION BY CERTIFICATE New_CERT;
The following only returns the first character of the fedid field. I tried endless permutations of CONVERT in the update statement.
update v set fedid=encryptbykey(Key_GUID('NewKEY'), t.fedid) from vend as v join tempdb.dbo.VEND2 as t on v.vendid=t.VENDID where t.VENDID='somevendorID'
select vendid, CONVERT(VARCHAR(9), DECRYPTBYKEY(FEDID)) from vend where vendid='somevendorID'
This works fine:
update v set fedid=encryptbykey(Key_GUID('NewKEY'), '123456789')
from vend as v join tempdb.dbo.VEND2 as t on v.vendid=t.VENDID where t.VENDID='somevendorID'
Note that I imported VEND2 from a flat file. The data came over fine. vendid is char(6) and fedid is nvarchar(40). fedid in the production table is varbinary(max).
Thanks for any assistance. Also, besides MS, is there any good resource for learning SQL Server's encryption? I need to come up to speed quickly.
March 30, 2015 at 11:37 am
Since constants worked, I gave up the other approach and used a Select statement to generate a bunch of update statements:
select 'update vend set fedid=encryptbykey(Key_GUID(''NewKey''), '''+ rtrim(fedid) + ''') where vendid='''+ vendid +''''
from tempdb.dbo.vend2
where rtrim(fedid) <> ''
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply