March 30, 2017 at 7:57 am
Hi Experts,
I am moving a SCCM Orchestrator database which has database master key from Old server to alwayson AG - a primary and a secondary. Different service accounts are used to run SQL services on Old server,both primary and secondary replica.There are also 10 database already exist on AlwaysOn replica with different AG Group.These 10 databases are not encrypted or has no DMK.
SCCM Orchestrator database master key is encrypted by asymmetric key and symmetric key is encrypted by asymmetric key. There is no certificate.
According to MS,I need to backup SMK from old server and restore it in new server.https://technet.microsoft.com/en-us/library/hh913929(v=sc.12).aspx
In my case, diffrent service accounts are used to run SQL services in old server,primary and secodary replicas.
I don't want to restore SMK from the old server to always on server (primary). What is the other workaround to accomplish this? I know a password of DMK from the old server. I have tried the steps below.
I restore the database to Primary server. On primary server, decrypt DMK with the password and encrypt DMK with SMK from primary server. Then add the database to AG group successfully.Then, failover to secondary. decrypt DMK with the password and encrypt DMK with SMK from secondary server (primary now). Failover successful.
But SCCM runbook is getting error.From my research, password and license key are encrypted using DMK in the database.
I used different service accounts running SQL service on Primary and Secondary replica.How does encryption work during failover? Is there any impact on existing 10 databases on Different AG group if I restore SMK from primary to secondary?
Thank you
AM
March 30, 2017 at 10:04 am
why do you have a DMK created in the user database
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 30, 2017 at 11:15 am
Database master key is encrypted by SMK. It is set up according to MS Design. User database has DMK, symmetric and asymmetric keys.
March 30, 2017 at 11:39 am
I am not able to restore SMK from from primary to secondary when different service accounts are running. If both use the same service account I am able to restore. Is it by design?
March 31, 2017 at 11:15 am
ayemya - Thursday, March 30, 2017 11:39 AMI am not able to restore SMK from from primary to secondary when different service accounts are running. If both use the same service account I am able to restore. Is it by design?
The SMk is based on the user account and computername, what is the user DMK for?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply