Displaying Workbook using external data with periodic refresh in EWA  
Author Message
KrisF





PostPosted: SharePoint - Excel Services, Displaying Workbook using external data with periodic refresh in EWA Top

Hello. I have a very simple workbook containing a Pivot Table that is populated by an external data source (SQL Server 2005 database). The connection properties of the external data source are configured to Enable background refresh, Refresh every 1 minute, and Refresh data when opening the file. The connection is configured to use Windows Authentication. The workbook resides in a Trusted File location.

When I open the workbook in Excel 2007, the refresh works fine. No problems.

When the workbook is opened in EWA, it appears to load a cached (old) version of the workbook, then the first time a refresh occurs, the Data Refresh Failed message is displayed. I have closely followed the "instructions" provided at http://www.hide-link.com/ , but can't seem to solve this problem. Any help would be very much appreciated. Thanks.

KrisF



SharePoint Products and Technologies4  
 
 
Ira Levin





PostPosted: SharePoint - Excel Services, Displaying Workbook using external data with periodic refresh in EWA Top

Have you setup kerberos delegation between the SharePoint server and SQL

Are there any errors reported in the event log for the failed refresh operation

What is the topology you've setup the server Do you have all server components installed on a single machine if yes, then please log in to that machine access the workbook from a local browser instance and try to refresh. If it doesn't succeeds I suggest you try the following:

stsadm -o set-ecssecurity -accessmodel delegation -ssp <sspname>

stsadm -o execsvcadmjobs

iisreset

If you are now able to refresh when logged in locally to the server machine, then try from a remote machine (after a forced iisreset), if it doesn't succeed from the remote machine, then most likely you have a kerberos delegation issue. Reply and we'll go deeper.


 
 
KrisF





PostPosted: SharePoint - Excel Services, Displaying Workbook using external data with periodic refresh in EWA Top

Ira,

Thanks for your reply. The topology is all server components on same machine: Windows Server 2003 SP1, MOSS 2007, SQL Server 2005 SP1, Office Pro 2007, VS 2005, .NET Framework 3.0, IE7, ... the whole enchilada.

Per your suggestion, I logged in to this machine, opened IE7, entered http://myServer/Docs/Documents/EWATestBook.xlsx as the url, and pressed Enter. A File Download dialog opened up asking if I wanted to open or save the file. I selected Open. The workbook opened up in Excel 2007. IMPORTANT: Excel 2007 presented "Security Warning - Data connections have been disabled" along with an Options... button. At this point, some old data was being displayed in the Pivot Table. I selected the Options... button and a Microsoft Office Security Options dialog was displayed which said, "Data connections have been blocked. If you choose to enable data connections, your computer may no longer be secure. Do not enable this content unless you trust the source of this file. File Path: http://myServer/Docs/Documents/EWATestBook.xlsx". In addition, two radio buttons labelled Help protect me from unknown content (recommended) and Enable this content. Obviously, I trust this content AND the workbook resides in a trusted file location so I chose Enable this content and pressed OK. Immediately the values from the database were updated in the Pivot Table. Then, I manually opened the database table and updated some values. After a short delay (refresh period), the values were updated in the Pivot Table as expected. I did several manual updates and each upate was refreshed properly. So, it seems this test succeeds.

Concerning errors reported in the event log, no, none reported there. However, in the log file residing in C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS, there appears to be a bit of a hiccup. Here are the pertinent lines...

Excel Web Access 5cbi Medium InternalEwr.OnInit: Starting loading external EWR

Excel Services Web Front End 6k38 Medium ExcelServerProxy.ExecuteWebMethod: sending request of type RefreshAll, to server=http://oldi-server:56737/SharedServices1/ExcelCalculationServer/ExcelService.asmx, session=22.335wtNe/7C8ARk7FZ3Qi0e90.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, state id=2

Excel Services External Data 483a Warning Excel Services can not use credential delegation when it is configured to use a trusted subsystem with Windows SharePoint Server. [Session: 22.335wtNe/7C8ARk7FZ3Qi0e90.5.en-US5.en-US73.+0300#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: WORKGROUP\kfraley] 00000091-45d8-d51c-0000-000050f7b00b

Excel Calculation Services 5cag Medium ExcelService.PostProcessRequest: finished request of type RefreshAll 00000091-45d8-d51c-0000-000050f7b00b

The Excel Services External Data Warning above seems to have a haunting resemblance to your first question. Incidentally, my answer to your first question is, no, I have not setup kerberos delegation between the SharePoint Server and SQL. Frankly, being a relative newbie to MOSS 2007, I lack the know-how to perform this setup at present. Not to mention the fact that I did not know it was necessary. Anyway, do you recognize this warning Is the warning telling me that I need to setup kerberos delegation If so, can you point me to some instructions related to how this is done Or, if it is simple, can you just include them in a reply In the mean time, I'll see what I can find.

Sorry so long. Thanks very much for your help. I look forward to your reply.

Oh, one other quick thing, I have not attempted the stsadm commands yet. Is that something I should try before or after setup of kerberos delegation I'm going to hold off running those commands until I hear back from you. Thanks again.

KrisF


 
 
Ira Levin





PostPosted: SharePoint - Excel Services, Displaying Workbook using external data with periodic refresh in EWA Top

Kris,

If SQL and MOSS are all installed on the same machine, then all you have to do is run the stsadm commnads I mentioned in my previous post. As long as SQL and MOSS are on the same machine, you don't need to setup Kerberos delegation, and you should be able to refresh the data from any browser on any machine.

The "hiccup" you observed in the logs is alerting that you're trying to refresh data for a data connection that is using windows integrated security to authenticate with the data source (SQL,) but since currently you have Excel Services setup such that it doesn't delegate the end user windows identity context between its tiers (a.k.a Trusted subsystem access model,) the connection can't be refreshed. Switching Excel Services to Delegation access model will enable that.

In the Trusted Subsystem model you can only refresh connections that have their authentication method set to either None or SSO. You can find more details on these authentication types and Trusted Subsustem model in the technet article you mentioned in your original post.

One more thing... from your previous reply I got a bit confused as to what you're trying to achieve. The test you detailed is for the behavior in Excel 2007 client, not Excel Services, although from your original post it seems you're trying to resolve a data refresh issue using Excel Services. The information I provided is relevant to Excel Services, and will not affect the client. The warnings in Excel Client are not related to Excel Services, and the concept trusted locations is different for each and aren't related. If you still have questions regarding Excel Client 2007 I suggest you post in Excel Client forum, and continue to use this forum for Excel Services related content.


 
 
Randine





PostPosted: SharePoint - Excel Services, Displaying Workbook using external data with periodic refresh in EWA Top

A key part of the security model is to implement Kerberos authentication which enables user credentials to be validated at every service level. The Kerberos authentication system works with tokens that are received (using high encryption) during login to a system. In order to carry this authentication ticket through to other sub-systems, such as SQL Server and Analysis Server, we need to use delegation.

Delegation means granting a service the right to forward an authentication ticket on to another service. For obvious reasons this ability should be limited to only the intended operations. We set up delegation using the Windows Server 2003 tool SETSPN. This writes information to Active Directory indentifying the specific service on each machine that is allowed to delegate using a specific service domain account.

SETSPN can be downloaded as part of the Windows Server 2003 Service Pack 1 Support Tools from http://support.microsoft.com/kb/892777

Details on SETSPN can be found here:

http://www.mosha.com/msolap/articles/kerberos_delegation.htm

http://technet2.microsoft.com/WindowsServer/en/library/b3a029a1-7ff0-4f6f-87d2-f2e70294a5761033.mspx

By running

Setspn.exe -L Username command in a DOS window we can see the current setup for the Username domain account:

Registered ServicePrincipalNames for CN=Username ,CN=Users,DC=domain,DC=com:

HTTP/yourdomain

HTTP/mysite.yourdomain.com

Once Kerberos is set and you have set the sub system to Delegation mode etc

1) Go to the Central Admin page

2) Goto the Operations tab

3) Enable Enterprise feature on all sites

4) Tick the Enable feature, this time around it will take a while so please be patient

5) On completion, please goto the site containing your excel files

1) Goto Site Settings

2) Modify All Settings

3) Select Site Feature

4) If your Office Sharepoint Server Enterprise Site feature is active, then de-activate it then re-activate.

These are the steps I followed when we switched to Delegation mode from Trusted with Kerberos.



 
 
KrisF





PostPosted: SharePoint - Excel Services, Displaying Workbook using external data with periodic refresh in EWA Top

Ira,

I ran the stsadm commands ... problem solved! Thank you!

Sorry about the confusion you mention in your 4th paragraph. Honestly, I was unclear about what you wanted me to try. In your first reply you wanted me to "... log in to that machine [and] access the workbook from a local browser instance ...". My paragraph that begins, "Per your suggestion..." was in response to what you wanted me to try. Obviously, the outcome of what I tried opened the Excel 2007 client application. For future reference, what did you mean by "... log in to that machine [and] access the workbook from a local browser instance ..." I'm assuming that what I did was not what you intended me to do.

Anyway, again, thanks very much friend. The stsadm commands solved my problem AND I learned quite a bit in the process.

KrisF