Sqlserver: Change the master key of an SQL server instance.

Known problems: Anything related to encryption involves the sqlserver server master key. Therefore, the issue of "error 15466 during decryption" is involved. You can reset the master key of the server.

Note: This operation is computation-intensive !!!! Encrypted data may be lost!

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'}

Parameters

Force

Indicates that the Service master key should be regenerated even if there is a risk of data loss. For more information, see changing SQL statements below this topic
Server Service account.

Regenerate

Indicates that the Service master key should be regenerated.

Old_account = ' Account_name '

Specify the name of the old Windows service account.

Old_password = ' Password '

Specify the password of the old Windows service account.

New_account = ' Account_name '

Specify the name of the new Windows service account.

New_password = ' Password '

Specify the password of the new Windows service account.

Note

Use the local computer key and windows
Data Protection API encrypts the Service master key. This API uses the secret key derived from the Windows creden。 of the SQL Server service account.

WhenFirst timeThe CMK is automatically generated when you encrypt the password, credential, or database master key of the linked server using the CMK.

Service master keyIt can only be decrypted by the service account used to create it, or by the subject of Windows creden that can access the service account.. Therefore, if you change
The Windows account used by SQL Server must also enable the new account to decrypt the Service master key.

Change an SQL Server service account

To change the SQL Server service account, use the SQL Server Configuration Manager. To manage changes to service accounts, SQL Server
The redundant copy of the Storage Service master key, which is assigned to the SQL Server
The computer account of the service group that has the necessary permissions to protect it. When you recreate a computer, you can restore the Service master key for the same domain user that the Service Account previously used. This does not apply to local accounts
System, local service, or network service account. If you want
To migrate to another computer, use the backup and restoration functions to migrate the Service master key.

The regenerate phrase can regenerate the Service master key. SQL Server
All the keys encrypted with the CMK will be decrypted and then encrypted with the new CMK. This is an operation that consumes a large amount of resources. If the key security is not compromised, the operation should be performed in a time period with low resource requirements. If any decryption operation fails, the entire statement fails.

The Force
The key generation process can continue. Only when the re-generation process fails and you cannot use Statement to restore the Service master key, the force option is used.

Note:
The Service master key is SQL Server. The root of the encryption hierarchy. The Service master key directly or indirectly protects all other keys and confidential content in the tree. If a key cannot be decrypted during forced re-generation, the data protected by the key will be lost.

Machine Key
You can use the computer key to add or delete encryption.

Permission

You must have control server permissions on the server.

Example

The following example Re-generates the Service master key.

	Copy code
Alter Service master key regenerate; Go