XMLA Endpoint and Excel

[BenMackin]

Hello all.

Trying to work through a strange XMLA Endpoint issue. Everything works fine with Power BI Desktop, and even Excel Power Query when interactive with the XMLA Endpoint. However, when I try and use Excel Power Pivot, it gives me errors.

Steps to reproduce:

1) Open Excel

2) Open Power Pivot

3) In the toolbar, choose "From Database"

4) From within that drop down, choose "From Analysis Services or PowerPivot"

5) In the new pop-up, in the Server field, enter my XMLA Endpoint.

If I then try and select a Database, or use the Test Connection button, I get the following error:

Failed to connect to the server. Reason: Errors in the OLE DB provider. Unable to obtain authentication token using the credentials provided. If your Active Directory tenant administrator has configured Multi-Factor Authentication or if your account is a Microsoft Account, please specify empty values for 'User Name' and 'Password' fields, and then retry. You should then be prompted to enter your credentials.

Unfortunatly, there is no other options on this screen, so seemingly no way to continue. However, if I then do the following:

1) Click on Advanced

2) In the new popup under Security, remove SSPI from the Integrated Security subsection.

3) Click on Test Connection.

Now I get a popup window, and am asked to authenticate with my Microsoft account. It is very important that you click the Test Connection on this screen, after clearing the SSPI. If you don't, then it won't work.

Now, back at the main screen, I can open the drop down to select a database. The drop down properly shows all of my Power BI datasets for this XMLA Endpoint. If I select one, and click on Next, I am taken to a screen to input a query. Here, if I click on the "Design" button, I get an error

The connection string is not valid

However, if I hand write a query (DAX or MDX), I can click on Finish, and it will load the data into my Power Pivot.

The problem then becomes if I save and close this file, and then reopen it, again I can not refresh. I get the first error again (about Multi-factor authentication).

So it seems like there are a few issues going on with Excel Power Pivot and XMLA endpoints:

1) The Power Pivot connection isn't saving the fact that I remove the SSIP from the Integrated Security. It keeps reappending this, which breaks Power Pivot connectivity to XMLA Endpoint.

2) The query interface doesn't work against an XMLA end point.

If I take the same as a cube in Azure Analysis Services, the query interface works just fine. So this seems to be some interaction with the XMLA end point.

[BenMackin]

It doesn't seem like you have used Excel Power Pivot. Otherwise, you would know that your first option is not possible to perform when using the "From Analysis Services or PowerPivot". If I instead try and do an "Other ODBC" conenction, and have it connect using the Microsoft OLE DB Provider for Analysis Services 14.0, and do as you say (set Username and Password to blank) I get the following error:

Non-Windows user credentials were supplied for a non-http connection to Analysis Services. The Microsoft SQL Server Analysis Services data extension requires Windows Integreated Security unless connecting via http or https.

As for your second and thrid options, those are just restatements of what I had said I was already attempting. These are really not viable options to provide to over 100 users in our company, as most of them can not hand write a query.

Your fourth option also does not work. I tried saving a connection without the SSIP option present. When adding that back to my Power Pivot, and attempting to either write a query or refresh results, I get the same error as above:

Non-Windows user credentials were supplied for a non-http connection to Analysis Services. The Microsoft SQL Server Analysis Services data extension requires Windows Integreated Security unless connecting via http or https.

The issue appears to be with how Excel Power Pivot interacts with XMLA Endpoint using the client libraries. Not sure if it is a bug, or an incompatibility between the two. I would suggest trying to take a simple Power BI dataset, and then try and import the data via Excel Power Pivot. You shoudl quickly see the same errors I am facing.

[Anonymous]

Hi @BenMackin ,

It seems like you are experiencing some issues with connecting to an XMLA endpoint using Excel Power Pivot. Here are some potential solutions to the issues you are facing:

1. To resolve the issue with the authentication token, try specifying empty values for the 'User Name' and 'Password' fields, and then retry. You should then be prompted to enter your credentials.

2. To remove the SSPI from the Integrated Security subsection, click on Advanced in the 'From Analysis Services or PowerPivot' pop-up, and then remove SSPI from the Integrated Security subsection under Security. Make sure to click on Test Connection after removing SSPI to authenticate with your Microsoft account.

3. To resolve the issue with the query interface not working against an XMLA endpoint, try hand writing a query (DAX or MDX) instead of using the 'Design' button. This should allow you to load the data into your Power Pivot.

4. To resolve the issue with the Power Pivot connection not saving the fact that you removed the SSIP from the Integrated Security, try saving the connection string as a separate file and then importing it into your Power Pivot. This should ensure that the connection string is saved correctly.

I

How to Get Your Question Answered Quickly 

If it does not help, please provide more details.

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.