TDE and is_master_key_encrypted_by_server = 1

Question

Post reply

TDE and is_master_key_encrypted_by_server = 1

SQL Bandit

SSCommitted

Points: 1757
More actions
October 19, 2018 at 9:53 am

#416210

Hi there,
I'm after a bit of guidance really.
I have a sales demo environment that I've taken over and I need to encrypt the DB on it. I've taken a snapshot of the VM so I can have a play.
name      is_master_key_encrypted_by_server is_encrypted
-------------------- --------------------------------- ---------------------------------
BanditDEMO              1             0
The Box has is_master_key_encrypted_by_server set to 1. I believe incorrectly.
The BanditDEMO DB has a BanditDEMO has a MS_DatabaseMasterKey and DkimSymKey.
The master DB only has a MS_ServiceMasterKey.
I'm not sure if the passwords to any of the keys. If fact lets just conclude I don't have them.
Given the BanditDEMO Db is not actually encrypted, I'd like to delete the DatabaseMaster key and the BanditCert and start the whole TDE process again myself.
But the BanditDEMO has this is_master_key_encrypted_by_server flag set against it. What should I do, can I delete the DatabaseMaster and the BanditCert, I'm concerned I my break the BanditDEMO DB.
name                                          principal_id symmetric_key_id key_length
-------------------------------------------------------------------------------------------------------------------------------- ------------ ---------------- -----------
##MS_DatabaseMasterKey##                                   1    101     256
DkimSymKey                                        1    256     256
(2 row(s) affected)
name   principal_id pvt_key_encryption_type pvt_key_encryption_type_desc
---------- ------------ ----------------------- ------------------------------------------------------------
BanditCert 256    MK        ENCRYPTED_BY_MASTER_KEY

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

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

SQL Bandit SSCommitted Points: 1757 More actions · Answer 1

Given the Certificate is Encrypted by the master key and the master key is stored inside my BanditDB.

I don't think I can delete the cert or the master key. I think they are interlocked on each other.

I get ... Cannot drop master key because certificate 'BanditCert' is encrypted by it.

I also get The certificate cannot be dropped because one or more entities are either signed or encrypted using it. Then trying to drop the cert.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 2

The master key you are seeing that is showing as being encrypted by the server key, is that on the BanditDEMO database? If so it would appear that key is used for encrypting columns of data inside the BanditDEMO database, not the entire database when it gets written to disk like with TDE. With TDE the master key in question is only on the master database. It has to be because the master key protecting the cert used with TDE needs to be available before the TDE encrypted database comes online; it is the cert used to access the DEK used to decrypt data when reading it from disk.

Joie Andrew
"Since 1982"

SQL Bandit SSCommitted Points: 1757 More actions · Answer 3

I Reset password to the Masterkey that as falsely inside the DB with a ...

ALTER MASTER KEY
FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'MYpassword';

Just for the heck of it so I know its password.

Created a New Master key in Master

Created a New Certificate, leaving the old one there.

Went to create an encryption key then my attempt got rejected because our DEMO box is on standard edition. Doh!!!

All that effort and thinking. That's why nothing was recorded I suspect. Doh! oh well