0

Link: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql?view=sql-server-ver15

The service master key is automatically generated the first time it is needed to encrypt a linked server password, credential, or database master key.

Does this mean that if I restore the system dbs onto a new server (as part of migration or recovery), then will the linked servers stop working (since the new server won't have the same service master key)?

Improve this question
3
  • What method are you going to use to restore the system dbs on the new server?
    – Ronaldo
    May 12 at 1:00
  • I am restoring the master database using the restore command
    – variable
    May 12 at 1:04
  • were you able to test the restoring method I suggested?
    – Ronaldo
    May 13 at 11:05

1 Answer 1

Reset to default
0

It seems you're gonna have to generate the scripts for the linked servers as you can see on How to Transfer Linked Servers from one server to another automatically?, therefore, if you follow the Restore the master Database doc before executing the generated scripts on the new instance, the linked servers recreated will be encrypted with the same service master key from the old server.

According to what the How to Create a Service Master Key in SQL Server article says,

The service master key is a symmetric key stored in the master database. As such it can be found using the sys.symetric_keys catalog view in the that very database.

So, I'd say the linked servers won't stop working since the service master key would be restored along with the master database. To make sure that's the way it works you could easily test the procedure:

  1. Backup the master database on the old instance;
  2. Generate the linked server scripts;
  3. Restore the master database on the new instance;
  4. Execute the scripts for the linked servers on the new instance;
  5. Test if the linked servers are still working.

Also, it's recommended to Back Up the Service Master Key.

Improve this answer
5
  • The linked server scripts doesn't export the password so this is not a good approach.
    – variable
    May 13 at 11:41
  • @variable, I thought the scripts were the only approach to migrate when it comes to linked servers. How did you export them? Because if they were exported along with the backup and restore process, I believe they would be working from the beginning.
    – Ronaldo
    May 13 at 12:43
  • 1
    The linked server automatically show up once you restore the master db. However, the sensitive passwords in the linked server can be decrypted only if the sql service user is the same as the previous server. If the sql service account is different, then you have to backup the SMK from original server and restore on the target server. The original SMK has to be restored on the target for various other sensitive things to work like credentials.
    – variable
    May 13 at 12:46
  • Yes, I see. One thing is not clear to me: were you able to perform that restore and everything is working now or do you still have the problem?
    – Ronaldo
    May 13 at 13:42
  • Yes all ok with my test
    – variable
    May 13 at 15:00

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.