SharePoint Adventures : “The connection either timed out or was lost” with RS DataSource to SSAS

A customer had encountered an issue with their SharePoint 2010 / Reporting Services 2012 deployment.  They had setup a Data Source for Reporting Services that was setup to connect to a stand alone Analysis Services instance.  When they clicked on “Test Connection” they saw the following:

 

Within the SharePoint ULS Log, we saw the following – which was really the same error:

Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'AdventureWorksAS2012.rsds'. ---> Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The connection either timed out or was lost. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host

at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) --- End of inner exception stack trace ---

When I saw this error, I did not attribute the error to an authentication issue as this usually indicates a network related issue.  I was actually able to reproduce the issue in my local environment.  Once I had it reproduced I grabbed an Analysis Services Profiler trace and saw the following.

The minute I saw that, my mindset shifted to an authentication issue and I was pretty sure this was Kerberos related – which based on our deployment of SharePoint 2010 and RS 2012 this also equated to a Claims/Kerberos issue.  Some people think that because we are a Claims aware service now, that Kerberos isn’t needed any longer.  What you will see below is that Kerberos is definitely in play and contributes to the issue.

So, I started with a Network trace using Network Monitor 3.4.  After I collected the trace, I just filtered with the KeberosV5 protocol and applied that.  Here is what I saw:

There were actually two things going on here.

1. I was missing the MSOLAPSvc.3/BSPegasus SPN
2. The Claims Service Account did not have Constrained Delegation setup to allow delegation to the OLAP Service.

I added my MSOLAP SPN’s.  In this case it was requesting the NETBIOS name, so I added both:

What surprised me on this was that I didn’t see any PRINCIPAL_UNKNOWN errors here.  Just the KDC_ERR_BADOPTION.  In the past, I usually ignored BADOPTION errors and sometimes it can be red herring.  The key here is the number.  The BADOPTIONS I typically ignored had a 5 code with it.  These had 13.  Of note, this BADOPTION was because of Item 2 above – lack of Constrained Delegation configured within Active Directory.

The thing to remember about this deployment is that this is going to be Claims to start.  This means that we will be using the Claims to Windows Token Services (C2WTS).  There will be a Windows Service on the server that is affected and it will have an associated Service Account.  In my case, my service account is BATTLESTAR\claimsservice.  After adding the SPN, I allowed the the Constrained Delegation option to the MSOLAP service.  This is done on the Delegation tab for the service account in question.  If you are using LocalSystem for the C2WTS service account, it would be on the machine account for the server that the C2WTS service is running on.

NOTE: In order to see the Delegation tab in Active Directory, an SPN needs to be on that account. However, there is no SPN needed for the Claims Service Account. In my case, I just added a bogus SPN to get it to show. The SPN I added isn’t used for anything other than to get the Delegation tab to show.

After I had that in place, I did an IISReset to flush and cache for that logged in session and ran a Network Trace again – because I got the same error.

You can notice that the BADOPTION is not present after the MSOLAP TGS request.  That’s because what we did  corrected that one.  However, now we see a BADOPTION after the TGS request for the RSService.  This is something I ran into a few months back that a lot of people either aren’t aware of, or the configuration is so confusing that it is just missed.  Even though you setup the Claims Service with Delegation settings, the actual Shared Service that we are using, also needs these delegation settings.  In this case it would be Reporting Services.  So, we have to repeat what we did for the Claims Service with the Reporting Services account.

NOTE: In this configuration, the Reporting Services Service Account will not have any SPN’s on it as they are not needed (unless you are sharing it with something else). So, we’ll need to add a bogus SPN on the RS Service Account to get the Delegation tab to show up.

In my case, I’m sharing my RSService account with a native mode service, so I actually have an HTTP SPN on the account and the Delegation tab is available. 

NOTE: Because the Claims Service has forced Constrained Delegation because of the need for Protocol transitioning, the RS Service MUST use Constrained Delegation. You can’t go from more secure to less secure. It will fail.

Now lets look at the network trace with these changes.

You can see that we got a successful response on the 4th line without getting the BADOPTION.  We still see one more BADOPTION, but I didn’t concern myself with it, because…

I was now working!!!

 

Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton

Comments

• Anonymous
  March 18, 2015
  i spend a day to configure a kerberos for my RS DataSource to SSAS. this post it great!

• Anonymous
  June 29, 2017
  I get a similar error when I attempt to do this across a two way trust forest.Now, I WAS getting the errors in the primary domain with it's SharePoint Site but following this has fixed it.But I cannot seem to translate this to when I do this on a farm in a trusted domain attempting to use the EXACT same data source.Feel free to email me if you need very detailed information.s