Transact-SQL Syntax ConventionsChanges the service master key of an instance of SQL Server
Transact-SQL Syntax Conventions
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' } 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 SQL Server Configuration Manager instead.
OLD_PASSWORD =’password’
Specifies the password of the old Windows service account.
[!WARNING]
This option is obsolete. Do not use. Use SQL Server 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 SQL Server Configuration Manager instead.
NEW_PASSWORD =’password’
Specifies the password of the new Windows service account.
[!WARNING]
This option is obsolete. Do not use. Use SQL Server Configuration Manager instead.
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.
SQL Server 2012 (11.x) 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 SQL Server 2012 (11.x) Database Engine to SQL Server 2012 (11.x) Database Engine SQL Server 2012 (11.x) 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).
To change the SQL Server service account, use SQL Server SQL Server Configuration Manager. To manage a change of the service account, SQL Server SQL Server 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 SQL Server SQL Server 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 SQL Server SQL Server SQL Server 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.
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.
Requires CONTROL SERVER permission on the server.
The following example regenerates the service master key.
ALTER SERVICE MASTER KEY REGENERATE;
GO RESTORE SERVICE MASTER KEY (Transact-SQL)
BACKUP SERVICE MASTER KEY (Transact-SQL)
Encryption Hierarchy