2

We recently migrated our database over from SQL Server 2012 to a new SQL Server 2019 box. As soon as the database was migrated, I regenerated the master key, but whenever our SQL Server 2019 box reboots, I have to regenerate the master key before our Symetric Key starts working. Once the master key has been regenerated, everything works until the next reboot. The specific error we receive is:

System.Data.SqlClient.SqlException (0x80131904): Please create a master key in the database or open the master key in the session before performing this operation. The key 'symmetric key' is not open. Please open the key before using it.

I realize the error message pertains to the Symmetric Key, but regenerating the Master Key fixes the issue. What can I do to stop having to regenerate the Master Key after every server reboot. I feel like I am missing something obvious.

1 Answer 1

0

I believe the error message is indicating you to follow the procedures from the OPEN MASTER KEY documentation in order not to go through that situation after every reboot.

The Remarks section of the doc says:

If the database master key was encrypted with the service master key, it will be automatically opened when it is needed for decryption or encryption. In this case, it is not necessary to use the OPEN MASTER KEY statement.

When a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK). When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY (Transact-SQL). The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy.

You can exclude the Database Master Key of a specific database from automatic key management by using the ALTER MASTER KEY statement with the DROP ENCRYPTION BY SERVICE MASTER KEY option. Afterward, you must explicitly open the Database Master Key with a password.

3
  • This is the exact procedure I followed after the migration. Commented Dec 6, 2022 at 22:36
  • @TimothyRitter, Have you tried to use the DROP ENCRYPTION BY SERVICE MASTER KEY and ADD ENCRYPTION BY SERVICE MASTER KEY options? (I've never done this procedure, so I recommend you try it first on a non production backup of the problematic database)
    – Ronaldo
    Commented Dec 7, 2022 at 1:10
  • No I have not. I'll give it a try on one of our non-prod boxes and let you know if it works. Thanks! Commented Dec 8, 2022 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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