Decryption Issue

• lhowe

  Ten Centuries

  Points: 1287

  More actions

  March 24, 2015 at 11:49 am

  #296325

  Hi,

  I've got a situation where what I'm doing with encryption is working in my test environment, but not in the production environment, and can't figure out why.

  Here's my issue...

  I have an externally accessible SQL Server (2005) that we are going to house somewhat sensitive data in. Let's call that "ServerE". Due to the exposure, we are encrypting (via Symmetric key) the sensitive data into varbinary columns. Since ServerE is externally exposed, we wish to pull the data via a job that runs every 15 minutes, in its encrypted form, into an internal server ("ServerI") table (also SQL Server 2005) that is defined the same as the table on ServerE, decrypt the data, store into a table that is structured with varchar instead of varbinary, and delete the original row on ServerE. This way our external exposure is limited to no more than 15 minutes. To encrypt on ServerE, I'm using a Symmetric key built on a Certificate that is based upon the Database Master Key for this database. To decrypt on ServerI, I restored the Database Master Key and Certificate from the ServerE backup files, created a Symmetric key from the Certificate using the same Algorithm, Key Source, and Identity Value as was used to create the Symmetric key on ServerE.

  As I mentioned, I have this working fine in our test environment, but on the production environment, all of the encrypted columns are returning NULL for some reason. The table definitions are exactly the same between test and production. Also, before someone suggests a column size issue, I've already tested using just a Select and varchar(max) for each of the Casts on the decrypting columns. Didn't make a difference...this appears to be strickly a key issue.

  Being thoroughly frustrated, I even rebuilt the encryption keys from scratch on ServerE, deleting the previous versions first of course, then again backed up the Database Master Key and the Public and Private keys of the Certificate. I cleared out the keys on ServerI, restored the Database Master Key and Certificate from the backup files, and recreated the Symmetric Key based upon the information specified above. None of this made a bit of difference.

  As far as code is concerned, I'm not doing anything out of the norm; encrypting via the EncryptByKey function using the open Symmetric key, and decrypting using the DecryptByKey function with the Symmetric Key open. Since I'm on a different server while decrypting than I was while encrypting, for some reason I also needed to open the Database Master Key on the decryption side in order to be able to open the Symmetric Key. I had to do that in Test too, and then that worked.

  I'm not sure what else I can explain. If you need more information, please ask. Has anyone else tried to do this scenario; encrypting on one SQL Server and decrypting on a different SQL Server?

  Really frustrated here! Thanks in advance for your help!

  Larry