locked
Service Master Key Initialization RRS feed

  • Question

  • Hi all.

    I am doing a test SQL Server instance migration from 2017 to 2019. This includes migrating the master, model and msdb databases.

    All has gone smoothly, including the user databases and I can connect to the various databases after migration.

    However, upon checking the logs for any hidden errors, I have noticed a couple of lines which concern me a little....

    Service Master Key could not be decrypted using one of its encryptions. See sys.key_encryptions for details

    and

    An error occurred during Service Master Key Intialization. SQLErrorCode=33095, State=8, LastOsError=0.

    Three questions have arisen from this:

    1. What do the errors actually mean in real life? I have queried sys.key_encryptions and can find nothing of note in there to help me identify what's going on
    2. I have read elsewhere that running ALTER SERVICE MASTER KEY FORCE REGENERATE could help in this instance - but can have negative consequences. What are these negative consequences that could possibly happen?
    3. Is there any way to fix this without having to run the command in point 2?

    Thanks in advance.

    Wednesday, January 22, 2020 4:29 PM

Answers

  • yeap, if it is a test and you are doing a side by side migration above i attached the link with the steps that you need to follow. regarding your questions:

    1. What do the errors actually mean in real life? I have queried sys.key_encryptions and can find nothing of note in there to help me identify what's going on -> Means that you didn't restore the service key (stored in the master database or means that the service key is corrupted because of the upgrade)
    2. I have read elsewhere that running ALTER SERVICE MASTER KEY FORCE REGENERATE could help in this instance - but can have negative consequences. What are these negative consequences that could possibly happen? -> Here you have the official doc -> https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql?view=sql-server-ver15  The risk is that if a dependent key cannot be decrypted all the information will be lost. 
    3. Is there any way to fix this without having to run the command in point 2? you will have to backup the existing service key and then restoring it again and see if the issue is fixed.

    Wednesday, January 22, 2020 4:50 PM
  • Hi warnerrj79,

    >>What do the errors actually mean in real life? I have queried sys.key_encryptions and can find nothing of note in there to help me identify what's going on

    This means the service master key did not work in the new SQL server instance. 

    >> I have read elsewhere that running ALTER SERVICE MASTER KEY FORCE REGENERATE could help in this instance - but can have negative consequences. What are these negative consequences that could possibly happen?

    The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly secures all other keys in the tree. If a dependent key cannot be decrypted during a forced restore, data that is secured by that key will be lost. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement. Please refer to this MS document.

    >> Is there any way to fix this without having to run the command in point 2?

    Yes. You can migrate the service master key by using backup and restore. Please refer to BACKUP SERVICE MASTER KEY and RESTORE SERVICE MASTER KEY.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by warnerrj79 Thursday, January 23, 2020 3:38 PM
    Thursday, January 23, 2020 2:50 AM

All replies

  • Hi Warnerrj,

    Are you doing an upgrade in place? or a side by side? if you are migrating from one side to another one you can follow this link -> https://mattsql.wordpress.com/2012/11/13/migrating-sql-server-databases-that-use-database-master-keys/


    Wednesday, January 22, 2020 4:40 PM
  • Hi Warnerrj,

    Are you doing an upgrade in place? or a side by side?

    Hi.

    So at the moment, this is just a test.

    My plan for the real migration is to a side by side migration (migrate a database one at a time over many days), connect any applications to the new instance - then take the old instance database offline.

    Does that make sense?

    Thanks.

    Wednesday, January 22, 2020 4:45 PM
  • yeap, if it is a test and you are doing a side by side migration above i attached the link with the steps that you need to follow. regarding your questions:

    1. What do the errors actually mean in real life? I have queried sys.key_encryptions and can find nothing of note in there to help me identify what's going on -> Means that you didn't restore the service key (stored in the master database or means that the service key is corrupted because of the upgrade)
    2. I have read elsewhere that running ALTER SERVICE MASTER KEY FORCE REGENERATE could help in this instance - but can have negative consequences. What are these negative consequences that could possibly happen? -> Here you have the official doc -> https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql?view=sql-server-ver15  The risk is that if a dependent key cannot be decrypted all the information will be lost. 
    3. Is there any way to fix this without having to run the command in point 2? you will have to backup the existing service key and then restoring it again and see if the issue is fixed.

    Wednesday, January 22, 2020 4:50 PM
  • Hi warnerrj79,

    >>What do the errors actually mean in real life? I have queried sys.key_encryptions and can find nothing of note in there to help me identify what's going on

    This means the service master key did not work in the new SQL server instance. 

    >> I have read elsewhere that running ALTER SERVICE MASTER KEY FORCE REGENERATE could help in this instance - but can have negative consequences. What are these negative consequences that could possibly happen?

    The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly secures all other keys in the tree. If a dependent key cannot be decrypted during a forced restore, data that is secured by that key will be lost. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement. Please refer to this MS document.

    >> Is there any way to fix this without having to run the command in point 2?

    Yes. You can migrate the service master key by using backup and restore. Please refer to BACKUP SERVICE MASTER KEY and RESTORE SERVICE MASTER KEY.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by warnerrj79 Thursday, January 23, 2020 3:38 PM
    Thursday, January 23, 2020 2:50 AM
  • Hi Cathy and Gonzalo

    Both explanations were great and the solutions worked fine.

    Thank you both very much for your help!

    Thursday, January 23, 2020 3:39 PM
  • Hi both again - one final question....

    Is moving the system database files (master, model, msdb etc) from 2017 to 2019 a best practice int the method described above?

    I understand that using backup files to restore will not work as system databases cannot be restored to an instance that is not the same SQL Server version.

    So the only other way I can think of is doing a copy of the mdf and ldf system files and attach to a later versioned instance in single user mode.

    Is this correct or is there a better/safer way?

    Thanks in advance again!

    Thursday, January 23, 2020 5:09 PM
  • I guess that looking at the advice here, scripting out everything to do with system databases (logins, jobs etc) is the preferred method.....
    Thursday, January 23, 2020 5:33 PM