Data Refresh issues.. Yay oh the joy

Question

I'm not going to hide my lack of motivation for working with PowerPivot due to the fact that it is extremely unstable for use in SharePoint after spotting countless workarounds on the interweb..

I've configured the lot using http://msdn.microsoft.com/en-us/library/ee210616.aspx#deploywsp and finally got to the point where I want to get some data auto-refreshed.

When I get to the 3 options for the 'Credentials', I can't get it working with any of the 3.

Trying my luck with option1:Use the data refresh account configured by the administrator, I get:

Target application not found (application id: domain\serviceaccount) => ????? That application ID doesn't correspond to my Secure store app ID (PowerPivotDataRefresh) but hey thanks for trying.

Trying my luck with option2: Connect using the following Windows user credentials, I get:

An unexpected error has occured: <some correlation ID>. Checking in the ULS logs:

System.ServiceModel.FaultException`1[[System.ServiceModel.ExceptionDetail, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]: Access is denied.   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)    ... 1abd0893-1dc6-4445-8d26-7e72a97372c7
03/05/2012 19:28:15.76* w3wp.exe (0x1E30)                        0x2554 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... 1abd0893-1dc6-4445-8d26-7e72a97372c7
03/05/2012 19:28:15.76* w3wp.exe (0x1E30)                        0x2554 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) 

Trying my luck with the 3rd option: Connect using the credentials saved in Secure Store Service (SSS): I am out of luck too with an error :

The provided Secure Store target application is either incorrectly configured or does not exist

I followed another blog about adding the user accout to the secure store admin section for managing service apps but still no avail.

I do believe that Excel services is working as I can open up excel docs via the browser and I have created the secure store following MSDN.

I am tired and unable to think on this for tonight. Hopefully a Microsoft tooth fairy would drop me a hint as to what is going :/

-Mike

Answer 1

Been making a little progress. I got a powerpivot document to update using the SSS application but we've uploaded another that uses an OLEdb connection...we've given both the PowerPivot applicaiton pool account and the account specified for the target application in SSS(desperate I know) owner permissions on the database but getting -

OLE DB or ODBC error: Login failed for user '<ourdomain>\<SSS target app acccount>'.; 28000. A
connection could not be made to the data source with the DataSourceID of
'd919401f-46eb-4886-89e5-22b0d4f5f7e1', Name of 'SqlServer <ourSQLInstance>
DataWarehouse'. An error occurred while processing the 'Query' table. The
operation has been cancelled.

One more thing to mention though, the powerpivot account that actually worked was pointing a database within the same sql instance as SharePoint and was using a .Net SQL provider instead of OLEdb...

Answer 2

Ok more info on this.

When we create a PowerPivot doc with a datasource that connects the SQL database using a SQL Server login, it works and data refresh seems happy. When the users specifies his windows account to the data source, it breaks with the 'Login failed for user' error. As mentioned, the login account that fails is actually our credentials specified in the SSS so I do believe that the data refresh is using the correct account to try and log in. We made that account a sysadmin on the DB server AND dbowner so there is abolutely no reason for it to fail.

This article leads me to believe that it is not the double-hop issue but thusfar I cannot figure out why we cannot get this to work with a Window/AD user account.

Anyone that can shed some light please?

Answer 3

Hi Pikker1981,

For SQL Server Agent, you can configure the services to run under domain user accounts to have a try.

---

Challen Fu

TechNet Community Support

Answer 4

Hi Challen,

Thanks for the reply mate. I think this issue might have been a collection of issues that might together/independently been contributing.

The client's using SQL Server 2008 SP 1 and I suggested that we take that to the latest service pack and CUs. After we did that, I reinstalled the PowerPivot issues and it magically started working.

I know its a bit similar to the "Hey let's rebuild windows" solution for every small problem but we were running out of time with this.

Regards

Mike

Answer 5

Pikker1981, I'm having the same issue with the data refresh.   'Target application not found'.     Did you remove the PowerPivot feature from SQL Server?   Or would I be ok to just retract the .wsp solution from SharePoint and then deploy it again?     thanks