Restoring SSAS Cubes to a SQL 2022 Server with CU5

I have a client who was upgrading some servers from pre-2022 versions of SQL Server to SQL Server 2022 CU7. They had some multidimensional SSAS cubes that were to go on the new server. But they ran into an issue after the upgrade. After restoring a backup of an SSAS database to the new server they found that they could no longer modify the data source using SSMS.

The error SSMS produced was: “Errors in the metadata manager. Multidimensional database ‘DBName’ is not using latest encryption schema. Please create a backup file and restore DB from backup file with the optioin EnsureProperEncryption to upgrade to the latest encryption.

SQL 2022 CU1 and later includes enhanced encryption for “certain write operations to the model database schema”. SQL 2022 CU5 includes a bug fix with the description “Adds additional enforcement of write operations to the encryption algorithm that’s used to encrypt data sources and connection strings in SQL Server Analysis Services (SSAS) models”.

The solution required two steps, as stated in the error message:

1. Back up the SSAS database.
2. Restore with the EnsureProperEncryption option enabled.

While you could do the backup using the SSMS GUI, the option to ensure proper encryption was not available there. Microsoft Support gave us the XML to perform the backup and restore, so I’m sharing it here in case someone else runs into this issue.

Backup

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="false">
  <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>your_databse_id</DatabaseID>
</Object>
<File>your_backup_file_pathname</File>
<AllowOverwrite>true</AllowOverwrite>
<ApplyCompression>false</ApplyCompression>
</Backup>
</Batch>

Restore

1
2
3
4
5
6
7

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="false" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl100="http://schemas.microsoft.com/analysisservices/2008/engine/100">
<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl922="http://schemas.microsoft.com/analysisservices/2022/engine/922">
<File>your_backup_file_pathname</File>
<AllowOverwrite>true</AllowOverwrite>
<ddl922:EnsureProperEncryption>true</ddl922:EnsureProperEncryption>
</Restore>
</Batch>

Remember that the backup file should have a .ABF extension and the file path should be a UNC path.

Up and Running

After the backup and restore using the code above, the SSAS database was back in action and the client was able to modify the data source settings again.