August 9, 2017 at 5:26 am
We have always on environment.
Every time that we fail over one of the database, the encryption key must be open manually.
The following commands must be ran in each time the fail over happens
OPEN MASTER KEY DECRYPTION BY PASSWORD and ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
I know i can setup a job to detect the failover and run the above commands but I am wondering is there a configuration on the encryption key that will open the key automatically after the fail over.
Thanks
August 9, 2017 at 5:36 am
was this DMK restored from the primary server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 9, 2017 at 5:45 am
Yes it was
August 9, 2017 at 5:54 am
and no DMK existed on the instance before the key was restored from the primary server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 9, 2017 at 6:46 am
No it didnt from what i am aware of it.
Why, if the DMK was restored from the primary should this not happen?
August 9, 2017 at 7:20 am
okay, golden rule, do not restore DMKs across instances, there is no need to and it's an extra security risk.
Now for the default behaviour with DMks.
To resolve this do not restore DMKs across instances, if you have already restored then use the following--Open the DMK OPEN MASTER KEY DECRYPTION BY PASSWORD = 'blahblahblah'
--now set encryption by the SMK ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Read my article at this link for more info
http://www.sqlservercentral.com/articles/Security/122707/
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 9, 2017 at 7:52 am
The databases is encrypted on the primary so when you restore to the secondary it is also encrypted.
If you backup the service master key from your primary server and then restore it to the secondary server before adding an encrypted database to the AG, you don't have the problem with having to run the open master decrypt routines on failover.
But this wasn't done because when the failover happens the database can't be used until you run
OPEN MASTER KEY DECRYPTION BY PASSWORD and ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Is there anyway around this?
August 9, 2017 at 8:19 am
juniorDBA13 - Wednesday, August 9, 2017 7:52 AMThe databases is encrypted on the primary so when you restore to the secondary it is also encrypted.
If you backup the service master key from your primary server and then restore it to the secondary server before adding an encrypted database to the AG, you don't have the problem with having to run the open master decrypt routines on failover.But this wasn't done because when the failover happens the database can't be used until you run
OPEN MASTER KEY DECRYPTION BY PASSWORD and ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYIs there anyway around this?
You dont backup and move the SMk or the DMK, thats the point.
is this TDE encrypted database?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 9, 2017 at 8:22 am
Yes it is
August 9, 2017 at 9:12 am
juniorDBA13 - Wednesday, August 9, 2017 8:22 AMYes it is
you only need to restore the cert backup to the secondary server.
SMK on the secondary instance is already created.
Create the DMK using a unique password, then restore the cert backup.
to cure the issue you currently have run the tsql i posted above
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply