I’ve been struggling for quite a long time with Excel Web Access in combination with a SQL Analysis Server and SharePoint.
The problem is that I am able to display an excel file (which is in a SharePoint web part), but whenever I choose to refresh the connection I get the following error:
Unable to retrieve external data for the following connections:
[ODC File]
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.
Here is a screenshot:
Finally today I got this error resolved.
First of all I did all the action that MVP – Ton Stegeman did in his blog about this subject. Check it out here (follow all 4 parts). He ends in part 3 with the error I have, but there was no solution provided.
First of all I needed to make sure that Kerberos is working and did not have a double hop problem, and that I could access the SSAS server and his cube. For that I added a SQL Server 2005 Analysis Services Filter Web Part and specified the same ODC file. It worked great (I could select a dimension and a Hierarchy).
The problem with this error is that I am running MOSS and SSAS both on a Windows 2008 Server (2 different servers btw.). You might think that it should not be any different than a 2003 server, but Server 2008 is “AES aware” (Advanced Encryption System). There is a problem with AES aware systems like W2K8, Vista and the use of Kerberos.
To get it to work, you will have to follow the steps below:
- Open the following file in Notepad: “c:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini”
- There is a section called Security, which looks like this:
<Security>
<DataProtection>
<RequiredProtectionLevel>1</RequiredProtectionLevel>
</DataProtection>
<AdministrativeDataProtection>
<RequiredProtectionLevel>1</RequiredProtectionLevel>
</AdministrativeDataProtection>
<RequireClientAuthentication>1</RequireClientAuthentication>
<SecurityPackageList/>
<DisableClientImpersonation>0</DisableClientImpersonation>
<BuiltinAdminsAreServerAdmins>1</BuiltinAdminsAreServerAdmins>
<ServiceAccountIsServerAdmin>1</ServiceAccountIsServerAdmin>
<ErrorMessageMode>2</ErrorMessageMode>
<CellPermissionMode>0</CellPermissionMode>
</Security>
- Change the tag <DataProtection> and <AdministrativeDataProtection>, so it looks like this:
<Security>
<DataProtection>
<RequiredProtectionLevel>0</RequiredProtectionLevel>
</DataProtection>
<AdministrativeDataProtection>
<RequiredProtectionLevel>0</RequiredProtectionLevel>
</AdministrativeDataProtection>
<RequireClientAuthentication>1</RequireClientAuthentication>
<SecurityPackageList/>
<DisableClientImpersonation>0</DisableClientImpersonation>
<BuiltinAdminsAreServerAdmins>1</BuiltinAdminsAreServerAdmins>
<ServiceAccountIsServerAdmin>1</ServiceAccountIsServerAdmin>
<ErrorMessageMode>2</ErrorMessageMode>
<CellPermissionMode>0</CellPermissionMode>
</Security>
- Stop and restart Analysis Services
- Then edit your ODC file and add the following to the connection strong:
”;SSPI=Kerberos;Protection Level=Connect” (without the quotes)
Now try again.
Tip:
- restart IIS every time you make a change in your ODC file.
Apparently there is a bug in Service Pack 2 of SharePoint 2007. 
.
