question

JokoHimawan-8684 avatar image
7 Votes"
JokoHimawan-8684 asked EllaCalais-0863 commented

AADSTS50011: Error occured when some users want to access Azure Analysis Service via Excel

Hello,

I got the report from user that want to access Azure Analysis Service via Excel that an error has occured. From what I've read it has something to do with Azure Active Directory, but I can't find the solution yet (many of the documentations suggested to put the url on 'App Registration', however I can't find the application).

This is the detail of the error when some users wants to access AAS via Excel:
111617-1.png




(Edit: I got additional information from the user, that there is an Office365 issue for some users (can't login). Is it relevant AADSTS50011's error?)

Any information and assistance, will be much appreciated. Thanks!

azure-active-directoryazure-analysis-services
1.png (185.2 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

the same issue here, but when logging in throught ssms on our servers :/ - libraries updated. Cant figure out that is the problem

1 Vote 1 ·

Hi @MarekSuwart-0892 . Probably updating your SSMS' version to the latest would help? Hope this will help you

0 Votes 0 ·

Exactly this is what it finally realized yesterday, there was an SSMS form 2018 installed, installing the newest one, solve the issue ;)

0 Votes 0 ·

Hi, if the posted answer resolves your question, please mark it as the answer by clicking the check mark. Doing so helps others find answers to their questions.

0 Votes 0 ·
amanpreetsingh-msft avatar image
0 Votes"
amanpreetsingh-msft answered JokoHimawan-8684 commented

Hi @JokoHimawan-8684 · Thank you for reaching out.

By tracking the Request/Correlation ID, I found:

  • Reply url in authentication request is: urn:ietf:wg:oauth:2.0:oob

  • Reply url registered in the application with ID cf710c6e-xxxx-xxxx-xxxx-xxxxxxxx4c66 is https://login.microsoftonline.com/common/oauth2/nativeclient

To resolve the issue, kindly navigate to Azure Portal > Azure Active Directory > App Registration > Search for App ID cf710c6e-xxxx-xxxx-xxxx-xxxxxxxx4c66 > click on Authentication blade and set redirect uri to urn:ietf:wg:oauth:2.0:oob


Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your quick response @amanpreetsingh-msft

I followed your instruction, however I can't find the applicationid for cf710c6e-xxxx-xxxx-xxxx-xxxxxxxx4c66


'![111668-image.png][1]




Any idea why this is happened? Or how can I find the application?


Thanks

0 Votes 0 ·
image.png (22.1 KiB)

Hi @JokoHimawan-8684 · I checked for for app ID cf710c6e-xxxx-xxxx-xxxx-xxxxxxxx4c66 and found that it is for Azure Analysis services client which is a first party (Microsoft) app and the reply url urn:ietf:wg:oauth:2.0:oob is replaced with new url. You can try updating to the latest Azure Analysis services client libraries provided here: https://docs.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions#download-the-latest

2 Votes 2 ·

Hi @amanpreetsingh-msft

I really appreciate your response regarding this matter. Just a quick question, in order to update to the latest Azure Analysis services client libraries all I need to do is to download and install the three files below on my local machine, am I correct?

111687-image.png




Is there any step that I have to do in Azure Portal?

Thank you

1 Vote 1 ·
image.png (22.6 KiB)
Show more comments
OleThomsen-1690 avatar image
2 Votes"
OleThomsen-1690 answered TonyValentine-9182 commented

We have exactly same problem with Azure Analysis Services Client, starting saturday.

Upgrading the libraries did not solve it.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @OleThomsen-1690 . After you installed/upgrade three of the client libraries, if you access the Analysis Service via Excel, you can replace the AS OLEDB in Microsoft Office directory with copying the AS OLEDB from MSOLAP.

Copy from: C:\Program Files\Microsoft Analysis Services\AS OLEDB\140 (after you installed the MSOLAP)

To: C:\Program Files\Microsoft Office\root\vfs\ProgramFilesX64\Microsoft Analysis Services\AS OLEDB\140\ (replace the existing folder and it's contain with the one from MSOLAP)

Don't forget to make a backup of AS OLEDB folder from Office's directory. (Just in case something happens)

I hope it will help.

1 Vote 1 ·

Thanks a lot, that works!

0 Votes 0 ·

Hi @amanpreetsingh-msft ,
the solution provide by @JokoHimawan-8684 work well (copying manually in Office folder) now my question is what are we supposed to do in a company where users don't have rights to to this kind of things and mostly when Office updates follow some company rules.
What are we supposed to "update" to get the things back in order.

Moreover, having this error now from multiple different clients looks like it's more a change in Azure Analysis Service side which introduce this connectivity issue with certain Office versions.

Regards
Cedric

0 Votes 0 ·
TonyValentine-9182 avatar image TonyValentine-9182 CdricFodouopKamologne-6881 ·

Same problem here. This is an outage for our customers. Please roll back the weekend change MSFT!

1 Vote 1 ·
OleThomsen-1690 avatar image OleThomsen-1690 CdricFodouopKamologne-6881 ·

@CdricFodouopKamologne-6881 I totally agree. Whatever Microsoft did in their backend during the weekend, they should roll back.

We have raised a ticket with Microsoft.

0 Votes 0 ·
coenenp-6641 avatar image
1 Vote"
coenenp-6641 answered

What worked for me was update office.

We have a VDI environment where I could not update the office right away.

I copied the “C:\Program Files\Microsoft Office\root\vfs\ProgramFilesX64\Microsoft Analysis Services\AS OLEDB\140\” from my laptop to our VDI servers. Users on the VDI servers are now also able to connect to AAS again.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KoppenolSteven-9055 avatar image
2 Votes"
KoppenolSteven-9055 answered KoppenolSteven-9055 published

Updating office (2019) did not work for me. After updating office and the client libraries, I looked into the folder mentioned by coenenp-6641 and found that that was still out of date.

Manually copying the entire folder from C:\Program Files\Microsoft Analysis Services\AS OLEDB\140 to C:\Program Files\Microsoft Office\root\vfs\ProgramFilesX64\Microsoft Analysis Services\AS OLEDB\140 solved the issue

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ValerSimina-0821 avatar image
2 Votes"
ValerSimina-0821 answered ValerSimina-0821 commented

Hello,

We have the same issue (same app ID) while trying to deploy models to AZURE Analysis Services Server using Visual Studio. We have updated all OLEDB drivers, without any luck.

This seems to be a widespread issue.

Are there any updates?

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am still having the same issue as you. All libraries/drivers are updated. I have manually copied the folder (as suggested above). My VS version and my SSMS versions are both up to date. I spent most of yesterday just thinking I was losing my mind, glad to know I am not alone. I'll report back if I find a solution!

1 Vote 1 ·

IIf it helps, my Excel and SSMS were working fine prior to updating the drivers and still do. My only beef is with Visual Studio model deployment.

1 Vote 1 ·

it seems like you also need to update the whole studio , i had similar issue with SSMS version 17 , updatig libraries didn solve the issue, only updating SSMS to latest versin (18) did the job

0 Votes 0 ·

Thanks for the suggestion, I have tried updating Visual Studio 2017 to the latest version, but again without any luck. Will try it on VS 2019 and report back.

0 Votes 0 ·

@ValerSimina-0821 I have a solution that worked for me - do you ever use Tabular Editor? My coworker does and suggested I try it - I opened my .bim file in tabular editor and deployed without issue.

0 Votes 0 ·

Hi,

I have tried tabular editor but it didn't work for me (something related to the security users.)

What did work for me is installing the latest SSDT (https://go.microsoft.com/fwlink/?linkid=2161172) see and alone installer. Now I can apply continue my work using Visual Studio 2017.

0 Votes 0 ·
AnjaliJaideep-8517 avatar image
2 Votes"
AnjaliJaideep-8517 answered EllaCalais-0863 commented

Facing the same issue since couple of days. Raised a ticket with Microsoft and this was the reply I got:

We are retiring the old client libs because of TLS 1.2 and insecure redirects. At the end of June 2021, AAD will stop accepting insecure connections.

So here the action plan :
1. Update SSMS to 18.9.1.
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
1. Update AS Client Libraries to latest and restart local machine.
https://docs.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions

Our client libraries were already up-to-date. We have updated SSMS to 18.9.1 now but still the error persists.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


@AnjaliJaideep-8517 Did you solve the problem? Is Updated Office a final solution?

0 Votes 0 ·

nope, it did not work

0 Votes 0 ·

@AnjaliJaideep-8517 Hi! Did you get this issue resolved? Several months have passed and I'm curious if you've come up with a solution :)

0 Votes 0 ·
RaviKosuri-7504 avatar image
1 Vote"
RaviKosuri-7504 answered

Hello, lot of users in my organization started seeing the same error recently. We tried installing MSOLA(x86) on one of the computers but it didn't really help. Please Suggest.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OleThomsen-1690 avatar image
1 Vote"
OleThomsen-1690 answered CdricFodouopKamologne-6881 commented

We got this reply from Microsoft Support:

The problem you reported in this case was caused by a recent changed in TLS documented in announcement at Earlier versions of Analysis Services client libraries will retire on 30 June 2021 | Azure updates | Microsoft Azure.

If you have Excel users, please Excel—apply Office upgrade. Excel comes with its own build of MSOLAP provider. The problem should be fixed after applying the Office update.


You will also need to make sure all AAS Connection Strings are using MSOLAP.8 and not MSOLAP.7. Here is an example:
In Excel go to Data Tab -> Connections and Queries -> Right-click each connection and select Properties -> Definition Tab and update the below.


This:
Provider=MSOLAP.7;


Needs to be updated to:
Provider=MSOLAP.8;


If your users uses Power BI and SSMS, please update the Power BI, and SSMS applications to the latest versions at Download SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) | Microsoft Docs and Download Microsoft Power BI Desktop from Official Microsoft Download Center

Then follow the instructions on the page to install the latest MSOLAP and AMO libraries.

Action required

To minimize risk and potential security vulnerabilities, please update your Analysis Services client libraries to version 15.1.37.40 (MSOLAP) and 18.7.0.1 (AMO and ADOMO) before 30 June 2021. For more details about the Analysis Services client libraries, please reference the article Analysis Services client libraries in the product documentation.

Visual Studio:
Run Visual Studio Installer and apply any updates to the version you are using. You also need to update Analysis Services Projects in the Extension management as well.
In Visual Studio go to:
VS 2019:
Extension -> Manage Extensions -> Updates -> Apply any updates to AS Projects.


VS2017:
Tools -> Extensions and Updates -> Updates -> Apply any updates to AS Projects.


You can also download the latest extension here at as manually:
Microsoft Analysis Services Projects - Visual Studio Marketplace


Edit: I can add that our Office 365 apps are in semi-annual mode, and Excel MSOLAP is not being upgraded

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @OleThomsen-1690,
thanks for sharing answer give by the support.

IIn our case, an update of Office was enough to solve the issue.

Regards

0 Votes 0 ·
RafaelLedesma-5544 avatar image
0 Votes"
RafaelLedesma-5544 answered

MSOLAP Libraries upgrade worked for me for users using Office 2016.

Users using Office 365 Business seems not affected by this issue.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnjaliJaideep-8517 avatar image
1 Vote"
AnjaliJaideep-8517 answered

We have been able to resolve this for our users now.

Following steps were done:
1. Client libraries supporting Power BI,excel, SSMS and visual studio were updated. (This was done before 30th June 2021 itself).


  1. SSMS and Visual Studio 2017 updated to latest version. SSMS - 18.9.1 and Visual Studio 2017 - 15.9.36


  2. Existing version/extension of SQL server data tools (SSDT) was uninstalled and upgraded to 15.1.62002.01090.


Post this, we were able to deploy our tabular model from Visual Studio successfully.

However, when I tried connecting to the model from Excel, it gave the following error:
Unable to connect to datasource. Reason: unable to locate database server.

Our Office was already running on the latest version.
We tried uninstalling the entire office pack and reinstalling it again.
This clicked and now, the whole flow of deployment of tabular model from VS and accessing of model from Excel is working perfectly for us.

Hope this helps!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.