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?
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
ENCRYPTION BY PASSWORDclause). The same password is specified during the SMK restore (
DECRYPTION BY PASSWORDclause), 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.