December 7, 2015 at 1:28 pm
Hello,
We are new to encrypting databases so I have been following instructions from a professional who has done it before. The databases will also need to be mirrored. I have just finished encrypting the primary database and when I turned on encryption the mirroring session became suspended. This was expected as I had not restored the master key on the mirror server or created the certificate. I did these two things and according to the instructions the next step is to remove mirroring and restore the encrypted database to the mirror, then open the master key and setup mirroring via tsql like below.
USE MASTER
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'NotThePass'
ALTER DATABASE DatabaseName
SET PARTNER = 'TCP://server@company.com:5022'
GO
CLOSE MASTER KEY
GO
However the other DBA wanted to try something and just resumed mirroring instead of creating a fresh backup and rebuilding the mirror. It worked....they resumed. We failed them over a couple times that went fine too. My question is do you not have to perform the step above anymore? Once a database is encrypted can we just resume mirroring as long as the master key is all setup on the mirror? I know like it seems like I answered my own question but I was curious if this a new feature or I forgot to record one of my steps. This is a 2012 box. Thanks for taking the time to read this.
December 8, 2015 at 8:56 am
lmacdonald (12/7/2015)
This was expected as I had not restored the master key on the mirror server or created the certificate.
You do not need to restore the database master key from one instance to another. Keep an eye out for my article being published over the next day or so which details symmetric and asymmetric key security in SQL Server, it's explained there.
lmacdonald (12/7/2015)
I did these two things and according to the instructions the next step is to remove mirroring and restore the encrypted database to the mirror, then open the master key and setup mirroring via tsql like below.USE MASTER
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'NotThePass'
ALTER DATABASE DatabaseName
SET PARTNER = 'TCP://server@company.com:5022'
GO
CLOSE MASTER KEY
GO
Hmm, this is not correct either, why on earth are you being asked to open the DMK, again it's all explained in my article.
lmacdonald (12/7/2015)
However the other DBA wanted to try something and just resumed mirroring instead of creating a fresh backup and rebuilding the mirror. It worked....they resumed. We failed them over a couple times that went fine too.
Listen to this guy, he knows his stuff by the sound of it, as for the expert???
lmacdonald (12/7/2015)
My question is do you not have to perform the step above anymore? Once a database is encrypted can we just resume mirroring as long as the master key is all setup on the mirror? I know like it seems like I answered my own question but I was curious if this a new feature or I forgot to record one of my steps. This is a 2012 box. Thanks for taking the time to read this.
you keep referencing the DMK, it is not this that encrypts the database, it's the certificate.
As I said, keep any eye out for my article, give it a thorough read
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply