In my organization, we are migrating from Active Directory Federation Services to Entra ID. As one of the migration steps, we are using Staged rollout to test if all services are working properly on a group of users. Before I create a ticket to Microsoft, I believe I will find help here.
In consultation with another team, I found out that users are reporting a problem connecting to a SQL database on Azure Analysis Services in Excel File, which using it via PivotTable Analyze. They are receiving error like below.
An operation that uses the database driver could not be completed. If the driver is Microsoft driver, make sure the driver file isn’t damaged, and if it is, reinstall the driver by reinstalling Microsoft Query. For other drivers, contact your database administrator or driver vendor.
Searching the Internet, I checked step by step.
Checking if it is installed propper version of MSOLAP – it should be installed version 8 (source)
In path C:\Program Files\Microsoft Analysis Services\AS OLEDB\ is only folder 140 (which means it's MSOLAP version 8)
Tested on two versions of Office: 2302 (Build 16130.20810) and 2202 (Build 14931.20764) – the same result
I have checked Sing-in log
Anyway ADFS does not show any associated logs.
Provider=MSOLAP.8;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<catalog>;Data Source=asazure://<region>/<servername>;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2
Can I check something else? I test connection like end-user, because I don't have access to SQL database (it is managed by diffrent team), my guess is based on logs only.
Many thanks.
Ask them to drop/remove Integrated Security=SSPI; from the connection string.
