Problem with Encryption / Decryption after database is re-attached...

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  August 13, 2013 at 4:17 pm

  #276883

  Hello - Using SQL Server Developer Edition 2005, and am having an issue with Encryption and Decryption of certain columns in certain tables where Symmetric Key Encryption was put in place.

  The database in question (along with all others in the instance) was dismounted from the instance in order for an Edition Upgrade to take place. The Edition Upgrade did not work, so a full uninstall and reinstall of SQL Server was done (DEV x64, 9.0.5000 SP4).

  Upon reattaching the databases, they all work, but the Encryption in the one is no longer working. Have tried several things with the previous MASTER KEY password to bring it back to life, but to no avail. Here is some of the code I have been working with...

  BACKUP MASTER KEY TO FILE = '\\Server\c$\Temp\masterkey2013'

  ENCRYPTION BY PASSWORD = 'Password'

  --Did the above command after the database was reattached.

  RESTORE MASTER KEY FROM FILE = '\\Server\c$\Temp\masterkey2013'

  DECRYPTION BY PASSWORD = 'Password'

  ENCRYPTION BY PASSWORD = 'Password'

  FORCE

  --Performed the above command first, but receive a message that stated 'The old and new master keys are identical. No data re-encryption is required'.

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password'

  ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

  GO

  --The above command is used in our nightly restore process of our PROD database to a PROD-REPORTING database, and works like a charm, but when I attempted to use this to fix my issue, it did not work. The command executes without error, but my encrypted columns are still encrypted. I have a View that is used to query the table with that Decrypts that content, but still - no workie.

  USE DATABASE;

  GO

  OPEN MASTER KEY

  DECRYPTION BY PASSWORD = 'Password'

  ALTER MASTER KEY

  DROP ENCRYPTION BY SERVICE MASTER KEY;

  ALTER MASTER KEY

  ADD ENCRYPTION BY SERVICE MASTER KEY;

  CLOSE MASTER KEY;

  GO

  --So in my efforts to research anything else that might help me out here, I did find the above command set that had a few more commands to use to try and reset things, but again - did not work. Ran without error, but still no data in my encrypted columns.

  Any and all help here would be appreciated very much. Thank you in advance.

  SQL_ME_RICH