locked
Unable to connect to SharePoint list data from PowerPivot in Excel RRS feed

  • Question

  • Hi,

    I have the PowerPivot excel add-in installed and I can connect successfully to SQL data.  I have two SharePoint farms - Farm 1 and Farm 2.

    I am able to connect PowerPivot to SharePoint lists in multiple web applications in Farm 1 by using the ListData.svc.

    e.g. https://farm1/_vti_bin/ListData.svc/Tasks

    In Farm 2 I am unable to connect to SharePoint data.  I have tried multiple web applications on Farm 2 and can't connect.  I receive the following error.  I am able to browse to the address in Internet Explorer and see a feed.

    Has anyone else had this error?

    Thanks in advance,

    Mark

    
    Error Message:
    ============================
    
    Cannot connect to the specified feed.  Verify the connection and try again. Reason: The underlying connection was closed: An unexpected error occurred on a receive..
    
    ============================
    Call Stack:
    ============================
    
       at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.UpdateDataFeedParameters(ConnectionStringBuilder connBuilder, IDataSource dataSource, Boolean checkFeedValid)
       at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.GetCurrentConnectionString(Boolean checkFeedValid)
       at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.ClickTestConnection(Object progressControl)
    
    ============================

    
    • Edited by Millard1 Tuesday, November 6, 2012 3:53 PM
    Tuesday, November 6, 2012 3:52 PM

Answers

All replies

  • Hi Millard1,

    From your descripton, it seems you want to use SharePoint list as a data souce. If so, you should export the SharePoint as data feed in your SharePoint site, and then create a new PowerPivot table that contains the list. Here are some articles for your reference, please see:
    Use Data Feeds (PowerPivot for SharePoint): http://technet.microsoft.com/en-us/library/ee210625.aspx#sharepointlist
    Using a SharePoint list as a data source: http://powerpivotgeek.com/2010/10/28/using-a-sharepoint-list-as-a-data-source/

    Regards,


    Bin Long

    TechNet Community Support

    Wednesday, November 7, 2012 10:25 AM
  • Hi,

    Thanks for you reply.  At present I have not installed the PowerPivot SharePoint component and will not be able to in the immediate future.

    Despite this, it i possible to connect to SharePoint list data from PowerPivot in Excel using a data feed using the ListData.svc

    e.g. https://farm1/_vti_bin_/ListData.svc/

    This is a supported method as per this whitepaper by Microsoft - PowerPivot Connecting to SharePoint List Data

    As mentioned in my initial post I am able to do this in Farm 1 but not in Farm 2.  I am not sure what is different between the two farms or where to start to get this working.

    Has anyone else seen this issue?

    Thanks,

    Mark

    Wednesday, November 7, 2012 4:30 PM
  • I have made some progress in that I know this is an authentication issue.

    We have multiple web applications in Farm 2.  All are set to Claims authentication.  I have found the following: 

    Web App A - Claims Type: Integrated Windows Authentication.  (I am able to connect successfully to SharePoint List data from PowerPivot in Excel)

    Web App B - Claims Types: Integrated Windows Authentication and Trusted Identity Provider (ADFS).  (I am not able to connect to SharePoint List Data from PowerPivot in Excel)

    So it looks like the addition of more than one claims type prevents PowerPivot in Excel connecting to SharePoint list data.

    Has anyone overcome this issue?

    Thanks,

    Mark

    Thursday, November 8, 2012 12:21 PM
  • Mark,

    Still an issue?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, October 27, 2013 7:31 AM
  • Hi,

    same configuration as Millard1 postet above:

    Web App A - Claims Type: Integrated Windows Authentication.  (I am able to connect successfully to SharePoint List data from PowerPivot in Excel)

    Web App B - Claims Types: Integrated Windows Authentication and Trusted Identity Provider (ADFS).  (I am not able to connect to SharePoint List Data from PowerPivot in Excel)

    Are there any solutions yet?

    Thanks,
    Bernd
    Friday, January 30, 2015 7:57 AM
  • Has anyone found a solution for this? I am running into same problem with SharePoint 2013 with NTLM & ADFS on the same default zone. According to Microsoft engineers (from India), they are clueless.

    Web App B - Claims Types: Integrated Windows Authentication and Trusted Identity Provider (ADFS).  (I amnot able to connect to SharePoint List Data from PowerPivot in Excel)


    mgmjtech

    Thursday, June 30, 2016 3:24 PM