0

I understand that the SMK is used to encrypt sensitive data like passwords in the linked server configuration.

Hence when I migrate or upgrade the sql server to another computer, then I assume I should backup and restore the SMK so that the linked servers continue to work.

However I cannot find any msdn documentation that specifies this. Is this (backup/restore of SMK) an optional step?

1 Answer 1

2

The service master key should be backed up and stored in a secure, off-site location. Creating this backup should be one of the first administrative actions performed on the server.

Backup: https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-service-master-key-transact-sql?view=sql-server-ver16

Restore: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/restore-the-service-master-key?view=sql-server-ver16

For what I know, starting from SQL Server 2012, SQL Server uses the AES encryption algorithm to protect the service master key (SMK) and the database master key (DMK). After upgrading an instance of the Database Engine to SQL Server 2012 or above the SMK and DMK should be regenerated in order to upgrade the master keys to AES using ALTER MASTER KEY.

3
  • Can you point me to any documentation which mentions about the SMK with respect to upgrade on same server or migrate to another server?
    – variable
    Jun 7 at 6:08
  • learn.microsoft.com/en-us/sql/t-sql/statements/… this is the only reference I have found in ms doc
    – MBuschi
    Jun 7 at 6:14
  • Yes surprising that this information isn't part of any upgrade/migration documentation. I assume this is because they probably assume that the service account will remain the same which doesn't require the SMK to be restored (If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key.)
    – variable
    Jun 7 at 6:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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