April 26, 2016 at 8:02 pm
When I moved my database to an existing sql server 2008, I ran this script on the old server :
BACKUP MASTER KEY TO FILE = N'path_to_file'
ENCRYPTION BY PASSWORD = N'oldPassword';
and after transferring the database - on the new server :
RESTORE MASTER KEY
FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'oldPassword'
ENCRYPTION BY PASSWORD = 'newpassword';
and all worked well.
Now I want to move this database from this SQL Server 2008 iteration to an existing 2014 iteration.
The BACKUP of the Master Key on the 2008 database works fine of course, but after transferring the database the RESTORE on the 2014 database fails with the error :
Msg 15329, Level 16, State 30, Line 2
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.
Naturally I don't want to lose all the encrypted data, so what is the correct statement to run on a 2014 database?
Doug
April 26, 2016 at 9:49 pm
This was removed by the editor as SPAM
April 27, 2016 at 12:59 am
Many thanks. So the Database Master Key is now moved with the database backup unlike versions of SQL Server up to 2008.
April 28, 2016 at 4:15 am
Doug Simmons (4/26/2016)
When I moved my database to an existing sql server 2008, I ran this script on the old server :BACKUP MASTER KEY TO FILE = N'path_to_file'
ENCRYPTION BY PASSWORD = N'oldPassword';
and after transferring the database - on the new server :
RESTORE MASTER KEY
FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'oldPassword'
ENCRYPTION BY PASSWORD = 'newpassword';
and all worked well.
Now I want to move this database from this SQL Server 2008 iteration to an existing 2014 iteration.
The BACKUP of the Master Key on the 2008 database works fine of course, but after transferring the database the RESTORE on the 2014 database fails with the error :
Msg 15329, Level 16, State 30, Line 2
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.
Naturally I don't want to lose all the encrypted data, so what is the correct statement to run on a 2014 database?
Doug
You do not need to move the DMK held in master from one server to another.
Is this a TDE protected database you are moving?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 28, 2016 at 7:23 pm
I remember when I moved a database from a SQL 2000 iteration to a 2008 iteration, I had to Restore the Database Master Key from a backup. I didn't realize that this had changed and wasn't needed when moving from 2008 to 2014. But once I tried to just open the DMK as described in the previous post I could see that the old DMK was still working. So I was then able to change the password and all my encrypted fields are now decrypted correctly on the new server.
Doug
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply