July 15, 2011 at 8:22 am
Having difficulty decrypting data on new SQL Server.
Last week:
- successfully backed up My_Database on SOURCEServer (2005)
- RESTORED the backup to my TARGETServer (2008 R2)
- Executed this and was able to see my encrypted data via a view:
USE My_Database
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd_ABC'
GO
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'pwd_XYZ';
GO
This week -- the same process is NOT working:
- successfully backed up My_Database on SOURCEServer (2005)
- RESTORED the backup to my TARGETServer (2008 R2)
- Executed the above 6 lines to OPEN MASTER KEY and ALTER MASTER KEY -- and received error msg:
Msg 15320, Level 16, State 1, Line 1
An error occurred while decrypting certificate 'SensitiveCustomerDataCertificate' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable.
-- Here is a sample of my KEY data:
--*******************************************************************************************
--SourceServer Key Info (sourced from VAULT by searching on "Service Master"):
--
-- SourceServer Service Master Key: pwd_123
--
--
-- My_Database Master Key: pwd_ABC
-- My_Database Master Key Backup Pwd: pwd_777
-- My_Database Sensitive Customer Data Certificate Key: pwd_345
--*************************************************************************************************************************
July 15, 2011 at 10:12 am
Resolved... I used this solution to resolve:
http://www.sqlservercentral.com/Forums/Topic629972-359-2.aspx#bm960265
USE your_database
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'your_old_sourcedb_master_key'
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'your_new_db_master_key'
CLOSE MASTER KEY;
July 15, 2011 at 10:25 am
correction... (on 3rd statement -- I avoided REGENERATING THE MASTER KEY and used the old one.. this fixed our issue)
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;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply