TDE master key password change

Question

TDE master key password change

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
August 18, 2022 at 4:02 pm

#4078007

Hello
I have a requirement to change the TDE master key password in one of the environments. Do I need to redo the whole process (encrypt the dbs with the new master key password) again after I regenerate the master key password?

Kindly advice
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

You shouldn't. ALTER MASTER KEY WITH REGENERATE should decrypt the protection on the cert and then re-encrypt it with the new key. The cert protects the DEK, which isn't something you need to change

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-master-key-transact-sql?view=sql-server-ver16

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Thanks, Steve!

So after I regenerate the master key, do I also need to take the backup of the "service master key" and the "backup master key" steps? or the old backup files will still work?

The reason is to change the password of TDE in every environment after the VM is rebuilt...so this question came up.

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 3

Should have a backup of the cert periodically.

I don't worry about the SMK, as this can be overridden, so it's more if you plan to restore this exact server setup again without some snapshot.

For the master key, the old backup would not work if you needed to restore it to get to the cert, but since this master key is in the master db, I'm not sure it's a concern for backups. You can take one if you like. More, having a cert backup is the important thing. You can always restore the cert under a new master key and it (the cert) will work with the DEK

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

The certificate was backed up using the old master key password. The question is, if I change the master key password, do I need to take the certificAte backup again?

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 5

The cert backup isn't protected by the master key. The cert is.

The backup is protected by a password when you run it.

Jameswalter Newbie Points: 8 More actions · Answer 6

You need to be a member of the sysadmin group before you can change the master key. Also, changing the master key doesn't decrypt and re-encrypt any TDE-encrypted database, it simply encrypts and re-encrypts certificates that have been encrypted with the key. Those certificates remain valid and are not modified in any way; simply they are protected with a new master key.

Saving passwords in scripts is always a problem. I've addressed that in the past by treating the certificate-related scripts as protected, the same way I protect TDE keys. In source code control systems, it's necessary to apply access controls to the scripts to ensure only authorized sysadmins can access them.