ALTER SERVICE MASTER KEY (Transact-SQL)

SQL Server 2005 Books Online (November 2008)

Changes the service master key of an instance of SQL Server.

Topic link icon Transact-SQL Syntax Conventions

Syntax

ALTER SERVICE MASTER KEY 
    [ { <regenerate_option> | <recover_option> } ]
    |
    [ { ADD | DROP } ENCRYPTION BY MACHINE KEY ]

<regenerate_option> ::=
    [ FORCE ] REGENERATE

<recover_option> ::=
    { WITH OLD_ACCOUNT = 'account_name' , OLD_PASSWORD = 'password' }
    |    
    { WITH NEW_ACCOUNT = 'account_name' , NEW_PASSWORD = 'password' }

Arguments

FORCE: Indicates that the service master key should be regenerated, even at the risk of data loss. For more information, see Changing the SQL Server Service Account later in this topic.

REGENERATE: Indicates that the service master key should be regenerated.

OLD_ACCOUNT = 'account_name': Specifies the name of the old Windows service account.

OLD_PASSWORD = 'password': Specifies the password of the old Windows service account.

NEW_ACCOUNT = 'account_name': Specifies the name of the new Windows service account.

NEW_PASSWORD = 'password': Specifies the password of the new Windows service account.

Remarks

The service master key is automatically generated the first time it is needed to encrypt a linked server password, credential, or database master key. The service master key is encrypted using the local machine key or the Windows Data Protection API. This API uses a key that is derived from the Windows credentials of the SQL Server service account.

The service master key can only be decrypted by the service account under which it was created or by a principal that has access to the Windows credentials of that service account. Therefore, if you change the Windows account under which the SQL Server service runs, you must also enable decryption of the service master key by the new account.

Changing the SQL Server Service Account

To change the SQL Server service account, use SQL Server Configuration Manager. To manage a change of the service account, 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. When you are moving SQL Server to another computer, migrate the service master key by using backup and restore.

The REGENERATE phrase regenerates the service master 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. This is a resource-intensive operation. You should schedule this operation during a period of low demand, unless the key has been compromised. If any one of the decryptions fail, the whole statement fails.

The FORCE option causes the key regeneration process to continue even if the process cannot retrieve the current master key, or cannot decrypt all the private keys that are encrypted with it. Use FORCE only if regeneration fails and you cannot restore the service master key by using the RESTORE SERVICE MASTER KEY statement.

Caution:
The service master key is the root of the SQL Server encryption hierarchy. The service master key directly or indirectly protects all other keys and secrets in the tree. If a dependent key cannot be decrypted during a forced regeneration, the data the key secures will be lost.

The MACHINE KEY options allow you to add or drop encryption using the machine key.

Permissions

Requires CONTROL SERVER permission on the server.

Examples

The following example regenerates the service master key.

ALTER SERVICE MASTER KEY REGENERATE;
GO

Changing the SQL Server Service Account

Reference

Other Resources

Help and Information