SQL Server 2012 Service Master Key Regenerate

Question

I have a new SQL Server 2012 instance and when I run the following command I get an error.

ALTER SERVICE MASTER KEY REGENERATE

Error:

Msg 15329, Level 16, State 1, Line 1
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 can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

When I try with the Force option I get:

The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

Error:

Msg 33096, Level 16, State 1, Line 1
A generic failure occurred during Service Master Key encryption or decryption.

Any ideas?

Answer 1

In order to regenerate your master key, you need to open it with the old password. Then, once it's open, you can use the REGENERATE syntax to change it to a new password:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '0ldP@55w0rd';

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'N3wP@55w0rd';

CLOSE MASTER KEY;

Which is why you should always have your encryption passwords safely secured. Without them, you could be in a place where you can't decrypt your databases.