0

I have migrated sql server 2017 to new hardware. The sql error log shows following error log whenever the sql server service is restarted.

An error occurred during service master key initialization

I recently restored the master, msdb databases from older hardware to new hardware. Linked servers don't work for example if I try to create a linked server it gives following error: An error occurred during service master key decryption. Same error when creating credential.

I understand this is something related to the SMK (service master key).

Doesn't the SMK already exist since I have restored the master database (assuming the SMK is held in the master db)?

Why am I getting these errors? Why can I not create/access linked sever and credentials?

8
  • Are you using the same service account? If not, you need to backup and restore the service master key separately because it's protected by the service account.
    – Dan Guzman
    Aug 16, 2022 at 18:34
  • Using different service account. Should I restore the SMK before or after restoring the master and msdb?
    – variable
    Aug 16, 2022 at 18:46
  • Restore the SMK after restoring master. That will allow decrypting of the secrets it protects.
    – Dan Guzman
    Aug 16, 2022 at 20:52
  • MSDN says - When the service master key is restored, SQL Server decrypts all the keys and secrets that have been encrypted with the current service master key, and then encrypts them with the service master key loaded from the backup file. - after restoring the master db, and since my sql service account is different, how will it be able to decrypt the keys and secrets that have been encrypted with the current service master key
    – variable
    Aug 17, 2022 at 4:39
  • When you backup the SMK from the source, you provide a password to protect it (ENCRYPTION BY PASSWORD clause). The same password is specified during the SMK restore (DECRYPTION BY PASSWORD clause), which allows decryption of existing secrets. The secrets are then re-encrypted using the restored SMK and the SMK is protected with the SQL Server service account.
    – Dan Guzman
    Aug 17, 2022 at 9:17

0

Your Answer

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