Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with Encryption / Decryption after database is re-attached... Expand / Collapse
Author
Message
Posted Tuesday, August 13, 2013 4:17 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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
Post #1484023
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse