We are deploying SQL Server 2005 with Analysis and Reporting services. This deployment is still in test/development phase. At this point I am stumbled at publishing an Excel 2007 pivot table (linked to the SQL 2007 AS data cube) on the SharePoint 2007 server. I can publish a pivot table, but when I try to update/filter in a web browser I am getting following error:
===================================
Unable to
retrieve external data for the following connections:
<Server Name> <Cube Name>
The data sources may be unreachable, may not be responding, or may have denied you access.
Verify that data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set.
How do I
ensure a secure and valid refresh operation
===================================
I followed the Step-by-Step instructions from the MS TechNet article "Plan external data connections for Excel Services", but it did not resolve the problem.
Thank you in advance.
Unable to retrieve external data
ShrikantBijapurkar
Sephiroth_
Did the above posts answer your question If so could you please mark it as the answer
Otherwise let us know what you still need help with.
Thanks!
- Luis
AsifHameed1
Hey Marin,
Here is what I have found, your situation may be different. What happens when MOSS creates the SSO database it creates an index on the SSO_Credentials table with the name IX_SSO_Credentials. However, when I created a new encryption key the index was renamed to IX_SSO_TempCredentials. The SSO service could not find the credentials it was looking for because it is looking for IX_SSO_Credentials. When I renamed IX_SSO_TempCredentials to IX_SSO_Credentials everything worked fine. For a while I thought that SSO didn't work and I needed to setup Kerberos, but this took care of it. Maybe this will help someone.
Modeller
Which type of authentication setting did you select for the connection you authored in excel
Is SharePoint 2007 server installed on a single machine Is AS installed on that machine, too
You can check the application event log for more information as to why the connection fails to establish. Also, through central admin, you can enable Verbose logging for the Unified Logging Service (ULS) logs and monitor the logs for the External Data category.
Robert Mileski
Hi George,
The solution you've provided helped us to solve this problem. We've spend a lot of time to enable refresh of a pivot within Excel Services. Only after I manually renamed the name of the index from IX_SSO_TempCredentials to IX_SSO_Credentials the refresh of data worked! Thank you very much for this tip!
Regards,
Reinier
chamsoft
Let me know if you find any solution.
TIA
VoiceOfExperience
I experienced the above problem and i just run the following commands and it worked fine
>stsadm -o set-ecssecurity -accessmodel delegation -ssp <the shared services name>
>stsadm -o execadmsvcjobs
>iisreset
Thanks for posts
Hosam Arafa
dophine
If the AS is on a different box, and you wish to use windows authentication, then you'll need to establish a kerberos delegation trust between the two services and switch excel services to use the delegation access model.
If you want to use SSO, then please make sure you've configured the SSO service correctly and that the service is running on the SharePoint machine. Then you'll want to define an application in SSO, and use it in the connection you defined in Excel.
Maybe what you've missed is making the Excel Server shared web service a member of the SSO Admin group. This is necessary for when Excel Services is to the TrustedSubsystem model. Since SharePoint 2007 is all on a single machine, you can safetly switch the system to Delegation, that will no longer require SSO Admin privileges. Please run the following on the SharePoint machine to switch to Delegation access model:
stsadm -o set-ecssecurity -accessmodel delegation -ssp <the shared services name>
stsadm -o execadmsvcjobs
iisreset
kostrin
The SharePoint 2007 is on a single machine, as a part of AD. The AS is on different box.
I checked all event logs and there is nothing (important) logged.