Kerberos Delegated SSAS Receive Connection Timeout Error

Question

I have a situation where clients connecting to my web service (which exist on another server) must access SQL Server databases and SSAS servers.

It must use the credentials of the client that invokes the service when accessing SQL Server and SSAS cubes.

For this I do

var winId = HttpContext.Current.User.Identity as WindowsIdentity; var ctx = winId.Impersonate (); // Access to the database / SSAS ctx.Undo ();

in my service, which works great when accessing SQL Server databases.

However, when I access SSAS servers, I get "The connection was either lost or was lost."

There are several posts, for example http://denglishbi.wordpress.com/2009/03/31/windows-server-2008-kerberos-bug-%E2%80%93-transport-connection-issues-with-ssas-data/ http://sqlblogcasts.com/blogs/drjohn/archive/2009/03/28/kerberos-kills-large-mdx-queries-on-windows-server-2008-ssas.aspx

but I use Windows Server 2008 R2 where my service lives, so this should not be a problem, because this error should be fixed by Microsoft.

Any information on how best to diagnose this problem would be appreciated.

To clarify that SSAS servers have an SPN. It actually worked at some point, but now it has stopped. There are no signs of duplicate SPNs or anything else.

Interestingly, it works intermittently on one SSAS server, but it seems to work all the time for another. Both of them called the SPNs mentioned in this document.

https://support.pyramidanalytics.com/entries/22056243-Configuring-Kerberos-Delegation-for-Named-Instances-of-SSAS-with-Active-Directory-and-additional-pro

Answer 1

My production environment represents a balanced load (and under heavy load) in a very large corporate Active Directory network. The following has done a lot of testing to finally collapse the settings that work.

• I also run on Windows 2008 Server R2
• My web services are in ASP.NET in IIS. For authentication, I have included "Windows Auth" and "impersonation of ASP.NET". Kernel mode is disabled, and the provider "Negotiate: Kerboros"
• SPNS and Trusted Deletion are configured for the AD account. My AD account looks like sys_myservice (sys_ is just my company’s naming convention)
• Application Pool Id set to use sys_myservice
• After making all the changes to dev env, restart the entire server. For some odd reason, this is always necessary when we launch new servers and configure them.

With this setting, my web services access SSAS, SQL Server, etc., which use Windows Kerboros authentication, and all queries are executed correctly under user credentials.

The difference in my setup from your system is the impersonation of ASP.NET. At IIS level it is included. I was unable to impersonate the code that you are trying to do. If you get code-level impersonation to work with your workflow, I would be very interested to see how you publish the update.

Forgot to mention. My services are in an MVC application, I apply a global filter to all Action methods to force the application to authenticate all connections.

public static void RegisterGlobalFilters(GlobalFilterCollection filters) { filters.Add(new HandleErrorAttribute()); filters.Add(new System.Web.Mvc.AuthorizeAttribute()); } 

and in the web.config system.web section

 <authentication mode="Windows" /> <identity impersonate="true" /> 

Answer 2

I agree that the intermittently “successful” instance of SSAS is suspicious. I wonder if he really uses Kerberos all the time. A combination of negotiation / NTLM and Kerberos can be used, with one auth method actually working and the other unsuccessful. It might be worth taking another look at SPN. This link may help: http://msdn.microsoft.com/en-us/library/dn194200.aspx

Answer 3

Have you tried using Wireshark or any other network analysis tools to see something red happening at this point of failure? This is probably better if you provide more troubleshooting options from your end.

In addition, do your web services have a load balancer?

Regards, Sashi.