Sharepoint 2010: an error occurred during an attempt to establish a connection to the external data source tfsolapreport (cant see excel charts)

Question

I'm getting this error for reports that come up (excel) on the main progress.aspx page for a given TFS 2010 sharepoint portal site..

 

To aid in this, here is my setup:

 

Sharepoint 2010 :  on vsintranet02

Sql 2008 r2:  on Vssqlprod01

TFS2010:  on Vstfs02

Sharepoint portal site, lives off of vsintranet02 at http://tfs02/sitename etc

 

I've installed the dashboard software update on the sharepoint site i believe as well.

I've verified the settings used for the excel reports, that they are set as windows authentication.

Id rather not use SSS and have to change every report for every new TFS site..

I believe the issue is something similar to a double hop authentication issue, thinking maybe a component needs installed on the sql box perhaps, or some other change is needed, one that could be globally set.

 

Any thoughts here?

 

Thanks

 

 

Answer 1

Hi markm75g,

 

What’s error’s content?

 

It’s look like a SharePoint Dashboards data source problem, for this issue please refer to: http://blogs.msdn.com/b/granth/archive/2009/12/03/tfs2010-troubleshooting-sharepoint-dashboards-data-source-problems.aspx.

---

Best Regards

John Qiao

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hi markm75g,

 

What’s error’s content?

 

It’s look like a SharePoint Dashboards data source problem, for this issue please refer to: http://blogs.msdn.com/b/granth/archive/2009/12/03/tfs2010-troubleshooting-sharepoint-dashboards-data-source-problems.aspx .

---

Best Regards

John Qiao

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Welcome to the All-In-One Code Framework ! If you have any feedback, please tell us.

The error is just this on the page itself (where the graph would be)

an error occurred during an attempt to establish a connection to the external data source tfsolapreport

I've looked through that article before.. but this is basically suggesting to go through and change every data connection on every excel report to use SSS, which is rather tedious.

I'm searching for a way to leave everything as is, with windows authentication set, as the default for all new TFS projects (unless there is a way to make all new TFS projects set their xls files to use SSS, without going through each one by one?)

Answer 3

What do you mean by "Id rather not use SSS and have to change every report for every new TFS site." The correct way to configure the portal to avoid double hop issues is to use SSS. Furthermore, you will also need to publish SSAS over HTTP/HTTPS (search for OLAP Pump) in order to avoid having to expose your Analysis database and have to grant each and every user access to said database for them to use Excel Reports in Excel.

---

Leo Vildosola - InCycle Software

Answer 4

Hi markm75g,

 

Has this issue been resolved?

---

Best Regards

John Qiao

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 5

What do you mean by "Id rather not use SSS and have to change every report for every new TFS site." The correct way to configure the portal to avoid double hop issues is to use SSS. Furthermore, you will also need to publish SSAS over HTTP/HTTPS (search for OLAP Pump) in order to avoid having to expose your Analysis database and have to grant each and every user access to said database for them to use Excel Reports in Excel.

---

Leo Vildosola - InCycle Software

The instructions in that link state:

If the connection string is incorrect, then you need to open the Team Foundation Administration Tool on your Application Tier and configure the reporting settings correctly.

The next thing to check is the Single Sign-On configuration.

• Click on the Authentication Settings... button

The SSS radio button should be selected and an SSS ID specified

If an SSS ID (sometimes incorrectly referred to as an SSO ID) is not specified, then you need to open the Team Foundation Administration Tool on your MOSS server and configure an SSS ID.

 

The "authentication settings" button is accessed via the reports themselves.. so to me, this sounds as if i need to go through every report in a tfs portal site (excel files) and change them to SSS, and the corresponding SSS ID (i've already created an SSS ID in the sharepoint side of things).. so how can i avoid having to go through every report and change the authentication mechanism to SSS and the ID?

Maybe i misread all that somehow..

 

Answer 6

Yeah, I can see how this can be misleading. However, I have done configurations where SSS configuration and integration between SharePoint and TFS was done after Team Projects were created and it still works, the SSS gets propagated in the Excel reports from what I experienced.

In case you don't have the information handy, to property configure the integration, you can refer to the details with screen shots in the following post: http://blogs.msdn.com/b/zhengtu/archive/2010/02/03/setup-sss-for-vsts-2010-dashboards.aspx.

To summarize it:

• In SharePoint Central Administration, select Central Administration or Application Administration, then select Manage service applications. Click on Secure Store Service. When the page appears click on New on the toolbar on the top of the page. Complete the information as described in the post using the identity representing TFS Reports. Take note of the string used for the Target Application ID (e.g. tfs). This value will be used in the next part.
• In the server where your SharePoint instance is hosted, launch the Tfs Admin Console, navigate to Extensions for SharePoint Products, select your SharePoint Web Application and click on Modify Access. You will now see the Access for Team Foundation Server dialog. Enter the Enterprise Application Definition (optional) value (e.g. tfs).

This will get you to the point where you can now correctly see the Excel Reports in SharePoint (dashboard) without double hop problems, but it may still fail when launching the reports in Excel. This can happen because there may not be direct access to the analysis database. This is especially true if you publish your sites over the web but can also happen if you use a topology where the SQL Server is behind a few layers of firewalls. You can see this behavior when you inspect the connection string of an Excel Report. For example,

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=SERVER;Location=SERVER;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

By default, every Excel Report points directly to the server hosting the Analysis database. If you don't have direct access to that server and your identity is not allowed in the analysis database then you will not be able to work correctly with these reports.

In this scenario, you will want to use an OLAP Pump, where you configure Analysis Services to exchange data via HTTP/HTTPS. When configured in this manner, and you use the TFS Reports identity to perform the interaction with the Analysis database, you will then be able to use Excel Reports without direct access to the Analysis database. The drawback is that you will have to change the connection string of each Excel Report to the following:

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=https://olap.myserver.com/msmdpump.dll;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

Where Source now points to the pump and Location is removed. Hope this helps more than confuse.

---

Leo Vildosola - InCycle Software

Answer 7

Yeah, I can see how this can be misleading. However, I have done configurations where SSS configuration and integration between SharePoint and TFS was done after Team Projects were created and it still works, the SSS gets propagated in the Excel reports from what I experienced.

In case you don't have the information handy, to property configure the integration, you can refer to the details with screen shots in the following post: http://blogs.msdn.com/b/zhengtu/archive/2010/02/03/setup-sss-for-vsts-2010-dashboards.aspx .

To summarize it:

• In SharePoint Central Administration , select Central Administration or Application Administration , then select Manage service applications . Click on Secure Store Service . When the page appears click on New on the toolbar on the top of the page. Complete the information as described in the post using the identity representing TFS Reports . Take note of the string used for the Target Application ID (e.g. tfs ). This value will be used in the next part.
• In the server where your SharePoint instance is hosted, launch the Tfs Admin Console , navigate to Extensions for SharePoint Products , select your SharePoint Web Application and click on Modify Access . You will now see the Access for Team Foundation Server dialog. Enter the Enterprise Application Definition (optional) value (e.g. tfs ).

This will get you to the point where you can now correctly see the Excel Reports in SharePoint (dashboard) without double hop problems, but it may still fail when launching the reports in Excel. This can happen because there may not be direct access to the analysis database. This is especially true if you publish your sites over the web but can also happen if you use a topology where the SQL Server is behind a few layers of firewalls. You can see this behavior when you inspect the connection string of an Excel Report. For example,

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=SERVER;Location=SERVER ;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

By default, every Excel Report points directly to the server hosting the Analysis database. If you don't have direct access to that server and your identity is not allowed in the analysis database then you will not be able to work correctly with these reports.

In this scenario, you will want to use an OLAP Pump , where you configure Analysis Services to exchange data via HTTP/HTTPS. When configured in this manner, and you use the TFS Reports identity to perform the interaction with the Analysis database, you will then be able to use Excel Reports without direct access to the Analysis database. The drawback is that you will have to change the connection string of each Excel Report to the following:

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=https://olap.myserver.com/msmdpump.dll ;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

Where Source now points to the pump and Location is removed. Hope this helps more than confuse.

---

Leo Vildosola - InCycle Software

I followed this, up to "Enter the Enterprise Application Definition (optional) value (e.g. tfs )."

 

Here i entered "tfs" (this on the sharepoint intranet server in the tfs console)..  i'm not sure if i did something incorrect in setting up the SSS, as even after this, i try to bring up the progress.aspx page and on the page the dashboards still show the error.

I didnt do the steps after this, because i was assuming once the first changes were in place, that at least the dashboard would work.?

 

Answer 8

Yeah, I can see how this can be misleading. However, I have done configurations where SSS configuration and integration between SharePoint and TFS was done after Team Projects were created and it still works, the SSS gets propagated in the Excel reports from what I experienced.

In case you don't have the information handy, to property configure the integration, you can refer to the details with screen shots in the following post: http://blogs.msdn.com/b/zhengtu/archive/2010/02/03/setup-sss-for-vsts-2010-dashboards.aspx .

To summarize it:

• In SharePoint Central Administration , select Central Administration or Application Administration , then select Manage service applications . Click on Secure Store Service . When the page appears click on New on the toolbar on the top of the page. Complete the information as described in the post using the identity representing TFS Reports . Take note of the string used for the Target Application ID (e.g. tfs ). This value will be used in the next part.
• In the server where your SharePoint instance is hosted, launch the Tfs Admin Console , navigate to Extensions for SharePoint Products , select your SharePoint Web Application and click on Modify Access . You will now see the Access for Team Foundation Server dialog. Enter the Enterprise Application Definition (optional) value (e.g. tfs ).

This will get you to the point where you can now correctly see the Excel Reports in SharePoint (dashboard) without double hop problems, but it may still fail when launching the reports in Excel. This can happen because there may not be direct access to the analysis database. This is especially true if you publish your sites over the web but can also happen if you use a topology where the SQL Server is behind a few layers of firewalls. You can see this behavior when you inspect the connection string of an Excel Report. For example,

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=SERVER;Location=SERVER ;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

By default, every Excel Report points directly to the server hosting the Analysis database. If you don't have direct access to that server and your identity is not allowed in the analysis database then you will not be able to work correctly with these reports.

In this scenario, you will want to use an OLAP Pump , where you configure Analysis Services to exchange data via HTTP/HTTPS. When configured in this manner, and you use the TFS Reports identity to perform the interaction with the Analysis database, you will then be able to use Excel Reports without direct access to the Analysis database. The drawback is that you will have to change the connection string of each Excel Report to the following:

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=https://olap.myserver.com/msmdpump.dll ;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

Where Source now points to the pump and Location is removed. Hope this helps more than confuse.

---

Leo Vildosola - InCycle Software

I followed this, up to "Enter the Enterprise Application Definition (optional) value (e.g. tfs )."

 

Here i entered "tfs" (this on the sharepoint intranet server in the tfs console)..  i'm not sure if i did something incorrect in setting up the SSS, as even after this, i try to bring up the progress.aspx page and on the page the dashboards still show the error.

I didnt do the steps after this, because i was assuming once the first changes were in place, that at least the dashboard would work.?

 

Anyone have any ideas if a step was missing here?

 

Thanks

Answer 9

The following link that was referenced no longer works:

http://blogs.msdn.com/b/zhengtu/archive/2010/02/03/setup-sss-for-vsts-2010-dashboards.aspx .

Is anyone able to fill in the steps that the above post used to provide?

Thank you.

Answer 10

Try the following:

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

I also used that post. It contains a section to configuring SSS.

Answer 11

I am experiencing same issue.  I went thru the links and references and made sure my secure store and TFS is setup correctly.  Still, I am getthing the error.

Answer 12

One of the things I see happens often is that the credentials for SSS are not configured properly. Perhaps this may be the culprit. Can you verify this is set correctly? Once the SSS configuration is in the list, did you "Set Credentials"? Take a look a the dropdown that appears when you highlight it in the list in SharePoint and select the Set Credentials option. The account should be your TFSReports account. Also, make sure that everyone that needs access is a member of the SSS configured.

The following link is a general purpose "how to configure SSS". Extract the steps you need to verify. It has good screen captures that may help. Note, this link is not specific to TFS but can be used as a general guideline.

http://fabiangwilliams.wordpress.com/2010/04/16/how-to-create-configure-consume-sharepoint-2010-secure-store-in-business-connectivity-services/

---

Leo Vildosola - InCycle Software

Answer 13

Thanks Leo for the info.  I have checked my Secure Store Service and the credential is set to TFSReports account.  I even re-enter the password.  Still the same.    For testing purpose, I have added the all authenticated users to member.  The results is the same.