Skip to content

Latest commit

 

History

History
114 lines (83 loc) · 6.82 KB

alter-service-master-key-transact-sql.md

File metadata and controls

114 lines (83 loc) · 6.82 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
ALTER SERVICE MASTER KEY (Transact-SQL)
ALTER SERVICE MASTER KEY (Transact-SQL)
VanMSFT
vanto
03/14/2017
sql
t-sql
reference
ALTER_SERVICE_MASTER_KEY_TSQL
ALTER SERVICE MASTER KEY
REGENERATE phrase
FORCE option
ALTER SERVICE MASTER KEY statement
cryptography [SQL Server], Service Master Key
modifying Service Master Key
decryption [SQL Server], Service Master Key
encryption [SQL Server], Service Master Key
service master key [SQL Server], modifying
TSQL

ALTER SERVICE MASTER KEY (Transact-SQL)

[!INCLUDE sql-asdbmi]

Changes the service master key of an instance of [!INCLUDEssNoVersion].

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

ALTER SERVICE MASTER KEY   
    [ { <regenerate_option> | <recover_option> } ] [;]  
  
<regenerate_option> ::=  
    [ FORCE ] REGENERATE  
  
<recover_option> ::=  
    { WITH OLD_ACCOUNT = 'account_name' , OLD_PASSWORD = 'password' }  
    |      
    { WITH NEW_ACCOUNT = 'account_name' , NEW_PASSWORD = 'password' }

[!INCLUDEsql-server-tsql-previous-offline-documentation]

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.

Warning

This option is obsolete. Do not use. Use [!INCLUDEssNoVersion] Configuration Manager instead.

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

Warning

This option is obsolete. Do not use. Use [!INCLUDEssNoVersion] Configuration Manager instead.

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

Warning

This option is obsolete. Do not use. Use [!INCLUDEssNoVersion] Configuration Manager instead.

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

Warning

This option is obsolete. Do not use. Use [!INCLUDEssNoVersion] Configuration Manager instead.

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 [!INCLUDEssNoVersion] service account.

[!INCLUDEssSQL11] uses the AES encryption algorithm to protect the service master key (SMK) and the database master key (DMK). AES is a newer encryption algorithm than 3DES used in earlier versions. After upgrading an instance of the [!INCLUDEssDE] to [!INCLUDEssSQL11] the SMK and DMK should be regenerated in order to upgrade the master keys to AES. For more information about regenerating the DMK, see ALTER MASTER KEY (Transact-SQL).

Changing the SQL Server Service Account

To change the [!INCLUDEssNoVersion] service account, use [!INCLUDEssNoVersion] Configuration Manager. To manage a change of the service account, [!INCLUDEssNoVersion] stores a redundant copy of the service master key protected by the machine account that has the necessary permissions granted to the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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, [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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.

If you move SQL to another machine, then you have to use the same service account to decrypt the SMK - SQL Server will fix the Machine account encryption automatically.

Permissions

Requires CONTROL SERVER permission on the server.

Examples

The following example regenerates the service master key.

ALTER SERVICE MASTER KEY REGENERATE;  
GO

See Also

RESTORE SERVICE MASTER KEY (Transact-SQL)
BACKUP SERVICE MASTER KEY (Transact-SQL)
Encryption Hierarchy