Problem upgrading encryption for multidimensional analysis services database in SQL 2022

Question

Problem upgrading encryption for multidimensional analysis services database in SQL 2022

Jim Dale 15

I am migrating 2 analysis services databases that are in multidimensional mode from a 2017 instance to a 2022 instance that has had the CU1 update applied.

I need to upgrade the encryption on these DBs so i can amend the connection string.

This article tells me how i should do it but i see no option anywhere to enable EnsureProperEncryption when restoring the database.

https://learn.microsoft.com/en-us/analysis-services/instances/encryption-upgrade?view=sql-analysis-services-2022&preserve-view=true

This is relevant section:

Multidimensional mode

For multidimensional model databases at all compatibility levels, the following error can be returned during certain schema write operations:

"Multi-dimensional database '%{DatabaseName/}' is not using latest encryption schema. Please create a backup file and restore DB from backup file with the option EnsureProperEncryption to upgrade to the latest encryption."

To upgrade encryption, backup the database and then restore with the EnsureProperEncryption option enabled.

Hoping someone can offer some advice on how to achieve this.

Thanks

Céline Rodríguez 16 Reputation points

2025-05-02T16:09:13.2066667+00:00

Please, have a look at this link: Upgrade Encription

1 answer

Your answer

Céline Rodríguez 16 Reputation points

2025-05-02T16:09:13.2066667+00:00

Please, have a look at this link: Upgrade Encription

Answer 1

Amira Bedhiafi 31,406

It appears that you're facing an issue while upgrading the encryption for multidimensional analysis services databases in SQL Server 2022. Based on the information you provided I am assuming the following :

First, ensure that you have a proper backup of the database in question. Since the GUI might not be showing the EnsureProperEncryption option, consider using T-SQL or PowerShell to perform the restore with the required options.

Using SQL Server Management Studio (SSMS), you might write a T-SQL script like this to restore the database with the EnsureProperEncryption option:

   RESTORE DATABASE YourDatabaseName

   FROM DISK = 'path_to_your_backup.bak'

   WITH EnsureProperEncryption;

Make sure that you have the required permissions to perform the restore operation with the EnsureProperEncryption option. You might need to be a system admin or have a special role to enable this option.

VA-4110 0 Reputation points

2023-08-15T16:47:39.5066667+00:00
"

RESTORE DATABASE YourDatabaseName FROM DISK = 'path_to_your_backup.bak' WITH EnsureProperEncryption;

It didn't work .

Msg 155, Level 15, State 1, Line 8
'EnsureProperEncryption' is not a recognized RESTORE option.

Microsoft SQL Server 2022 (RTM-CU7) (KB5028743) - 16.0.4065.3 (X64) Jul 25 2023 18:03:43 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor)

Jim Dale 15

Yes i couldn't get it to work either. I think scripting it is the right idea but this isn't the correct syntax for analysis services. You need to write the query in one of languages supported in AS like XMLA. I have tried various versions of something like the script below but still can't get it to work

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Backup\ProductAnalysisDB.abf</File>
<DatabaseName>ProductAnalysisDB</DatabaseName>
<AllowOverwrite>true</AllowOverwrite>
<Password>Backup</Password>
<EnsureProperEncryption>true<EnsureProperEncryption>
<DbStorageLocation>xmlns="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">C:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Data\</DbStorageLocation>
</Restore>

Share via

Problem upgrading encryption for multidimensional analysis services database in SQL 2022

1 answer

Your answer