Database Master Key Password Lost

Question

Post reply

Database Master Key Password Lost

DIB IN

Right there with Babe

Points: 749
More actions
December 30, 2011 at 2:40 am

#391225

I have created database Master Key With password. Created certificate, Symmetric keys. Data has been Encrypted using Key. Now we restore the database back-up to a new server.
Database master key password is lost.
How Do you re generate the keys in new server?
How data is decrypted?
i am new in data Encryption.
Thanks in advance
Regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 1

You can regenerate it.

Example:

USE AdventureWorks2008R2;

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003';

GO

For More: http://technet.microsoft.com/en-us/library/ms186937.aspx

DON'T try it on PROD server until you verify the above on a test server / database.

Perry Whittle SSC Guru Points: 233854 More actions · Answer 2

DIB IN (12/30/2011)
Data has been Encrypted using Key. Now we restore the database back-up to a new server.

Are you usIng transparent data encryption?

DIB IN (12/30/2011)
Database master key password is lost.

Have you created a new master key on the new server?

DIB IN (12/30/2011)
How Do you re generate the keys in new server?
How data is decrypted?

You don't need to, the certificate protects the database. The master key protects the certificate private key. Backup the certificate private and public keys and restore to the new server. Create a new master key on the new server first.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

suchita0805 SSC Veteran Points: 271 More actions · Answer 3

I created master key on non production server to restore encrypted database from production to non production.

So, database got restored successfully.

But lost master key password for non production. please suggest.

thanks

Eddie Wuerch SSChampion Points: 12532 More actions · Answer 4

If the source database is live in production, and the Database Master Key (DMK) has been encrypted by the instance's Service Master Key (SMK), then you can create any number of additional passwords for the Database Master Key inside that database, and use them instead of the lost password:

ALTER MASTER KEY

ADD ENCRYPTION BY PASSWORD = 'your_new_password'

This allows you to give a different password to the DMK to different people, and to then remove a specific password to take away that access without affecting others.

This will allow you to restore any new backups taken after you create the new password(s). Note: If the above statement throws an error because the DMK isn't open when you tried to alter it, then you're going to need to dig up that password.

Once you've created the new password, just take a new backup and use that to restore onto the new server.

Restore and recover the database on the new server with the new backup, and then use the following to re-encrypt the DMK with the Service Master Key of the new SQL Server instance:

OPEN MASTER KEY

DECRYPTION BY PASSWORD = 'your_new_password';

ALTER MASTER KEY

ADD ENCRYPTION BY SERVICE MASTER KEY

Eddie Wuerch
MCM: SQL