Cannot create a connection to data source 'TemporaryDataSource'........System.Net.Sockets.SocketException: No such host is known

  • Article

Recently, I was puzzled from one of my troubleshooting experience with our premier customer.

The scenario is:

  • We have SharePoint 2013 configured in one Server with SSRS 2012 integrated mode
  • This is one node SharePoint Server, with Central Admin and Front End on the same machine, from the architecture standpoint.
  • Excel Services pointing to the PowerPivot instance installed on a separate machine though
  • We create BISM Data Source, pointing to a tabular mode SSAS Database deployed on the same machine, where PowerPivot instance is running, no error during the Data Source creation.

In other words:

Machine A: SharePoint Central Admin + WFE + SSRS 2012 Integrated Mode + Excel Services

Machine B: PowerPivot Instance for the Excel Services + Tabular Mode SSAS instance.

We have SP1 on the SQL Server 2012.

Interesting is, after some environmental change, when we try to create PowerView report, using the BISM Data Source, we get the following error message:

"An error occurred while loading the model for the item or data source 'EntityDataSource'. Verify that the connection information is correct and that you have permissions to access the data source."

This is the same error that is discussed on the connect bug, and gives some hints on how to get around the error:

https://connect.microsoft.com/SQLServer/feedback/details/715205/you-cannot-modify-the-data-source-of-a-power-view-report

From Verbose log, we verified the error message, found that C2WTS was not started. Once we started this service, we found the error message now changed to:

Unexpected       Throwing Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: , Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: An error occurred while loading the model for the item or data source 'https://<URLToGallary>/bismfilename.bism'. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'TemporaryDataSource' . ---> Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. ---> System.Net.Sockets.SocketException: No such host is known     at System.Net.Sockets....

Subsequently, we took the following steps

  • Made sure the browser service is running, ports (2382 and others) are opened on the firewall for the SharePoint and the SSAS Tabular mode servers.
  • Netmon shows we did not try to connect to browser service or tabular instance port, but I do see the PowerPivot activity on PowerPivot instance port. Also, there was no attempt to connect to the tabular mode instance.
  • Profiler trace does not show activity obviously to tabular mode instance.
  • IP:port or SSASServer:port combination also does not show connection working
  • However, the UDL connection (or VS tabular mode project connection) from SharePoint to the SSAS Server were both successful

All indications were pointing to the fact that something happened when the file was loaded, however, I could not make sense where the error was coming from.

We then found a temporary workaround by using an RSDS file to connect to the BISM file and run a PowerView report using this method. In the process we found that, the RSDS would only work, if we used stored credentials, which led to further investigation in the Kerberos troubleshooting path. In this scenario, the C2WTS service account changed at some point which perhaps broke the original C2WTS Kerberos settings that had been done, although I was informed that, prior to having the issue, we did not have to setup Kerberos. So we started troubleshooting, in the Kerberos path, thinking that constraint delegation is failing for the C2WTS service account.

In this direction, we took the following steps:

  • Looked at the "Delegation" tab for C2WTS Service Account in Active Directory, it is currently set to "Do not trust this user for delegation"
  • We changed the settings to "Trust this user for delegation to specified services only", "Use any authentication protocol", we made sure, the SPN for the Tabular mode SSAS instance is included in the list. This is constraint delegation to SSAS Tabular mode instance.
  • We added the SPNs for the tabular instance, for the service account it is listening on, prior to setting the constraint delegation.

setspn -S MSOLAPSvc.3/<SSASServer:TabularInstanceName> <tabularmode service Account>
setspn -S MSOLAPSvc.3/<SSASServer.FQDN:TabularInstanceName> <tabularmode service Account>

After these changes, the broken PowerView report with BISM data source started working again.

I am puzzled as to how it took the troubleshooting path we had to take to resolve the issue, the error message was sure misleading to point to other potential issues.

Special thanks to my colleague, Josh Gerszewski, who helped me investigate this issue.

 

Happy PowerViewing.....