How to change SQL Server master key password?

Question

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

Answer 1

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.

Some notes:

1. You're actually not modifying the existing master key with ALTER MASTER KEY REGENERATE. You're really decrypting anything that relies on the key, then 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.

2. 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.

3. 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.