Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SQL_ME_RICH
SQL_ME_RICH
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 1588
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search