locked
Cannot connect to PowerPivot document from Report Builder or Excel

    Question

  • With PowerPivot documents residing in a PowerPivot library on a SharePoint 2010 site, we cannot connect to any PowerPivot document as a data source in SSRS 2008 R2 or Excel 2010 with the PowerPivot add in installed.

    The PowerPivot document in SharePoint 2010 works fine and I have access to it.

    When connecting from ReportBuilder, the connection test succeeeds but when trying to create a query in a dataset, it throws the error:The remote server returned an error: (401) Unauthorized.

    When connecting from Excel, Excel launches the "Multidimensional Connection 10.0" dialog, which it usually does when it can't connect to teh source.

    Again, I can connect and use the PowerPivot documents directly but just cannot use them as a data source.

    Thanks for your help.


    Jeff T Jones
    Sunday, November 14, 2010 2:51 PM

Answers

  • Ok, I think I figured this out.  I made this change to get the Management Dashboard working (which did the trick - thanks Mariano!), but later when I tried to use an uploaded PowerPivot workbook as a data source, I got 401 Unauthorized errors out the ying-yang.  My debugging attempts went like this:

    - verbose ULS logs... useless for this issue
    - OLE DB for Analysis Services 10.0 driver re-install on my client machine and all servers in the farm... nada
    - Netmon 3.4... ok, I see the 401 response :-(
    - WIF/WCF Tracing... whoa, I could see my pool account impersonating me - cool, but no help
    - Failed Request Tracing in IIS 7.5 - voila!

    After nearly a full day of troubleshooting, I finally stumbled on the Failed Request Tracing functionality in IIS 7.5 (pretty cool, btw) and saw that I was sending kerberos tickets to the PowerPivot Redirector service, and I was getting an Unauthorized 401 from somewhere deeper in the stack (can't remember, exactly).

    I looked at the other Sharepoint Web Services (the guid ones) and they're all using Anonymous Authentication.  Well, that's all well and good for internal services, but these PowerPivot services are external facing.  Then, I figured that if making the change referenced in this thread had fixed the dashboard, maybe the services farther down the stack also need to be using "Windows" authentication.  So, I went back to "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\isapi\PowerPivot\web.config" and changed all 4 basicHttpBindings at the top to clientCredentialType="Windows" and also changed the 2 customBindings to authenticationScheme="Negotiate".  And voila!  I was immediately able to use powerpivot workbooks as data sources, AND my Management Dashboard still worked like a charm!

    Now, I may have broken other stuff that's yet to be discovered... however, I finally have a fully functional Kerberos-enabled Sharepoint farm that has PowerPivot and all its bells and whistles.  Hallelujah.

    So, moral of the story... if you've taken the time to configure a Kerberos-enabled Sharepoint farm, and your web applications are set to "Negotiate (Kerberos)" instead of "NTLM", then you'll have to change the web.config for the externally-facing PowerPivot services to match.  It seems like most everything will "fall back" to NTLM if Kerberos doesn't get it done (including IIS), but WCF services can only specify one "clientCredentialType" per binding.

    BTW - If you use the Excel -> Data -> From Other Source -> From Data Connection Wizard -> Other/Advanced -> Microsoft OLE DB Provider for Analysis Services 10.0 to connect to a PowerPivot workbook on Sharepoint AND YOU'RE ON A 64-BIT CLIENT OS and you get the "Test connection succeeded but some settings were not accepted by the provider." message, don't sweat it.  Evidently, this is a 64-bit OS issue, not a PowerPivot issue (http://www.reportportal.com/forum/topic.asp?TOPIC_ID=1473).  I can connect to workbooks using the Excel -> PowerPivot -> From Database -> From Analysis Service or PowerPivot -> approach with the expected "Test connection succeeded." result.

    Also, I don't have any issues connecting to PowerPivot workbooks that are under a managed path (http://www.bluedoglimited.com/SharePointThoughts/Lists/Posts/Post.aspx?ID=317).  However, I do have a site collection at the root with a fully-functional Reporting Services folder.


    Joe Cole
    • Proposed as answer by tugba Thursday, December 22, 2011 12:37 PM
    • Marked as answer by Jeff Jones Thursday, December 22, 2011 1:44 PM
    Thursday, August 4, 2011 9:38 PM

All replies

  • Jeff,

    The error "(401) Unauthorized" usually indicates that the connection has been established but the permission check fails. I recommend that you refer to "Sharepoint permissions" in this article, http://msdn.microsoft.com/en-us/library/ee210621.aspx to check at your sharepoint site to see if you have correctly grant enough access permissions for your client user who runs your Excel file.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, November 17, 2010 10:33 AM
    Moderator
  • Thank you Charles for the response.  I did check our SharePoint configuration as you suggested and we are setup properly according to that document.  We are currently working with Microsoft Support on this issue.

     

    Thanks for your help.

    Jeff


    Jeff T Jones
    Tuesday, November 30, 2010 2:07 PM
  • Thanks Jeff for your update. Hope you can post the solution here after you get this issue resolved with MS support. Have a nice day!
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, December 1, 2010 9:11 AM
    Moderator
  • I am having this exact same error and have also gone to Microsoft support.  They bounced me around every related department but were unable to resolve the issue.

    Were you ever able to find a resolution?

    Tuesday, June 7, 2011 9:51 PM
  • H Derek,

    Unfortunately we haven't resolved this yet.  Our team has been pulled off on other projects so really haven't had a chance to dedicate much time to it.  We will probably engage Microsoft support in a month or so.  We do need to get this fixed relatively soon.

    Sorry I couldn't be more helpful but I will post the solution once we get the issue resolved.

    Take care


    Jeff T Jones
    Thursday, June 9, 2011 3:07 AM
  • Is your site at http://servername/sites/sitename or at any other subsite level. We are having the same problem and I found this solution:

    http://denglishbi.wordpress.com/2010/07/18/reporting-services-%E2%80%93-remote-server-returned-an-error-401-unauthorized/

    We have not tried it yet but as I cant find anything helpful we may be forced to. Anyone found any other solution yet?

     

    Thursday, June 23, 2011 5:39 AM
  • I am also having this exact issue.  Doesn't matter if I'm trying from a remote machine or directly from the server.  I get a 401 either way.  Just can't figure out why!?! Here's the ULS log:

     08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation General 6t8b Verbose Looking up context  site http://portal:80/_vti_bin/PowerPivot/redirector.svc/?DataSource=%2Fdocuments%2Ftest%2520-%2520data%2520source.xlsx in the farm SharePoint_Config

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation General 6t8d Verbose Looking up the additional information about the typical site http://portal:80/_vti_bin/PowerPivot/redirector.svc/?DataSource=%2Fdocuments%2Ftest%2520-%2520data%2520source.xlsx.

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation General 6t8f Verbose Site lookup is replacing http://portal:80/_vti_bin/PowerPivot/redirector.svc/?DataSource=%2Fdocuments%2Ftest%2520-%2520data%2520source.xlsx with the alternate access url http://portal.

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation General 6t8g Verbose Looking up typical site http://portal:80/_vti_bin/PowerPivot/redirector.svc/?DataSource=%2Fdocuments%2Ftest%2520-%2520data%2520source.xlsx in web application SPWebApplication Name=SharePoint - 80.

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation General 6t8h Verbose Found typical site / (25f71acb-7f8b-4a3f-968e-f6cf41a0bd13) in web application SPWebApplication Name=SharePoint - 80.

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nasq Medium Entering monitored scope (Request (POST:http://portal:80/_vti_bin/PowerPivot/redirector.svc/?DataSource=%2Fdocuments%2Ftest%2520-%2520data%2520source.xlsx))

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Logging Correlation Data 77a3 Verbose Starting correlation. de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Logging Correlation Data xmnv Medium Name=Request (POST:http://portal:80/_vti_bin/PowerPivot/redirector.svc/?DataSource=%2Fdocuments%2Ftest%2520-%2520data%2520source.xlsx) de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nasq Verbose Entering monitored scope (BeginRequestHandler) de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation SQM 97qu Verbose SQM: Datapoint id 6693 marked as Increment datapoint. de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring b4ly Verbose Leaving Monitored Scope (BeginRequestHandler). Execution Time=0.0511238160157227 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____SQL Query Count=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Execution Time=0.0511238160157227 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nasq Verbose Entering monitored scope (PostAuthenticateRequestHandler) de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring b4ly Verbose Leaving Monitored Scope (PostAuthenticateRequestHandler). Execution Time=0.0737523903177638 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____SQL Query Count=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Execution Time=0.0737523903177638 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Logging Correlation Data xmnv Medium User=LIQUID\svcspsetupadmin de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nasq Verbose Entering monitored scope (PostResolveRequestCacheHandler) de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring b4ly Verbose Leaving Monitored Scope (PostResolveRequestCacheHandler). Execution Time=0.0170412720052409 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____SQL Query Count=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Execution Time=0.0170412720052409 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nasq Verbose Entering monitored scope (PostRequestExecuteHandler) de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring b4ly Verbose Leaving Monitored Scope (PostRequestExecuteHandler). Execution Time=0.00530793718196028 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____SQL Query Count=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Execution Time=0.00530793718196028 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nasq Verbose Entering monitored scope (EndRequestHandler SharePointEndRequest) de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Server Logging Correlation Data 9gc5 Verbose Thread change; resetting trace level override to 0; resetting correlation to de05080c-cae9-4c50-9af3-5a7d882fccf7 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Server Unified Logging Service cn4g Verbose Trace level override is turned off. de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring b4ly Verbose Leaving Monitored Scope (EndRequestHandler SharePointEndRequest). Execution Time=0.0477714346376425 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____SQL Query Count=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Execution Time=0.0477714346376425 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nasq Verbose Entering monitored scope (ForceDehydrateHttpContextWorkflows) de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring b4ly Verbose Leaving Monitored Scope (ForceDehydrateHttpContextWorkflows). Execution Time=0.0108952394787606 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Execution Time=0.0108952394787606 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nasq Verbose Entering monitored scope (EndRequestHandler SPRequest Disposal) de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring b4ly Verbose Leaving Monitored Scope (EndRequestHandler SPRequest Disposal). Execution Time=0.00530793718196028 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____SQL Query Count=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Execution Time=0.00530793718196028 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.61 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring b4ly Medium Leaving Monitored Scope (Request (POST:http://portal:80/_vti_bin/PowerPivot/redirector.svc/?DataSource=%2Fdocuments%2Ftest%2520-%2520data%2520source.xlsx)). Execution Time=1.81307959531169 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____SPRequest Objects=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____SQL Query Count=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Execution Time=1.81307959531169 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Current User= de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____WebPart Events Offsets=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Service Calls=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Page Checkout Level=Published de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Current SharePoint Operations=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Critical Events=0 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Monitoring nass Verbose ____Log Correlation Id=de05080c-cae9-4c50-9af3-5a7d882fccf7 de05080c-cae9-4c50-9af3-5a7d882fccf7

    08/03/2011 16:24:00.62 w3wp.exe (0x20D0) 0x2140 SharePoint Foundation Logging Correlation Data 77a3 Verbose Ending correlation. de05080c-cae9-4c50-9af3-5a7d882fccf7

    Any help would be GREATLY appreciated!
    BTW - you can see that this PowerPivot workbook is in the root sharepoint site, so the "issue" reference above with managed site paths should not be in play in this case.  I can also browse my reporting paths in IE without any errors.
    Wednesday, August 3, 2011 9:44 PM
  • Ok, I think I figured this out.  I made this change to get the Management Dashboard working (which did the trick - thanks Mariano!), but later when I tried to use an uploaded PowerPivot workbook as a data source, I got 401 Unauthorized errors out the ying-yang.  My debugging attempts went like this:

    - verbose ULS logs... useless for this issue
    - OLE DB for Analysis Services 10.0 driver re-install on my client machine and all servers in the farm... nada
    - Netmon 3.4... ok, I see the 401 response :-(
    - WIF/WCF Tracing... whoa, I could see my pool account impersonating me - cool, but no help
    - Failed Request Tracing in IIS 7.5 - voila!

    After nearly a full day of troubleshooting, I finally stumbled on the Failed Request Tracing functionality in IIS 7.5 (pretty cool, btw) and saw that I was sending kerberos tickets to the PowerPivot Redirector service, and I was getting an Unauthorized 401 from somewhere deeper in the stack (can't remember, exactly).

    I looked at the other Sharepoint Web Services (the guid ones) and they're all using Anonymous Authentication.  Well, that's all well and good for internal services, but these PowerPivot services are external facing.  Then, I figured that if making the change referenced in this thread had fixed the dashboard, maybe the services farther down the stack also need to be using "Windows" authentication.  So, I went back to "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\isapi\PowerPivot\web.config" and changed all 4 basicHttpBindings at the top to clientCredentialType="Windows" and also changed the 2 customBindings to authenticationScheme="Negotiate".  And voila!  I was immediately able to use powerpivot workbooks as data sources, AND my Management Dashboard still worked like a charm!

    Now, I may have broken other stuff that's yet to be discovered... however, I finally have a fully functional Kerberos-enabled Sharepoint farm that has PowerPivot and all its bells and whistles.  Hallelujah.

    So, moral of the story... if you've taken the time to configure a Kerberos-enabled Sharepoint farm, and your web applications are set to "Negotiate (Kerberos)" instead of "NTLM", then you'll have to change the web.config for the externally-facing PowerPivot services to match.  It seems like most everything will "fall back" to NTLM if Kerberos doesn't get it done (including IIS), but WCF services can only specify one "clientCredentialType" per binding.

    BTW - If you use the Excel -> Data -> From Other Source -> From Data Connection Wizard -> Other/Advanced -> Microsoft OLE DB Provider for Analysis Services 10.0 to connect to a PowerPivot workbook on Sharepoint AND YOU'RE ON A 64-BIT CLIENT OS and you get the "Test connection succeeded but some settings were not accepted by the provider." message, don't sweat it.  Evidently, this is a 64-bit OS issue, not a PowerPivot issue (http://www.reportportal.com/forum/topic.asp?TOPIC_ID=1473).  I can connect to workbooks using the Excel -> PowerPivot -> From Database -> From Analysis Service or PowerPivot -> approach with the expected "Test connection succeeded." result.

    Also, I don't have any issues connecting to PowerPivot workbooks that are under a managed path (http://www.bluedoglimited.com/SharePointThoughts/Lists/Posts/Post.aspx?ID=317).  However, I do have a site collection at the root with a fully-functional Reporting Services folder.


    Joe Cole
    • Proposed as answer by tugba Thursday, December 22, 2011 12:37 PM
    • Marked as answer by Jeff Jones Thursday, December 22, 2011 1:44 PM
    Thursday, August 4, 2011 9:38 PM
  • With PowerPivot documents residing in a PowerPivot library on a SharePoint 2010 site, we cannot connect to any PowerPivot document as a data source in SSRS 2008 R2 or Excel 2010 with the PowerPivot add in installed.

    The PowerPivot document in SharePoint 2010 works fine and I have access to it.

    When connecting from ReportBuilder, the connection test succeeeds but when trying to create a query in a dataset, it throws the error:The remote server returned an error: (401) Unauthorized.

    When connecting from Excel, Excel launches the "Multidimensional Connection 10.0" dialog, which it usually does when it can't connect to teh source.

    Again, I can connect and use the PowerPivot documents directly but just cannot use them as a data source.

    Thanks for your help.


    Jeff T Jones

    Hi Jeff,

    I am facing the same issue. Can you post the solution if you got resolved.

    Thanks

    Wednesday, July 25, 2012 2:16 PM
  • This is a known issue when attempting to use a PowerPivot Workbook on the Mid-Tier server as a data source, if the server is configured for Kerberos authentication.

    Take a look at the following posting for details.

    http://blogs.msdn.com/b/johndesch/archive/2012/04/23/using-powerpivot-workbooks-from-a-mid-tier-server-configured-for-kerberos-authentication.aspx

    Wednesday, July 25, 2012 5:36 PM
    Moderator
  • This is a known issue when attempting to use a PowerPivot Workbook on the Mid-Tier server as a data source, if the server is configured for Kerberos authentication.

    Take a look at the following posting for details.

    http://blogs.msdn.com/b/johndesch/archive/2012/04/23/using-powerpivot-workbooks-from-a-mid-tier-server-configured-for-kerberos-authentication.aspx

    I think it's not the issue of kerberos authentication(since Powerpivot, sharepoint, Analysis services all are in same box).. there is no double hoping here. Though I change my Sharepoint web application to NTLM-- same problem.

    Also I tried to connect to sql server.. Below is the complete error message


    The remote server returned an error: (500) Internal Server Error. (System)

    ------------------------------
    Program Location:

       at System.Net.HttpWebRequest.GetResponse()
       at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetHttpResponse(String url, String soapAction, String user, String password)
       at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenHttpConnection(ConnectionInfo connectionInfo)
       at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
       at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
       at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
       at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
       at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
       at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
       at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    Hope this gives idea where is the issue..

    Thanks

    Friday, July 27, 2012 1:03 PM
  • To respond to your question, we did get this resolved and it turned out to be setting the authentication setting in the config file from NTLM to Kerberos (negotiate).  It is pretty much what Joe Cole explained above.

    Jeff T Jones

    Friday, July 27, 2012 1:47 PM
  • The 401 error is related to authentication and Kerberos. The 500 error is a different issue, and is probably more closely related to configuration. Start by checking for multiple HTTP bindings at the IIS level.
    Friday, July 27, 2012 4:41 PM
    Moderator
  • Hi all,

    I found that I have 3 http bindings for the same port 80.. I think this is causing issue.

    I added baseAddressPrefixFilters in the web.config file . which is working fine now.

    <serviceHostingEnvironment aspNetCompatibilityEnabled="true">
    <baseAddressPrefixFilters>
    <add prefix="http://site1.domain.com> 
    </baseAddressPrefixFilters>
    </serviceHostingEnvironment>

    But the problem is I hardcoded the site name in config file.. Is this correct? or else can i make any other changes.

    Thanks

    Monday, July 30, 2012 4:15 PM