How to resolve missing Database Master Key Password while Migrating databases in SQL Server

This video helps you in resolving the real time issues with Database Master Key. Whenever we are performing Migration on the database where column level encryption is in place and we have a situation where password is not there with DBA or APP teams. Also whenever a database gets failed over from primary to secondary replica in always on we will encounter the same error message.
Please create a master key in the database or open the master key in the session before performing this operation.
you don't need to regenerate or force it. you can use the technique that I am showing in this video

[@eminocak61]

This may be the most useful video I've watched recently, thank you.

[@chindamparamsh6091]

Wonderful Session Vamsy...TY Again

[@urvxfvdzrnp]

Excellent session 👍

[@mustafaxodayev6799]

Very informative, thanks. But how to get information what exactly is encrypted inside database using DMK?

[@sumuduroshini5222]

Excellent explanation and many thanks! What if there is no DMK but the column is encrypted by a private key in certificate and symmetric key is encryptedby certificate only? how to then decrypt without private key in certificate after restoring the db to new server?

[@vamsychiranjeevi9619]

I think I didn't get your question or you have not formed properly. can you re-phrase it?

[@RobertQC]

Excellent video, what happens if I only have one backup since server 1 was completely lost (reason: virus) I only have the backup available?

[@babban2020]

Thanks!

[@babban2020]

Vamsy, Younus here...I recently started watching your vids...very informative. Please give me the series link for Azure SQL plus SMI...keep up the good work..God bless you and your family for all ur efforts...

[@user-hf6fl2ph8d]

I have added password for dmk on source server then migrated to destination server now i have opened master key using new password now application team will perform transactions can i close symmetric or remains it has open state only
After adding password we have opened master key symmetric as well still getting same error from applocation side

[@rahuldongare4627]

This issue is never happened in SQL Server 2008 R2, but Issue occured in SQL Sever 2016 and above, why?

[@vamsychiranjeevi9619]

For sure this is not Version specific. There should be something missing.

[@rahuldongare4627]

SQL Sever 2008 R2 is a 32 bit machine, Is this the cause?

[@bhanuprakash8400]

In case source server is down. We restored db from available backups on target server. In this case we cant take dmk with new password. How to make it work on target server without dmk password?

[@vamsychiranjeevi9619]

you can't make it work with out DMK. If your source itself is gone then restore on some other server change the DMK and then perform the backup

[@tarunsagar8141]

I have migrated some databases and landed in same issue, so to resolve it I took backup of SMK from old server and tried restoring on new server. but I got below error:
"An error occurred while decrypting master key "databasename" 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.
I don't want to use force option, what should be the solution.

[@vamsychiranjeevi9619]

At the end of the video I have shown a solution where you can create one more password for DMK please watch that

[@tarunsagar8141]

@@vamsychiranjeevi9619 that would be created at source server I believe before migration. but there are some changes made to migrated database on new server already so I cannot restore it, also old server is decommissioned now..

[@tarunsagar8141]

there are 3 databases which are encrypted.
I may get password of DMK for all, what would be the procedure after that.

[@vamsychiranjeevi9619]

@@tarunsagar8141 it should be like the below if you are aware of the password .... you need to change your symmetric key name , certificate name and the password
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'migration_password@143$$'
OPEN SYMMETRIC KEY Table1_Sym_Key DECRYPTION BY CERTIFICATE Table1_Certificate;
SELECT Customer_ID,Customer_PWD AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(Customer_PWD)) AS 'Decrypted Customer Password'
FROM Table1;

[@tarunsagar8141]

@@vamsychiranjeevi9619 I get that, but I am not selecting data I have to restore SMK on new server.
Also I have three databases encrypted here..
use [database1]
open master key decryption by password = 'password'
OPEN SYMMETRIC KEY Key DECRYPTION BY CERTIFICATE Certificate
use [database2]
open master key decryption by password = 'password'
OPEN SYMMETRIC KEY Key DECRYPTION BY CERTIFICATE Certificate
use [database3]
open master key decryption by password = 'password'
OPEN SYMMETRIC KEY Key DECRYPTION BY CERTIFICATE Certificate
use [Master]
RESTORE SERVICE MASTER KEY FROM FILE = 'X:\servicekeybkp\service_master_key'
DECRYPTION BY PASSWORD = '***********'
CLOSE MASTER KEY;
CLOSE MASTER KEY;
CLOSE MASTER KEY;
will that be correct approach?

[@vasanth7507]

Can please explain azure migration process on premises to azure vm

[@vamsychiranjeevi9619]

Hi, I covered this during my training on Azure. if you are interested ping me on WhatsApp [phone]