2

I created a master key, then a certificate and encrypted my database.

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='A Very Strong Password';
GO

CREATE CERTIFICATE foo
WITH 
SUBJECT='Database Encryption for Multi';
GO

USE mydatabase
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE foo;
GO

ALTER DATABASE mydatabase SET ENCRYPTION ON;
GO

How do I change the MASTER KEY password?

Thanks

3

According to the Microsoft Docs page, syntax is:

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password';

From that docs page:

The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key. This resource-intensive operation should be scheduled during a period of low demand, unless the master key has been compromised.

However, ensure you understand the ramifications of changing the key, and make sure you backup the new key.

Check my recent post for more details.

  • Thanks, Max. i saw that in the docs, but it didn't specify whether or not 'password' is the old password or the new password. And if it's the new password, why doesn't it ask for the old password? (That's what really confused me.) – RonJohn Mar 26 at 12:51
  • You're actually not modifying the existing master key with ALTER MASTER KEY REGENERATE. You're really decrypting anything that relies on the key, the dropping the old key and creating an entirely new key, then using that new key to re-encrypt everything that was encrypted previously with the old key. – Max Vernon Mar 26 at 14:58
  • That's what I was afraid of: you don't seem to need the old password to reencrypt the databases. How could MS have ignored/overlooked that? – RonJohn Mar 26 at 15:50
  • 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. – Max Vernon Mar 26 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.