Australian cloud for business software — built and run in Sydney since 2003

+61 2 8282 5656
support@netleverage.com
Talk to an engineer
Talk to an engineer
NetLeverage
NetLeverage

SQL Server's service master key: linked servers, credentials and new hardware

Technical article
Backup & Recovery

Originally published:

About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

When a SQL Server is rebuilt or its databases move to new hardware, most plans cover user databases, logins and jobs. Fewer cover the service master key. It is small, created automatically and rarely seen, yet it quietly protects several things an application may depend on.

This guide is for DBAs planning a move or a rebuild of SQL Server on Windows. It explains what the key protects, how Windows protects it, what fails without it, and what to check before the day. It complements, rather than repeats, guidance on certificates for backup encryption and Transparent Data Encryption (TDE).

Why it matters

Microsoft's key overview says: "The service master key is the root of the SQL Server encryption hierarchy." It continues: SQL Server "uses it to encrypt linked server passwords, credentials, and the database master key in each database."

So three kinds of item depend on it:

  • Linked server passwords: stored remote logins for linked servers.
  • Credentials: server-level stored identities, often used by SQL Server Agent proxies or for access to outside resources (our understanding).
  • Database master keys (DMKs): specifically, the copy that lets SQL Server open each DMK automatically.

A user-database backup does not carry the instance's linked-server definitions, login mappings, server credentials, Agent jobs or proxies. Those objects and their required secrets need a separate inventory and supported reconstruction plan. Restoring the SMK does not recreate them, and DMK recovery/provisioning must be tested separately for each database.

How Windows protects the key

The overview explains: "The SMK is encrypted by using the local machine key or the Windows Data Protection API (DPAPI)." Further, "DPAPI uses a key derived from the Windows credentials of the SQL Server service account." As a result, "the service master key can be opened by the service account under which it was created, or by a principal that has access to the machine credentials."

This ties the key to a machine and a service account. The ALTER SERVICE MASTER KEY reference describes a safety net and its limits:

  • "SQL Server stores a redundant copy of the service master key protected by the machine account that has the necessary permissions granted to the SQL Server service group."
  • "If the computer is rebuilt, the same domain user that was previously used by the service account can recover the service master key."
  • "This does not work with local accounts or the Local System, Local Service, or Network Service accounts."

For a move to different hardware, the same page is direct: "When you are moving SQL Server to another computer, migrate the service master key by using backup and restore."

There is a small inconsistency in Microsoft's wording about when the key appears. The overview says it is generated "the first time the instance starts", the ALTER reference says it is "automatically generated the first time it is needed", and the encryption hierarchy page says it "is created by SQL Server setup". In practice, assume every instance has one (our reading).

What breaks on a new server without it

Database master keys stop opening automatically

The overview explains the default: "To enable automatic decryption of the database master key, a copy of the key is encrypted by using the SMK. SQL Server stores that copy in both the database where the key is used and in the master system database."

When a database moves, its copy arrives but the new instance's service master key is different. The OPEN MASTER KEY reference describes what happens: "When a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK)."

That needs the DMK password. After opening it, Microsoft says "you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK)." The ALTER MASTER KEY syntax also offers ADD ENCRYPTION BY SERVICE MASTER KEY.

Without the DMK password, anything that relied on automatic opening, such as certificates protected by the DMK and the data they protect, may be unusable on the new server (our reading). The overview states what the DMK does: it "protects the private keys of certificates and asymmetric keys that are present in the database."

Linked server passwords and credentials

Stored linked-server passwords and server-credential secrets are protected by the instance's SMK. Their server-level definitions are a separate dependency: restoring user databases and importing an SMK does not create linked servers, mappings, credentials, Agent jobs or proxies on a fresh instance. Inventory and export/recreate those definitions using supported procedures; obtain and provision current secrets through an authorised protected channel. Catalog inventories do not export passwords.

An absent object, an existing secret that cannot decrypt, a rejected remote password and a network/permission failure are different faults. Check them separately. SMK restore re-encrypts keys and secrets already present on the target; it is not a server-object migration package or an automatic fix for every restored database's DMK (our reading).

Back up, restore, regenerate: when each applies

Back up

The backup page is clear: "Back up the service master key as soon as SQL Server generates it, and store the backup in a secure, off-site location." The syntax is:

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

It warns: "You need the password to restore the service master key." Store the file and its password separately, and record where each is kept in your recovery documentation (our recommendation). Treat the backup as a secret: it protects stored passwords for other systems (our reading).

Restore

The restore page opens with a warning: "It's unlikely that you ever need to restore the service master key." When you do, "SQL Server decrypts all the keys and secrets encrypted with the current service master key, and then encrypts them with the service master key loaded from the backup file."

The FORCE option needs care. Microsoft says: "You can use the FORCE option to ignore errors, but this option causes the loss of any data that can't be decrypted." And: "If a dependent key can't be decrypted during a forced restore, data secured by that key is lost."

A documented use is planned migration to a new computer. A normal restore attempts to decrypt the target's existing protected keys/secrets and re-encrypt them with the imported SMK; it fails if required decryptions fail. It does not import missing linked-server definitions, mappings, credentials, jobs or proxies. Plan their reconstruction separately, back up target key material before a planned key change, and test DMK password opening and target-SMK encryption for each restored database under its version-specific procedure. Do not use FORCE to make a migration rehearsal appear successful (our recommendation).

Regenerate

ALTER SERVICE MASTER KEY REGENERATE creates a new key. "When the service master key is regenerated, SQL Server decrypts all the keys that have been encrypted with it, and then encrypts them with the new service master key." Microsoft notes it "is a resource-intensive operation" and to schedule it "during a period of low demand, unless the key has been compromised."

It is all or nothing: "If any one of the decryptions fail, the whole statement fails." The FORCE variant continues anyway, and Microsoft says to "Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement." Regeneration replaces the key, so take a fresh backup afterwards (our recommendation).

Changing the service account

Microsoft states: "To change the SQL Server service account, use SQL Server Configuration Manager." Changing it by other means can leave the service unable to open its key through DPAPI (our understanding).

Read-only checks

These queries read catalog views and change nothing. Run them as Transact-SQL in SQL Server Management Studio or sqlcmd on Windows, against the instance being moved. Have an authorised DBA run them with permissions sufficient for the installed version and record the permission scope. Metadata can be silently filtered without an error: empty or incomplete rows do not prove the objects are absent. Protect the inventory, which can expose infrastructure and account names.

Databases reporting SMK-encrypted master-key state:

SELECT name, is_master_key_encrypted_by_server
FROM sys.databases
WHERE is_master_key_encrypted_by_server = 1;

Microsoft describes the column as "1 = Database has an encrypted master key". This is catalog state, not proof that the target can decrypt the DMK or use a dependent certificate. Confirm DMK password custody and have the DBA test the actual required decryption using permitted test data after restore, including target-SMK provisioning where required (our recommendation).

Linked servers and their login mappings:

SELECT s.name, s.product, s.data_source, l.remote_name, l.uses_self_credential
FROM sys.servers AS s
LEFT JOIN sys.linked_logins AS l ON l.server_id = s.server_id
WHERE s.is_linked = 1;

sys.linked_logins "Returns a row per linked-server-login mapping". Where uses_self_credential is 0, Microsoft says the "session uses the name and password that are supplied"; the password is not exposed through the catalog view. Visibility depends on permissions and mappings. The sys.servers and sys.linked_logins documentation specifies VIEW SERVER SECURITY STATE for SQL Server 2022 and later; use the direct view references below for the installed version's full visibility rules.

Credentials:

SELECT name, credential_identity, create_date
FROM sys.credentials;

It "Returns one row for each server-level credential". The cited page requires either VIEW ANY DEFINITION or ALTER ANY CREDENTIAL, and additionally says the principal must not be denied VIEW ANY DEFINITION. These rows identify credentials; they do not return recoverable secrets.

Symmetric keys in master:

SELECT name, create_date, modify_date
FROM master.sys.symmetric_keys;

Inspect the visible key inventory, including the service master key where exposed. Creation and modification dates are clues for investigating backup freshness, not proof that a particular backup is current or usable. Record the backup identity, password custody and the result of an authorised restore test. sys.symmetric_keys metadata can also be filtered by permissions.

Service account. In SQL Server Configuration Manager, note the account running the Database Engine and whether it is a domain account. The rebuild safety net above does not apply to local or built-in accounts.

These checks tell you what depends on the key. They do not prove a backup file or password works (our reading).

Safe restore rehearsal

Use an authorised, isolated test instance with no network path to the real linked servers, and a test instance as the source with test credentials and test linked server logins (our recommendation).

  1. Prepare a disposable source with a test linked server and login mapping, a test credential and any test job/proxy that uses it, and a test database with a DMK, certificate and known encrypted fixture. Remote endpoints must also be isolated test systems.
  2. Protect all dependencies separately: the user-database backup, SMK backup and its password, DMK backup/password material, server-object definitions and authorised test-secret records. A catalog list is an inventory, not a password export. Record the fixture's expected result at the backup point.
  3. Restore and reconstruct on a fresh disposable target. Restore the user database; explicitly recreate the linked server, mappings, credential and test jobs/proxies from the recorded definitions and provision their current test secrets. Record missing-object failures separately from decryption, authentication and network failures.
  4. Test the DMK route under the DBA's version-specific plan: open it with its password, add encryption by the target SMK where appropriate, then test the dependent certificate/decryption and application operation against the known fixture. Do not infer success from a catalog flag.
  5. If testing SMK migration, use a separate clean, authorised disposable target, back up its existing key material, and perform the supported normal SMK restore without FORCE. Reconstruct the same server objects and provision test secrets explicitly; restore and test each database's DMK requirements separately. Importing the SMK alone is not expected to create objects or guarantee automatic opening of every restored DMK.
  6. Verify and record the required test linked-server query, credential-backed operation and fixture decryption on each tested path. Record exact backup identities, passwords needed, outcomes and elapsed time.

Diagnosing what you find

  • Certificates or encrypted columns fail after a move: the DMK is no longer opened automatically; the DMK password is unknown; or the certificate was protected by a password, not the DMK.
  • Linked server queries fail with login errors: the stored password could not be decrypted; the remote password changed since the backup; or the mapping was never moved.
  • Agent jobs using proxies fail: the credential secret could not be decrypted; the identity no longer exists on the new domain; or the proxy was not recreated.
  • Service master key restore fails: the backup password is wrong; the file is from a different instance than expected; or existing secrets on the target cannot be decrypted. Avoid FORCE until the loss it causes is understood.

Worked example (illustrative)

The following is invented. A reporting server is moved to new hardware. Databases and logins are restored and tested. On the first night, three reports fail, all using a linked server to a finance system, and an export job using a credential stops. One database also cannot open its certificate.

In this invented case, the linked-server mapping and Agent proxy had not been recreated, the credential needed a current external secret, and the restored database's DMK required password opening and target-SMK encryption. The absence of an SMK backup was a recovery gap, but it did not explain every failure. The team reconstructs the missing definitions from approved records, provisions current secrets through their authorised custodians, recovers the DMK password and applies the supported target-key procedure. A separate isolated reproduction with test endpoints then verifies the report query, credential-backed export and known encrypted fixture. The recovery register now covers object definitions, secret custody and tested key backups separately. This is an illustrative outcome, not an author-run lab result.

Risks and limits

  • Encryption behaviour differs in Azure SQL services and on Linux.
  • TDE and backup encryption certificates have their own recovery needs; see related guidance.
  • No command in this guide was run in a lab. Test first in an isolated environment.

Sources

All accessed 14 November 2026.

Related future topics

  • SQL Server Agent proxies and credentials in a rebuild
  • Linked servers: documenting and testing remote logins
  • A recovery register for database secrets and who holds them

This article was originally published on .