Cannot Browse XMLA dataset with SSMS

[Anonymous]

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).

[saini_chandni · Accepted solution]

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]

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

[Anonymous]

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

[v-shex-msft]

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]

Thanks for the reply Xiaoxin @v-shex-msft .

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

[v-shex-msft]

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]

@v-shex-msft- I'm puzzled by your response - I don't know if I havn't made my question clear or whether your answer has gone over my head.

My question has neither to do with Analyze in Excel nor with a web browser.  It has to do with the "Browse" feature in SQL Server Management Studio when you right-click a database.

[GilbertQ]

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.