Forum Discussion

cassidy's avatar
cassidy
Power Participant
2 years ago
Solved

Excel Version 2311

For those of you who use BI via connected PivotTables, the latest Excel version of 2311 is preventing both refreshes of existing connections as well as creating new connections.   Please vote for t...
General Comment
Need Help
Show and Tell
  • DHayes's avatar
    DHayes
    2 years ago

    GilbertQ cassidy I was able to fix this issue by creating a new Excel document from PowerBI online (I assume the same source) and then saving that as a copy to my PC.  This Excel document works with no problem but I noticed that the connection string is different than my original Excel documents so I copied the new connection string and overwrote the existing connection strings. This fixed my issue.

     

    Change Connection String

     

    OLD connection string (renamed GUIDs for privacy)

    Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-<GUID>;Data Source=pbiazure://;Location="https://wabi-us-north-central-redirect.analysis.windows.net//xmla?vs=sobe_wowvirtualserver&db=<GUID>&app=PBIAddin";Extended Properties="DataSource=pbiazure://";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, <GUID>;Update Isolation Level=2

     

    New connection string

    Provider=MSOLAP.8;Integrated Security=ClaimsToken;Persist Security Info=True;Initial Catalog=sobe_wowvirtualserver-<GUID>;Data Source=pbiazure://api.powerbi.com;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, <GUID>;Update Isolation Level=2

     

    The error we got for searchability:

    "Microsoft Excel
    COM error: COM error. Microsoft.AnalysisServices.AzureClient, Authentication failed; we cannot locate Azure Active Directory in your environment. This could be because your client application does not currently have the required components. Install the latest version of your client application or contact technical"

  • cassidy's avatar
    cassidy
    2 years ago

    Ignore the other "solution" that was selected by a MSFT employee incorrectly.

    The actual solution was that our company had TLS 1.3 enabled on Windows 10, which is not compatible.  Version 2311 of Excel apparently started using 1.3 when trying to reach out to Power BI Service, causing an error.  

    This also was preventing Power BI Desktop from Signing In, and also Excel from connecting to Azure SQL DB's

cassidy's avatar
cassidy
Power Participant
2 years ago

Ignore the other "solution" that was selected by a MSFT employee incorrectly.

The actual solution was that our company had TLS 1.3 enabled on Windows 10, which is not compatible.  Version 2311 of Excel apparently started using 1.3 when trying to reach out to Power BI Service, causing an error.  

This also was preventing Power BI Desktop from Signing In, and also Excel from connecting to Azure SQL DB's