locked
Unexpected Error: PowerPivot Data Refresh - Configure Schedule - Connect using the following Windows user credentials RRS feed

  • السؤال


  • trying to get the data refresh configured ...

    If i..
     - create a schedule and choose "Connect using the following Windows User Credentials"
     - input a valid username/password
     - click OK

    All i get is:

    Error
    An unexpected error has occurred.

    Troubleshoot issues with Microsoft SharePoint Foundation.

    Correlation ID: 4f0d1622-d367-4249-ae0b-1f8b54d666fc


    The ULS Log shows no real troubleshooting data:
    08/30/2010 14:26:02.82* w3wp.exe (0x2958)                        0x13BC SharePoint Foundation          Runtime                        tkau Unexpected ... at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)    Exception rethrown at [0]:      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)     at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)     at Microsoft.AnalysisServices.SharePoint.Integration.IGeminiServiceApplication.CreateTargetApplication(String appID)     at Microsoft.AnalysisServices.SharePoint.Integration.GeminiServiceApplicationProxy.CreateTargetApplications(IGeminiServiceApplicationClient channel, DataRefreshSchedule drs)     at Microsoft.AnalysisServices.SharePoint.Integration.GeminiServiceApplicationProxy.PersistDataRefreshSchedule(DataRefreshSchedule drs)     at Microsoft.AnalysisServices.SharePoint.Integrat... b9360c0e-a11a-4219-ac60-78f148e1edb4
    08/30/2010 14:26:02.82* w3wp.exe (0x2958)                        0x13BC SharePoint Foundation          Runtime                        tkau Unexpected ...ion.ManageRefresh.OKButton_Click(Object sender, EventArgs e)     at System.Web.UI.WebControls.Button.OnClick(EventArgs e)     at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)     at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)     at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) b9360c0e-a11a-4219-ac60-78f148e1edb4
    08/30/2010 14:26:02.83  w3wp.exe (0x2958)                        0x13BC SharePoint Foundation          Monitoring                     b4ly Medium   Leaving Monitored Scope (Request (POST:http://pbbi02:80/PlanB/_layouts/PowerPivot/ManageDataRefresh.aspx?list=%7BC6581C33%2D2F94%2D4BCC%2DA0E7%2D6C2463FBA505%7D&ID=2&Source=http%3A%2F%2Fpbbi02%2FPlanB%2FSitePages%2FHome.aspx)). Execution Time=1352.91412735418 b9360c0e-a11a-4219-ac60-78f148e1edb4
    08/30/2010 14:26:04.07  w3wp.exe (0x2950)                        0x1F5C Excel Services Application     Excel Calculation Services     8jg2 Medium   ResourceManager.PerformCleanup: Memory Manager: CurrentSize=373182464. 53fed7f1-0da4-653f-0000-000050f7b00c

     

     

    20/رمضان/1431 01:06 م

الإجابات

  • Hi JJ78,

      PowerPivot for SharePoint is part of SQL 2008R2 so all PowerPivot for SharePoint QFEs and SPs are SQL 2008R2 QFEs and SPs.

    I got the phone number to call to open a case on this and start the QFE process (no one has actually requested this yet). You can call 1-800-936-3100 (it is a general number so I have no idea what they will start with asking you). Reference this thread and you can also reference my name (Lee Graber) and indicate that the issue is related to Product Studio defect 20096257 (VSTS #419411). They should understand how to interpret these numbers.

    On a separate note, the doc I pointed you to was for setting up a target application which you could specify on the Manage Data Refresh page. For the unattended account, a basic individual package with the credentials associated with the farm account I believe would have worked for you. Regardless, I am glad you are able to proceed with functional testing and we will get a fix out for this issue so that you can eventually go into production.

    THanks
    Lee


    Lee Graber
    • تم وضع علامة كإجابة بواسطة JJ78 29/رمضان/1431 04:37 م
    23/رمضان/1431 05:26 م

جميع الردود

  • additional information: i already configured the powerpivot service application and activated "Allow users to enter custom windows credentials"

    20/رمضان/1431 01:10 م
  • Are you sure that Secure Store is correctly configured. We should have been giving you a better error than that but have you tried creating a Secure Store Target Application? You could follow the online steps for creating the Unattended Account which will walk you through creating a Target Application to make sure that Secure Store is working correctly.

    Also, is this a one machine farm? The stack you sent is failing based on the return value from our backend "PowerPivot System Service". There should have been some trace from that level also? Could that have been on a different machine?

     

    THanks
    Lee


    Lee Graber
    20/رمضان/1431 02:20 م
  • Yes, Secure Store is configured and in use by Excel Services, Visio Services...

    The unexpected error went away after i switched the authenficationmode for the webapp from NTLM to Kerberos!
    So i assume kerberos is a MUST HAVE if you want to use PowerPivot??? Did i miss the hint in powerpivot-documentation for this?

    After enabling Kerberos i can configure a schedule with "Connect using the following Windows User Credentials" without errors.
    BUt refresh still doesn't work, now i get following errormessage:

    Credentials were not found for the current user within the target application 'PowerPivotDataRefresh_50bfea16-2950-434a-8b42-c9072dd411f1_S-1-5-21-1877243322-2996294252-2553617595-3641'. Please set the credentials for the current user.

    So i further assume that powerpivot should create and use a SSS-Application on the fly if you configure "Connect using the following Windows User Credentials" ?
    Unfortunately the SSS-Applications are NOT deleted in my system, if i open the SSS-Management i can see now many newly created apps with guids in name..

    What can i do to get this datarefresh running?  As PowerPivot can create the SSS-Application i assume that its not a permission issue?

     

    21/رمضان/1431 08:20 ص
  • i activated logging for the SSS as described here: http://jochenj.spaces.live.com/blog/cns!9ECE63778227C1B2!2980.entry?&_c02_vws=1

    If i configure "Connect using the following Windows User Credentials" i can see that all SSS audit-entries have UserIdentityClaimType = svcPBBI02_FA
    WHY is the Farm Admin Account used for retrieving the restricted credentials?? In my understanding it should be the account "svcPBBI02_POW"!?

    Service Accounts:
    Web Application Pool - SharePoint - 80  = svcPBBI02_svc
    Windows Service - SQL Server Analysis Services = svcPBBI02_POW
    PowerPivotServiceApplication2 (PowerPivot Service Application)  = svcPBBI02_POW
    Excel Services Application (Excel Services Application Web Service Application)  = svcPBBI02_svc
    Secure Store Service (Secure Store Service Application) = svcPBBI02_svc
    Windows Service - Claims to Windows Token Service = Local System
    Farm Account = svcPBBI02_FA

    Data Refresh Not Working If PowerPivot System Service andFarm Account Are Different:

    For testing i changed the Serviceaccount of "Windows Service - SQL Server Analysis Services" and "PowerPivotServiceApplication2 (PowerPivot Service Application)" to the Farm admin, that also doesn't help!

    Local Server SQL Groups:
    I also included every serviceaacount in the local windows group "SQLServerMSASUser$PBBI02$POWERPIVOT" without success!

     The result is always the same, if i try to use "Connect using the following Windows User Credentials" i get following error:

    Credentials were not found for the current user within the target application 'PowerPivotDataRefresh_50bfea16-2950-434a-8b42-c9072dd411f1_S-1-5-21-1877243322-2996294252-2553617595-1158'. Please set the credentials for the current user. (unable to send notification email)

    If i try to use "Use the data refresh account configured by the administrator" i get following error:

    Credentials were not found for the current user within the target application 'PowerPivot2'. Please set the credentials for the current user.

     

     

     

    21/رمضان/1431 10:48 ص
  • Hi JJ78,

       Couple of things. First the part you want / don't want to hear. You are have hit a bug that we (very) unfortunately ended up shipping with. We have an issue in our data refresh code path when our service app account is different from the farm account. I have not hit the error you got when creating the schedule(I will look into that) but I know that after saving the schedule, the error you are getting is our bug. Unfortunately this bug was discovered too late. Having said that, I have contacted our support team and am getting you a phone number / email to contact to get a fix for this/

    In the meantime, I would like to help you get this up and running so that you can continue testing / using these features. One way to workaround this issue is to pre-create the Secure Store Target Application. If you follow the steps at the bottom of http://msdn.microsoft.com/en-us/library/ee210671.aspx on creating a pre-configured secure store app and make sure to add the farm account to the list of group members with access to the credentials, you should be able to workaround this for the time being.

    As to your earlier comment on Kerberos ... we do not require Kerberos, very explicitly. I have not seen the error you are getting on creating a schedule, but would like to explore that more with you. Again, we do not require Kerberos and so something else is going on.

    HTH
    Lee


    Lee Graber
    21/رمضان/1431 10:44 م
  • Hi Lee,

    THANK YOU, this is important information :) A hotfix for the bug would be very welcome, with the current bug i cannot install powerpivot in the production environment! Are there any plans/Timeframe for a service pack 1 for powerpivot? Or is this bundled with SQL2008R2 service pack schedule?

    After creating an SSS-App with type "Group", assigning farm admin account as an member and assigning this SSS-App as untattented account in powerpivot configuration refresh WORKS!

    >As to your earlier comment on Kerberos ... we do not require Kerberos, very explicitly. I have not seen the error you are getting on creating a schedule, but would >like to explore that more with you. Again, we do not require Kerberos and so something else is going on.

    Don't know where the error was from, for testing i switched the webapp back from kerberos to NTLM and it works now also with NTLM without problem!

    22/رمضان/1431 07:30 ص
  • Hi JJ78,

      PowerPivot for SharePoint is part of SQL 2008R2 so all PowerPivot for SharePoint QFEs and SPs are SQL 2008R2 QFEs and SPs.

    I got the phone number to call to open a case on this and start the QFE process (no one has actually requested this yet). You can call 1-800-936-3100 (it is a general number so I have no idea what they will start with asking you). Reference this thread and you can also reference my name (Lee Graber) and indicate that the issue is related to Product Studio defect 20096257 (VSTS #419411). They should understand how to interpret these numbers.

    On a separate note, the doc I pointed you to was for setting up a target application which you could specify on the Manage Data Refresh page. For the unattended account, a basic individual package with the credentials associated with the farm account I believe would have worked for you. Regardless, I am glad you are able to proceed with functional testing and we will get a fix out for this issue so that you can eventually go into production.

    THanks
    Lee


    Lee Graber
    • تم وضع علامة كإجابة بواسطة JJ78 29/رمضان/1431 04:37 م
    23/رمضان/1431 05:26 م
  • Hello, I seem to be having issues with the Secure Store Service Target Applications and not sure why. Just when you think it's setup correctly the Manage Data Refresh schedule returns 'Access Denied'. This issue also seems to be affecting a normal Excel (non-PowerPivot) refresh with similar 'Access was denied by the external data source'. Is this related to the bug mentioned above? Does the app ID need read access to the data source? Or is the app ID only used in the Secure Store service?
    27/ذو القعدة/1431 03:24 م
  • Hi Emilio,

     

    What do you mean by refreshing a non-PowerPivot workbook? Scheduling a data refresh through the Manage PowerPivot Data Refresh (this fails for non-PowerPivot workbooks), or while viewing the workbook in the broser clicking on Refresh (or Refresh All Connections) in the Excel's ribbon (again, in the browser)?

    You can learn more about Excel Service delegation here: http://powerpivotgeek.com/2009/12/11/excel-services-delegation/, but again, keep in mind that this article was meant for PowerPivot workbooks.

    Best,

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    27/ذو القعدة/1431 04:46 م
  • This is when using an Excel Workbook with a non-PowerPivot data source. The connection is to a .odc file saved in a Trusted Data Connection Library and I am trying to refresh from within the Excel Service browser using the Data button, Refresh All Connections. I get this error:

     'Access was denied by the external data source'

     


    Emilio
    27/ذو القعدة/1431 08:50 م
  • This whole product is buggy and poorly documented.  Errors abound and they are meaningless.  Shame on MS for providing us tools that don't work and are hard to configure.  I have spend countelss hours getting these services to work in my development farm only to find they do not work in my production environemnt...same meaningless errors.  Pelase do us all a favor and either leave these tools out of the product or give us some error messages that help us solve the problem.  Saying that the data refresh doesn't work and to look in your elusive log files is total bull$hit.
    19/جمادى الأولى/1432 04:34 م
  • All,

    I am seeing this same error.  Credentials were not found for the current user within the target application 'PowerPivot'. Please set the credentials for the current user.

    I originally had the PowerPivot service running as the farm admin account.  I was seeing this issue then already.  Tried to change it to a dedicated account and then added the farm admin account as a member to the Secure Store account but I am still seeing this issue.  Is there another possible cause for this issue?  Any help would be greatly appreciated!

    Thanks,

    Melissa


    08/رجب/1432 05:50 م
  • Experiencing this same issue at a customer.

    Steps:

    1)  Navigate to PowerPivot workbook in a powerpivot gallery and click the calendar Icon to schedule data refresh.

    2) specify "connect using the following windows credentials" and specify an account with access to the underlying data source.

    Receive "an unexpected error"

    Software versions:

    SharePoint 2013

    SQL Server 2012 SP2

    ULS logs show:

    System.ServiceModel.FaultException`1[[System.ServiceModel.ExceptionDetail, System.ServiceModel, Version=4.0.0.0, Culture=neutral,

    PublicKeyToken=b77a5c561934e089]]: Access is denied to the Secure Store Service.   Server stack trace:      at

    System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion

    version, FaultConverter faultConverter)     at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation,

    ProxyRpc& rpc)     at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation,

    Object[] ins, Object[] outs, TimeSpan timeout)     at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage

    methodCall, ProxyOperationRuntime operation)     at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)    Exception

    rethrown at [0]:      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)     at

    System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)     at

    Microsoft.AnalysisServices.SPAddin.IGeminiServiceApplication.CreateTargetApplication(String appID, Boolean isWindowsUser)     at

    Microsoft.AnalysisServices.SPAddin.WBDS.Utils.ExecuteWithRetriesOnEndPoint(String operationName, Int32 numOfRetries, Uri endpoint,

    CodeToRunOnChannel codeBlock)     at Microsoft.AnalysisServices.SPAddin.GeminiServiceApplicationProxy.ExecuteWithRetriesOnDiffEndpoints

    (String operationName, CodeToRunOnChannel codeBlock, Int32 numOfRetries, Nullable`1 preferredEndpointIndex)     at

    Microsoft.AnalysisServices.SPAddin.GeminiServiceApplicationProxy.ExecuteWithRetriesOnDiffEndpoints(String operationName,

    CodeToRunOnChannel codeBlock, Int32 numOfRetries)     at

    Microsoft.AnalysisServices.SPAddin.DataRefresh.ExecuteAsSpecifiedCredentials.PersistConfiguration(GeminiServiceApplicationProxy proxy,

    Guid itemID)     at Microsoft.AnalysisServices.SPAddin.DataRefresh.DataRefreshSchedule.SaveSchedule(String eTag, SPListItem item)     at

    Microsoft.AnalysisServices.SPAddin.DataRefresh.ManageRefresh.OKButton_Click(Object sender, EventArgs e)     at

    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)     at System.Web.UI.Page.ProcessRequestMain(Boolean

    includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint

    When can we expect a hotfix if this is a known issue?

    19/محرم/1436 12:04 ص