Secure Store with Team Foundation Server 2010. Access denied

Question

Hello

I'm currently running 2 installations of SharePoint 2010 EE with Team Foundation Server. In both installations we have a strange behaviour. Independend of the authetication provider (NTLM or Kerberos) when we configure the Secure Store Service with all the required steps, the Excel Reports are only displayed without any  problems when we add  

a) domain user accounts (one or more) directly or <domain>\Domain Users in the members group

When we add a typicall domain group, for example called TFS_Users, with the sames users as members as in a) the WebPart fails with an error "...connection failed to refresh TfsOlapReport". Additionally, in the local Event log of the SharePoint Server is an entry "

 

 

The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Access is denied.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK)."

 

If a came back and add the default <Domain>\Domain Users Group, all works fine again.

In the ULS Log File the following entry is generated when using the domain group

GetCredentials failed with the following exception: System.ServiceModel.FaultException`1[Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault]: Access is denied. (Fault Detail is equal to Microsoft.Office.SecureStoreService.Server.SecureStoreServiceFault).

Answer 1

Dear aeropostale,

if I understand you right, it sounds like a permissions issue with your SS account and/or an incomplete configuration.

After establishing the SS Target Application ID, you set the Members to be your AD group - that's good.

Next you have to Set the Credential stored by this entry in SS.  Select the SS App ID, (check box), then select Set Credentials.

Enter an account with SQL rights to READ the Databases you are trying to access with Excel.  This account must be a Login in SQL Server and granted the dataReader permissions to each database you wish to report against.

Also, you did not mention it, but you must also configure Excel Services for Trusted File Locations and Trusted Data Connections - both areas need to know where the Excel report and Excel Office Data Connect files are located - they must be trusted.

So... there are 4 areas required to use Excel Services with SharePoint.  1. Secure Store. 2. Excel Services. 3. SQL Server.  4. Active Directory (group and Windows Logins).

IF you have all of this setup correctly and the accounts "align", then Excel Services will render reports without errors.

I should also point out, that Excel only looks at the Windows Login ID of the user (not the SharePoint login) to determine access -

The process goes like this:  A user tries to access the Excel report - aha - get the Windows Login ID of this user - pass it over to Secure Store and look up the Target Appliction ID (based on the SSID set in the Excel report), investigate the "members" area - is this user found in the "list", if so, obtain the credentials stored with this Target ID, and pass that account over to SQL Server for access to the database pointed to by the Excel report (ODC) - if permissions allow, read the data, and return it to the Excel report and render it to the user.

Hope this helps,

---

Thanks, Eric S. http://www.pcubed.com