Unable to retrieve external data
We aredeploying 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 toretrieve external data for the following connections:
<Server Name> <Cube Name>
The datasources may be unreachable, may not be responding, or may have denied you
access.
Verify thatdata refresh is enabled for the trusted file location and that the workbook data
authentication is correctly set.
How do Iensure a secure and valid refresh operation?
===================================
I followedthe 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 inadvance.
[1870 byte] By [
Marin] at [2008-2-4]
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.
Windows authentication, but tried the SSO as well, without success.
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.
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
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
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.
