5

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

1 Answer 1

6

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.

5
  • Any idea if running ALTER MASTER KEY FORCE REGENERATE WITH ENCRYPTION BY PASSWORD = 'p@ssw0rd'; to replace the master key and lose the old data will break other databases too, or only the active one? It is not clear to me if this "master key" is the service master key or the active DB master key. When I ran it without the FORCE option I got this and got scared a bit: The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option..... Emphasis on the IF. What else could it be? Jan 3 at 16:13
  • Did you run that command in master or in the TDE-encrypted database?
    – Hannah Vernon
    Jan 3 at 18:55
  • I had it run on the encrypted DB (without the FORCE option) to get this message. And the DB is pretty much empty, if that matters in any way. Jan 4 at 9:22
  • You need to run the ALTER MASTER KEY in the master database, which is where the master key for TDE encrypted databases is stored. It's a little weird, I know. The master key is used to encrypt the certificate used to do the actual TDE encryption. The certificate lives in the TDE database.
    – Hannah Vernon
    Jan 4 at 13:56
  • Also, if you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context.
    – Hannah Vernon
    Jan 4 at 21:36

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.