New Sharepoint Portal - Excel Refresh Issues

Question

 

New Sharepoint Portal from TFS - Excel windows show popup box. I want to know how to make this just work, because clicking OK, refreshes the data so the authenication message must be wrong as no user has changed.

MSF for Agile Software Development v5.0" was the template used.

The help which goes to "Learn more about data refresh" doesn't help me, i don't want to manaully have to refresh things, i want it to just work!

=> Can you please explain the significance of the TFsOlapReport reference ? in this context

http://abc-tfs01/Reports/Pages/Folder.aspx shows TfsReports TfsOlapReportsDS TfsReportsDS tfs2010OlapReportDS tfs2010ReportDS

TfsOlapReportDS shows NO dependencies, while tfs2010OlapReportDS shows stacks

Similarly tfsReportDS shows no dep. while tfs2010reportDS shows stacks.

=>  So why does the popup reference TfsOlapReport which does seem to be the correct reference ?

Note: This is from Sharepoint - From Dashboard and you get the same from Excel Reports

Many thanks

nb: Rollup VS10SP1-KB2643415.exe installed on tfs server side

Answer 1

Hi Greg,

Thanks for your post.

For this issue, please try the workaround which Magra provided us in his post: http://social.msdn.microsoft.com/Forums/en-US/tfsgeneral/thread/f86c6a3d-e5ca-4452-bf26-52179e04bb1c/.   

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi

This was previously done and i am doing again. No mention is made in the guide on who the credential owner is, i.e. a highly restricted account or something like "domain users" ?

OK - I folowed the steps, no joy, then continued to go a "generate key" and then the "refresh key"

Now one of the sites is working showing graphs while other is not.

However the working site has the red text at the bottom referenced in thread

http://social.msdn.microsoft.com/Forums/en-US/tfssetup/thread/0f4fc08b-81c4-4986-a459-56c3e32040c5

Will delete site not working and try a manusal tfpt "addproject" to see if this fixes it.

Answer 3

Hi Greg,

Thanks for your post.

The Credential Ownersused to add the users or groups that you wish to give access to your TFS dashboards. For the next Windows User Nameand Password, type the TFS data source account(TfsReport) for SQL Server Reporting Service, please refer to this similar post: http://social.msdn.microsoft.com/Forums/nl/tfssetup/thread/192fb7ec-f3ff-4406-a180-b70c7d0daf39 and this article:  http://blogs.msdn.com/b/team_foundation/archive/2010/03/06/configuring-sharepoint-server-2010-beta-for-dashboard-compatibility-with-tfs-2010-beta2-rc.aspx.

---

John Qiao [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Update: After leaving the explorer session, and re-entering, the "good" site is back to the previous behaviour, e.g.

From another post, it seems this behaviour is related to the credential sore, i.e. after re-doing it things were ok for a session. Then on a new session the problems come back.

Answer 5

Seems like issue is with Secure store service configuration. Adding TfSReports & TfsService account to Target Application Administrator field in Secure store service in SharePoint 2010 would resolve the issue. If any other users are facing the problem add them to Members in Secure store service.

If this does not resolve the problem, this may require some 1:1 troubleshooting via remote connection. In that event, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.

---

Trevor Hancock (Microsoft)
Please remember to "Mark As Answer" the replies that help.

Answer 6

Trevor

Thanks for your reply and the offer of a glimmer of hope.

Cross check. Previously the Target Application Administrator had

TFSSERVICE WSSSERVICE SQLSERVICE 

I changed this to add TFSREPORTS

For the "Members" field we have added OURDOMAIN\Domain Users HOWEVER for some reason the name shows up sometimes as a SID, e.g. c:0+w|s-1-5-21-xxxxxxxxxx-yyyyyyyy

The Field Name & Field Type Screen has the TFSREPORTS account and its password.

I have done a refresh key and rebooted the Sharepoint server. No change.

So just to summerise :

- occasionally we get a site to display correctly, otherwise we get the popup or we get the widget displayed with 2009 dates (i.e. missing data), Excel behaviour is the same, connection details seem correct
- The issue seems to be secure store related.
- we use a 'tfs' target application id
- we have tried NEW TFS projects or to recreate existing projects (use tfpt), both with the same results.
- i have redone the SP "Extensions for Sharepoint products" "grant access
- The Sharepoint server seems to have AD issues finding groups in the domain yet running "adexplorer" on the server has no problem, i.e. the 2008 R2 box knows what domain it is in and how to talk to the AD...

Event log error 7493 :

The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was 'Credentials were not found for the current user within the target application 'tfs'. Please set the credentials for the current user.'. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).

------

Unauthorized attempt to access session by user 0#.w|ourdomain\userme. Session belonged to user 0#.w|ourdomain\wssservice.

I found http://blogs.msdn.com/b/jjameson/archive/2010/05/13/error-7493-access-is-denied-viewing-tfs-dashboard-in-sharepoint-server-2010.aspx so even added the service/reports account to the "members" group as well.
Still no change and the same error.

>> Does the TfsWarehouseDataReader members in the SQL tfs_analysis DB need to include all users as well, or only the accounts talking to it ? (currently has xxxservice/tfsreports)

p.s. just discovered domain AD works fine in server from explorer etc, however SQL / TFS / Sharepoint DO NOT see the AD, they either see none or 5 other sister ADs (not the ourdomain AD).

At this stage with collectively 100+ hours on this issue, i would sell my soul for a solution.

Cheers (check our gold partner access to support - remote desktop)

Answer 7

- My TFS and Sharepoint servers could NOT find the domain AD inside SQL, TFS Admin or Sharepoint.
However all servers found the domain from file explorer (when adding someone to a directories security or by running "adexplorer").

- The tfs server in its capacity as a tfs source and web client DOES accreditate people OK (not sure why), however the TFS - Sharepoint - Excel - SQL - tfs_analysis part, cannot accreditate.

When you go to choose a group inside these services, it either finds no AD, or the 5 sister ADs to "ourdomain" (big company).
However at a later time it showed the correct Ourdomain AD...

Could this be related to the accounts sharepoint and SQL run under ? but it would not explain why other ADs were seen, and not the AD the servers are in.

Note, the domain controller is getting many failures below. The DC is 2003 R2 and the the servers are 2008 R2. The item below was found on the net, i am hoping this is true...

At all times, you seem to get 7493 errors, and get something like below, using data to refresh does not work, however opening in excel sometimes works after the "enable connection". (nb: tfsusrxx means it sees the default widget, not the true data)

>> Does the TfsWarehouseDataReader members in the SQL tfs_analysis DB need to include all users as well, or only the accounts talking to it ? (currently has xxxservice/tfsreports) NOTE: The role tickboxes for this are NOT ticketed.

>> This is a complete mystery and makes no sense, but i hope some bright spark out there says, arr ha, i know what it is !

Answer 8

Are you TFS2008/TFS2010? Seems like you are TFS2008 as you are getting TFSOlapReport error in that popup.

On report site, go to home page :i.e. http://yourtfsserver:xx/reports (if reports ssrs is configured on TFSApp server).

If you are TFS2008, see what credentials are set for access TFSOlapReport (connection) or TFSOlapReport2010.

See if you are using "YourDomain\TFSservice" or similar "YourDomain\TFSReports" kind of account. See if that helps as the error you are getting on the sharepoint site page ... is not able to show report's data (in the form of a chart/report/table) and that access Analysis cube. So, credentials for "TfsOlapReport.." thing should be the same account and that account should ahve access on TFS/SQL end for both databases/analysis cube end. Also, i dont think if you go to the report site and can get "Burn down" report to work, is it?

---

Arun Sangal Software Configuration Management Denver USA

Answer 9

To clarify

TFS 2010 Sp1 on 2008 R2 Sp1, SQL 2008 R2

Sharepoint 2010 Sp1 on 2008 R2, SQL 2008 R2

One of the top posts talks about TfsOlapReport, my research indicates this is just a label for the text in the popup, everyone gets the same message.

Answer 10

Thanks to people who contributed, there has been an outcome, after weeks / months..

I raised a support ticket with Microsoft, after going through 4 lots of MS people at the other end, the solution was the following :

- In Excel Calculation services, set "allow external data" to trusted data collection and libraries" from none
(change made, but no "good" result on bad server, but may have helped after the next step...)

- On the TFS side, add Port 2383 to the Firewall SQL Inbound Rules with port 1433 and 1434

Then it worked...

Now before we say, well thats obvious, note this:
- the sandbox tfs server which was talked to by the same sharepoint worked, it thus had the same "none" setting for "allow external data", and had the same 1433, 1434 inbound exception for SQL
i.e. on paper and visibly, it had the same config as the production TFS server (broken excel)

So while making the "bad" server work maybe explained, why the "good" worked can't...

Anyway, if this helps someone the pain and trouble we went through, then good.

A search on 2383 showed these, not sure if i need to open more ports...

SQL Server Service Broker port 4022
SSAS Default Instance port 2383
SQL Server Browser Service port 2382

Cheers

Greg Roberts

Note to MS - For complex things like Sharepoint / TFS, it would be good if there wasa comprehensive and cohesive guide on how to manually test that each part is working, preferably outside the actual program itself (e.g. try some manual connection to SQL, etc).

Although you do well generally in the setups steps, you fall down in the checks of systems front.
Normally this needs to be found in web articles, not the setup guide itself.