Hugo Figueira

Hi all!

I'm trying to drill a AS cube via Excel Services and it gives me the error:

"Unable to retrieve external data for the following connections:

connection


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".

Followed the tasks in http://www.sharepointblogs.com/tonstegeman/archive/2007/03/11/using-analysis-services-data-in-excel-services-part-1-preparing-the-ad-for-kerberos.aspx and in its following pages but no luck here.

I have it working in a single machine, I mean MOSS + AS, but in different machines I haven't managed to get it working...

Have set reading permissions for the odc file and for the excel file for all the application users, and via Excel I can connect and drill the cube at will.

Any ideas

Thx all.



Re: SharePoint - Excel Services connecting to an Analysis Services Box via Excel Services

Hugo Figueira

Hi again.

In case I didn't express myself well, I trying to connect to a AS machine via Excel Services in different machines.

More info on my case:

I created a Excel and configured it to use a SQL source, made a pivot table, publish to Sharepoint and it works!

So the problem is with AS, at least I think so...





Re: SharePoint - Excel Services connecting to an Analysis Services Box via Excel Services

Shahar Prish - MSFT

The "Tome" for external data connections seems to be located here. If you run into problems after following these steps, try posting again.




Re: SharePoint - Excel Services connecting to an Analysis Services Box via Excel Services

Hugo Figueira

Hi Shahar.

Thanks for your reply. Read it and made the steps described there, but still not working...

I'll try to explain my situation better.

I have a three machine enviroment -> AD + (MOSS, Excel Services) + SQL Server.

I want to use Windows Authentication due to Analysis Services. When creating a pivot table in Excel I can browse, drill, etc the data from AS.

After publishing it to Sharepoint, when opening the Excel, it gives me the message described in first post.

If the data source is SQL Server, it works in Excel and Excel Services.

I have a embedded connection in the workbook, and confirmed that in Trusted File Locations that embedded are enabled. The security definitions for the Sharepoint Trusted File Location is Visualization for the users that need to access the book.

I need to use the MSOLAP.3 provider and checked that it's already a trusted provider.

Other configurations:

Set Sharepoint to delegation mode. This was done with the following commands:

stsadm -o set-ecssecurity -ssp "Shared Services" -accessmodel delegation

stsadm -o execadmsvcjobs

From the "tome" Smile I understanded, at least I think so, that I need to enable constrained delegation (Kerberos).

Setting Kerberos

I have set the Kerberos following the steps described in http://www.sharepointblogs.com/tonstegeman/archive/2007/03/11/using-analysis-services-data-in-excel-services-part-1-preparing-the-ad-for-kerberos.aspx.

Do you think is something wrongfully configured described above

Thanks again!

Hugo





Re: SharePoint - Excel Services connecting to an Analysis Services Box via Excel Services

John Campbell

I wasn't sure what you meant by theTrusted File Location is Visualization - there is no such setting that I am aware of. Can you explain what you meant by that Currently, we don't have any documentation on how to configure Kerberos constrained delegation end to end for a MOSS2007 + Excel Services environment. We do have articles on configuring Kerberos in general, and these should be enough theoretically, but setting up Kerberos can be tough even for the pros.

Do you need to use Kerberos, or could you use SSO

John





Re: SharePoint - Excel Services connecting to an Analysis Services Box via Excel Services

Hugo Figueira

Hi John,

What I mean with Visualization is the type of permission that was set for the users that can access the Excel documents in the Document Library selected has Trusted File Locations.

I'm trying to use Kerberos because from what I read, I thought it was the only way to pass Windows Authentication tokens from machine to machine. Can I use SSO for this

Btw, refreshing from an SQL Server Database doesn't work also in my case. I assume it did because I could browse the data, but hitting the refresh, the dreaded error. Perhaps this was misleading sorry.

Going to keep trying, thx!

Hugo





Re: SharePoint - Excel Services connecting to an Analysis Services Box via Excel Services

John Campbell

Hi Hugo,

I understand now. The View Only permissions you have set on the document library shouldn't be affecting your data refresh in this case. You can indeed use SSO instead of Kerberos for your scenario. I will break the rest of this post into two parts: 1) some more resources on troubleshooting Kerberos configuration, 2) some pointers to resources to get SSO configured.

1) Kerberos:

Take a look at this post on configuring Kerberos. You can also see this page for a good source of general Kerberos configuration information.

There are two really common problems people hit - one the AccessModel isn't set correctly. It looks like you have already taken care of that. Two - the SPNs aren't registered correctly for the data source. If, in the ULS logs, you get "...NT AUTHORITY/ANONYMOUS USER" in the external data category, then you probably need to take another look at your SPNs.

Also, as a sanity check, open the workbook in Excel client and make sure the server auth type is set to Windows and not None, or SSO.

2) SSO

You can set this up. The idea is that you create (preferably from a performance and scale perspective) a group mapping in SSO, to map all your Windows users to the same group. Make sure to set the check box which says they are Windows credentials, and DON'T set the checkbox that says anything about restricted credentials. You can also create individual mappings in SSO. This can be tricky as you need to find a way to populate everyone's credentials - typically you would write a page that your users would visit, and that page would set the credentials for your users in SSO (this isn't provided out of the box with Excel Services). Another option is to use Enterprise SSO, whcih ships with HIS server or BizTalk - this SSO has the functionality to have multiple group mappings, or auto population of individual credentials in an individual mapping case.

Once you have SSO setup, in the workbook, you just set the server auth type to be SSO, and enter the application ID where your credentials are stored.

The above directions were brief, and due to the complexity of hte subject matter, cryptic. Read the overview, and then the SSO section, of a paper I wrote at this location. It will give you the background, so that what I said above makes sense, as well as screen by screen and step by step instructions for configuring the authentication type in Excel client, as well as SSO configuration.

Thanks,
John





Re: SharePoint - Excel Services connecting to an Analysis Services Box via Excel Services

Hugo Figueira

Hi again.

Thx for your reply. Indeed I was able to get it working with Kerberos, it was because Shared Services wasn't in delegation mode...

John, your reply was awesome, thx for your time.

WORKING Smile

Thx all