Udostępnij za pośrednictwem

TFS Dashboards in MOSS 2010 Return Access Denied for TfsOlapReport to End-Users

  • Artykuł

If you’ve decided to integrate your TFS 2010 infrastructure with Microsoft’s enterprise-ready SharePoint Server 2010 (MOSS) then you are in for quite a store, both good and potentially bad.  MOSS is extremely powerful platform that many customers of ours are investing in and we certainly understand why.  However, the frustration often comes when you have competing heads – the TFS infrastructure “guys” and the SharePoint “gals – trying to get all the pieces working and playing together well.

In today’s post, I will share some insight that had our TFS consultants scratching their head and our MOSS folks (me – and can I say I’m limited) spinning around and saying “Just make it work…”  The beauty of working at Microsoft is the ability to find the right people and get the assistance needed to make it work.  This is the reward we get and that reward I’m happy to share with those of you that run into the same thing.

Goal

The goal here is to get TFS integrated fully into MOSS 2010 and, in particular, using Excel Web Services to display critical data in the TFS Dashboards.

Understanding Excel Web Services & TFS Integration

John Socha, a colleague and friend of mine, helped me quickly understand how the TFS Excel reports work with respect to TFS.  Excel workbooks have “cached” data embedded within the XLSX files themselves that is accessible via the Excel or via Excel Web Services.  By default, Excel Web Services (EWS) utilizes a SharePoint feature called Secure Store Service (or abbreviated often as SSS) to allow individuals who do not have direct access to the data source of the workbook to see the data within EWS.

Access Denied by External Data – TfsOlapReport

The reports start coming in as soon as you roll out TFS 2010 that you are having issues with “reports” showing up on the dashboard for each of your team projects.  TFS will create the dashboard anytime you point it towards the MOSS server which is helpful though it doesn’t mean that everything is good to go.

clip_image001

As you can see, the user will see the above error report every time they re-attempt to connect to the site and view the data.  If they click OK, you (and the user) are shocked when data is displayed to the end-user like below.

DynamicImageProvider Why is it saying I’ve been denied access?

Excel Workbook Caching versus Excel Web Services

The reason data is shown after clicking ok is the user has been granted access to the SharePoint site and hence can access the physical Excel workbook.  As mentioned earlier, Excel stores “cached” data from the last time that the workbook is refreshed and this is shown to the user.

You get Access Denied because the end-user has been denied access to the data source, not the entire workbook.  EWS attempts to access the data source directly each time the site is opened and an existing connection isn’t open.  To validate whether you have an issue with your SSS, you can do the following-

  1. Open your MOSS site hosting TFS content
  2. Click the Excel Reports in the land-hand navigation
  3. Click on the name of the Excel report such as Burndownimage
  4. For Data, click Refresh All Connectionsimage

This will verify if your SSS is configured to work as expected, and that within the Members for SSS you’ve granted that user permissions.  If you get an access denied, let’s walk through fixing this problem.

Determining EWS Configuration – One Workbook at a Time

The first step is to verify that your Excel workbook is using the authentication type.  TFS 2010, out-of-the-box, uses SSS and setups a Target Application ID called TFS.  This, of course, is configurable though you need to think about two things -

  1. SSS doesn’t require end-users to have direct access to the TFS cube (TFS_ANALYSIS)
  2. Windows Authentication requires direct access permissions to the TFS cube

SSS is optimal long-term (as put by someone who helped me figure this out) though it requires a bit more configuration than Windows authentication.

To determine which authentication a workbook is using (if necessary), do the following -

  1. Open the workbook in Excel (from SharePoint, not Visual Studio)
  2. Highlight the chart in the report by clicking on it
  3. Under the Data menu item, Connections
  4. Highlight TfsOlapReport, click Propertiesimage
  5. Click Definition tabimage
  6. For Excel Services, click Authentication Settings…image

As you can see, you can select SSS or you can choose Windows Authentication.  In this case, it should be set to the default of SSS – SSS ID:  TFS.  If not, change it to look directly like the above.

Correctly Configuring Secure Store Service (SSS)

If your workbook is configured correctly, and you are still getting access denied, then you will need to use the SharePoint Central Administration Site to correct the problem.  In this section of the post, I will share specifically how I corrected the problem that was occurring throughout our dashboard.

  1. Open the MOSS Central Administration Site
  2. Click Application Management
  3. Under Service Applications, click Manage service applications
  4. In this list of service names, click Secure Store Applicationimage
  5. You should see a Target Application Id of TFS (if not, you need to create one which isn’t the purpose of this article). click on TFS
  6. On the Target Application Settings page, click Nextimage
  7. On the Field Name page, click Nextimage
  8. On the Administrators & Members page, you will need to enter all potential dashboard viewers into the Members box.image
  9. Click OK

After you’ve clicked OK and saved, you can re-test and the users should not get the Access Denied if they are listed in a domain group that they are a member of or you’ve granted them direct access via their user account.

Summary

Doing software development is hard enough, let’s not make it any harder than it has to be.  Enter the handy dandy capabilities of SharePoint to make flashy, useful dashboards to give insight in how your team is executing and most importantly struggling.  Then it doesn’t work.  This is a challenge set of applications coming together as a “mash up” that one simple step missed can cause it to come tumbling down.  There is documentation out there that can help “lead” you to the problem but unless you know a)  TFS  b)  Excel Web Services  c)  SharePoint then your life is probably going to be a bit complicated.  In today’s post, I shared some insight into one problem that many of you might very well have and hopefully will make your life much better.

Enjoy!

-Chris

Comments