Passing the Excel parameters to the backend

Passing the Excel parameters to the backend

• Jesper Ekenberg

  Seems like you should be using SSO:

  http://technet2.microsoft.com/Office/en-us/library/7e6ce086-57b6-4ef2-8117-e725de18f2401033.mspx mfr=true

  This will allow Excel Services to impersonate the identity you mapped in sso, and use it to connect to the sql server.

  
  

• Spenceee

  Is this a security sensitive restriction If so, you may want to keep all user/permissions checking on the server side since anything you do on the client side is more easily circumvented. You may want to look into using row permissions in which case you get a secure way to have your request from above out of the box (no need to use parameters or anything). Check out http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx

  If this is not a security related request (if it's ok that they are able to workaround this "filter" and it's only there for their convenience) than you can use a stored procedure in SQL to return the current user. Say you have a column with user names that you check you can have something like "select * from myTable where usersColumn=sp_username()"

  I don't have a SQL server handy so I can't remember exactly what the stored procedure is, but I believe it's something like "sp_currentser()" or "sp_username()" (if you have trouble finding this let me know and I'll dig into it).

  
  
  

• ruippeixotog

  I changed the account setting for the app pool in IIS directly, I didn't know you could change it in central admin. The SSO service is the same account as the app pool now, but it's not the same account that was used to install MOSS. The account used to install MOSS is the local admin, and the SSO admin is a domain account, part of the local admins group.

  Yes all is on one machine, I will try your suggestion to switch to delegation. Hopefully there is nothing more to it than the command line you indicated.

  Many thanks for your help.

  
  

• GustavMahler

  OK, this would work in a Windows Auth scenario.

  Unfortunately, we're not in an environment where we can implement Windows Authentication.

  So Excel Services needs to find out which user is currently accessing the system, and pass it as a parameter.

  So you're right here, we have 2 problems, and the first one is how to get the name of the current user in Excel Services.

  The second problem is how do we pass a parameter from Excel Services to SQL Server.

  
  

• mobigital

  Thanks Ira. It's all working now, I can see the Excel report,and I can refresh it using SSO auth.

  Now I need to try and understand what the changes you recommended actually mean to the server installation.

  Thanks again, without your help I would have given up on Excel Services by now.

  
  

• Grant Jenkins

  I ran the suggested command and got "Operation completed successfully", and restarted IIS.

  I now get an "access denied" error when I navigate to the report. I can't display the report, so I don't get to try the refresh.

  
  01/22/2007 13:32:21.42 w3wp.exe (0x0EC8) 0x1334 Excel Services Excel Calculation Services 766u Medium ExcelService.LogRequest: starting request of type OpenWorkbook
  01/22/2007 13:32:21.42 w3wp.exe (0x0EC8) 0x1334 Excel Services Excel Services Session 6k6g High Ecs is set to Trusted Subsystem mode, although request doesn't contain expected work on behalf element. Caller IP: xxxx, Caller Identity: xxx\Administrator
  01/22/2007 13:32:21.42 w3wp.exe (0x0EC8) 0x1334 Excel Services Excel Calculation Services 3sll Medium ExcelService.PostProcessRequest: web method: OpenWorkbook, got exception Microsoft.Office.Excel.Server.CalculationServer.AuthorizationException: Access denied. You do not have permission to perform this action or access this resource. at Microsoft.Office.Excel.Server.CalculationServer.ExcelServiceSoap.ProcessRequestIdentity(WebMethodBehaviorAttribute webMethodBehavior, WorkOnBehalf workOnBehalfRequest) at Microsoft.Office.Excel.Server.CalculationServer.ExcelServiceSoap.PreProcessRequest(CommandParameter parameter, WebMethodType webMethodType) at Microsoft.Office.Excel.Server.CalculationServer.ExcelServiceSoap.ExecuteAsyncBeginWebMethod(CommandParameter parameter, WebMethodType webMethodType, AsyncCallback callback, Object state, AsyncPhaseFactory phaseFactory)
  01/22/2007 13:32:21.42 w3wp.exe (0x0EC8) 0x1334 Excel Services Excel Calculation Services 4qc1 Medium ExcelService.PostProcessRequest: Excel Server Recoverable Exception: Microsoft.Office.Excel.Server.CalculationServer.AuthorizationException: Access denied. You do not have permission to perform this action or access this resource. at Microsoft.Office.Excel.Server.CalculationServer.ExcelServiceSoap.ProcessRequestIdentity(WebMethodBehaviorAttribute webMethodBehavior, WorkOnBehalf workOnBehalfRequest) at Microsoft.Office.Excel.Server.CalculationServer.ExcelServiceSoap.PreProcessRequest(CommandParameter parameter, WebMethodType webMethodType) at Microsoft.Office.Excel.Server.CalculationServer.ExcelServiceSoap.ExecuteAsyncBeginWebMethod(CommandParameter parameter, WebMethodType webMethodType, AsyncCallback callback, Object state, AsyncPhaseFactory phaseFactory)
  01/22/2007 13:32:23.84 w3wp.exe (0x0EC8) 0x10D4 Excel Services Excel Calculation Services 8jfe Medium BackgroundTaskManager.ExecuteBackgroundTasks: Finished task Memory Manager.PerformCleanup
  

  
  

• oulisee

  Try running the following:

  stsadm -o execadmsvcjobs

  
  

• Steve Severance

  Please verify that Excel Services application pool is a member as the SSO administrator group or is the running as the SSO administrator identity:

  When Excel Services is configured in a trusted subsystem mode, the SSP application pool account must be a member of the SSO administrator group. (For more information, see Trusted subsystem and delegation .) This enables SSO ticketing, which Excel Services uses in such configurations, to work. The SSP application pool account is the security account that is used to run the shared services application pool in IIS.

  
  

• JavaBoy

  I have changed the account running the SSP application pool to the same account that is running the SSO Service, and this account is also SSO administrator.

  I get the exact same error when trying to refresh the Ecel report, plus I now get access denied trying to navigate to the Shared Services Administration page (SharedServices1), no matter which account I try to sign-in with.

  
  

• Carsten Kanstrup

  Alan,

  I still suspect the SSO Admin membership as the issue.

  How did you change the account settings for the SSP app pool using IIS directly or through Central Administration

  Please check that the SSO service running as SSO Administrator as well

  What's the topology you're using do you have both SharePoint and SSP installed on the same machine if yes, then you can try and switch Excel Services access model to Delegation. You can do that by running:

  stsadm -o set-ecssecurity -accessmodel delegation -ssp sharedservices1

  and then restart IIS.

  
  

• JFoushee

  I get the following error when I set up SSO according to the article indicated. What does this error code mean Thanks in advance.

  01/18/2007 09:00:50.42 SSOSRV.EXE (0x168C) 0x16B8 SharePoint Portal Server SSO 0 Medium RPC request to s_GetCredentialsUsingTicket
  01/18/2007 09:00:50.42 SSOSRV.EXE (0x168C) 0x16B8 SharePoint Portal Server SSO 0 Medium Gettting copy of the SSO Admin
  01/18/2007 09:00:50.42 SSOSRV.EXE (0x168C) 0x16B8 SharePoint Portal Server SSO 0 Medium Gettting copy of the SSO App Mgr
  01/18/2007 09:00:50.42 SSOSRV.EXE (0x168C) 0x16B8 SharePoint Portal Server SSO 0 High CSSOService::AuthenticateSSOPrivilege() failed
  01/18/2007 09:00:50.42 SSOSRV.EXE (0x168C) 0x16B8 SharePoint Portal Server SSO 0 High Error code is: -2140995579
  01/18/2007 09:00:50.42 SSOSRV.EXE (0x168C) 0x16B8 SharePoint Portal Server SSO 0 Critical The Microsoft Single Sign-on (SSOSrv) service failed to retrieve credentials using an access token. The error returned was 0x80630005. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).

  
  
• Passing the Excel parameters to the backend