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.
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.
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.
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.
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.
Why is it saying I’ve been denied access?
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-
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.
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 -
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 -
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.
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.
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.
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.
This, sir, was precisely the information I required to make my life suck a lot less over the next week or two. Thanks for posting.
I'm glad that it was helpful! Just remember, when you reset the password for the service account you will need to change it in the SharePoint SSS permissions otherwise things will again re-appear :)
I outlined this in this blog post and it might come in handy if this post was one you liked. blogs.technet.com/.../tfs-2010-service-account-password-changes-breaks-excel-web-services-sss-apppools-in-moss.aspx