Managing Instance Encryption Keys (XMLA)

You can use the SetEncryptionKey command in XML for Analysis (XMLA) to set or reset the instance encryption key for an instance of Microsoft SQL Server Analysis Services.

Security Note
Only server administrators can run the SetEncryptionKey command.

The instance encryption key can only be decrypted by the same account used to encrypt the key. Therefore, the instance encryption key must be first decrypted and retrieved before the service account used by the Analysis Services instance is changed, and then encrypted once after the service account is changed. Otherwise, the instance can no longer decrypt the database encryption keys, which in turn encrypt and decrypt secrets (such as the passwords for data sources) stored in the database.

To properly change the service account for an Analysis Services instance, you must perform the following steps:

1. Call the XMLA Discover method to decrypt the existing instance encryption key and retrieve the DISCOVER_MASTER_KEY schema rowset.

   Security Note
   Only server administrators can retrieve the DISCOVER_MASTER_KEY schema rowset.

2. Change the service account for the Analysis Services instance.

3. Use the SetEncryptionKey command to encrypt the retrieved instance encryption key using the new service account.

If you change the service account without first retrieving the instance encryption key, the Analysis Services instance can no longer read encrypted information from databases on that instance, and an error occurs. To correct this issue, you can change the service account back to the previously specified user account, and then perform the previous process to properly change the service account.

Specifying the Encryption Key

The Key property of the SetEncryptionKey command contains a string representation of the encryption key. The Key property should be set to the value of the KEY column in the DISCOVER_MASTER_KEY schema rowset that was retrieved before the service account for the Analysis Services instance was changed.

Resetting the Encryption Key

You can also reset the encryption key using the SetEncryptionKey command. To reset the encryption key, set the Reset attribute of the SetEncryptionKey command to true. Analysis Services resets the instance encryption key by performing the following actions:

1. Decrypts the instance encryption key, database encryption keys, and secrets contained in databases on that instance.

2. Changes the value of the instance encryption key.

3. Encrypts everything with the new instance encryption key.

The current service account for the Analysis Services instance is used to decrypt the old instance encryption key and encrypt the new instance encryption key. When resetting the instance encryption key, do not specify a value for the Key property of the command.

Examples

Description

The following example sets the instance encryption key to the value specified in Key.

Code

<SetEncryptionKey xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
  <Key>
    BSyB3nTLvkCR3GwLwMNAyQEAAAAEAAAA/////wECAAAJZgAAAKQAAEAcOEA0JbXfBxXfL+l/0BMA
    ylnQiDhI9Fgm/QoOAR3NIikzEQPPBNOGSILZfVQqPUiBXuSBnrR/VUI6pLa9AgAFLIHedMu+QJHc
    bAvAw0DJ
  </Key>
</SetEncryptionKey>

Description

The following example resets the instance encryption key.

Code

<SetEncryptionKey Reset="true" xmlns="https://schemas.microsoft.com/analysisservices/2003/engine" />

See Also

Concepts

Using XML for Analysis in Analysis Services (XMLA)