Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cannot Browse XMLA dataset with SSMS

I'm having trouble working with a Power BI dataset as an XMLA endpoint from SQL Server Management Studio (SSMS V18.4).

 

I connect to a Power BI model in Power BI Premium Capacity using a very simple connection string with no extra parameters of the form: powerbi://api.powerbi.com/v1.0/myorg/my-workspace-name

 

I can see the databases and tables and can open a DAX query and 'EVALUATE' a table and see its contents. I can also connect with Analyze in Excel. All looks fine until I right-click a database and choose "Browse...". I then get this error:

 

For AS Azure connections, if 'Integrated Security' connection string property is set to 'SSPI' then 'UseADALCache' connection string property cannot be specified.

 

It doesn't appear to matter what authentication method I use.

 

This issue was also reported in another thread some months ago and did not receive a resolution (https://community.powerbi.com/t5/Service/XMLA-endpoint-Integrated-Security-Error/m-p/969789).

 

Any help greatly appreciated - it fails for every dataset I've tried in our tenant (others have tried as well with the same result).

 

Need Help
  • saini_chandni's avatar
    saini_chandni
    5 years ago

    Hi,

     

    We faced same issue , but upgrading ssms v18.4 to ssms v 18.5.1 resolved our problem and now we are able to browse the cube/dataset.

     

    Thanks

     

8 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    6 years ago
    Hi there

    When logging in with Excel, and it prompts for the user name and password leave this blank and it will prompt you to log in via AAD

    Also make sure you have got the latest SQL Server Client tools installed.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous,

    According to your error messages, it seems like its connection string uses the property in 'SSPI' mode that not found or not able to be configured.

    Can you confirm your connection with the available credentials? (notice: azure as requires azure ad instead or normal windows authentication)

    Authentication and user permissions 

    BTW, these connection credentials are integrated into odc file and link to your power bi account, you can't do custom on it.

    Analyze in Excel: Change log in credentials 

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for the reply Xiaoxin Anonymous .

       

      To be clear, all operations with Analyze in Excel work properly, initial connection with SSMS appears to work properly as well (I can expand databases and Tables and can even dump table contents with an EVALUATE statement in a DAX query).  My trouble comes when I try to right-click a database and choose Browse...

       

      I have tried all three authentification methods:

      • Azure Active Directory - Universal with MFA
      • Windows Authentication
      • Azure Active Directory - Password

      All three allow me in and all fail in the same manner.

       

      Perhaps I don't understand enough about the authentication methods to be able to answer your question.

       

      Anything else you can offer would be appreciated.

       

      thanks,

       

      Dale

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI Anonymous,

        If the issue only occurs when you view on the browser, I'd like to suggest you contact to power bi team to confirm if any compatibility issue with analyze in excel and corresponding drivers.
        Regards,

        Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I have the same issue here. Any update on this?

  • saini_chandni's avatar
    saini_chandni
    Regular Visitor
    5 years ago

    Hi,

     

    We faced same issue , but upgrading ssms v18.4 to ssms v 18.5.1 resolved our problem and now we are able to browse the cube/dataset.

     

    Thanks

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks saini_chandni 

       

      I hadn't checked back here for a while and found this answer today.  Upgraded to 18.6 and it works as expected!

       

      Dale