Can SMK be restored without data loss when target server has the master db restored from source server and is using a different sql svc account?

Question

Assume I have sql server 1 (crashed unrecoverable OS) and sql server 2 (fresh install). Server 2 uses a sql service account that is different to server 1.

Assume I have the SMK backup file from server 1, created using the following command:

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'   
    ENCRYPTION BY PASSWORD = 'password'

Suppose I want to rebuild sql server 1 onto sql server 2. Then as 1st step say I restore the master db. Then to restore the SMK:

RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file'     
DECRYPTION BY PASSWORD = 'password'

The documentation about RESTORE SERVICE MASTER KEY says: https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-service-master-key-transact-sql?view=sql-server-ver16#remarks

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.

Since the sql svc account is different on the second server, it won't be able to decrypt the existing SMK from master db. Therefore it cannot decrypt the secrets/keys.

The solution is to use force keyword. I have tried this but want some clarity on how the force keyword works because the documentation mentions chance of data loss:

Forces the replacement of the service master key, even at the risk of data loss.

I found a webpage that says this (force) doesn't work - https://www.sqlservercentral.com/forums/topic/cannot-restore-service-master-key. But everyone on that page seem confused and miss the point that it is a simple case of missing permissions. So this example is irrelevant and can be ignored. (I only put this example here to help others who are in this situation)

Couple of links below that says this (force) just works;

• https://inframon7.rssing.com/chan-70065456/article10.html

• http://sqlservercorner.blogspot.com/2010/01/restore-service-master-key.html?m=1

• https://blog.topqore.com/system-center-orchestrator-2012-license/

When I run the RESTORE without force keyword I get following error which is understandable:

The current master key cannot be decrypted. If this is a database master key, you should attempt attempt open it in a session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.

When I try with the Force option, I get following output (note this is not an error. It is a success message output):

The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

I tested the linked sever, credentials on the server 2 and the restore seems to have worked fine.

Clearly as per above message the restore initially failed and then it did a force replacement. This question is to ask when/what exactly does it mean by risk of data loss upon using the force keyword?

Answer 1

What exactly is your question?

The documentation for RESTORE SERVICE MASTER KEY states:

If a dependent key cannot be decrypted during a forced restore, data that is secured by that key will be lost.

The documentation for service master key says the following (my emphasis):

The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

If you have access to the Windows credentials (that is, the user name and password) from the original service account, you won't have data loss. That appears to support your conclusion.

I've voted to close this, but I'm biased because I work at Microsoft on the SQL Docs content team, so I want to make sure that the documentation on this is clear. You have the credentials, so you can recover the SMK, so you can restore your data.