0

So here I am again dealing with probably the number one support question on IIS, SPNs. I am not a novice when it comes to this, having lived the pains of getting SSRS front ends to delegate for SQL and SSAS back-ends in a number of different environments over the last few years.

But Microsoft has now upped the ante with a redesigned IIS7 interface and Windows 2008 with kernel mode, and I'm right back there pulling all-nighters and pounding my head on the desk.

In the past I have successfully used Brian Booth's DelegConfig tool to quickly get to the bottom of issues, but in my current case I'm getting results that I don't know that I trust and I'm questioning if the tool is accurately portraying issues, or just wasting my time with issues that don't exist.

So, maybe now its time to get a second (and third and fourth) opinion.

From a high-level I have a web server that is going to run applications that need to act as delegates for access to SQL and SSAS back-end instances. Sounds simple enough on the surface.

The good news is the back-end instances are already set up and working. Those are named instances, running on domain service accounts, with the appropriate SPNs already in place.

The front end server looks like this:

O/S: Windows Server 2008 R2 Enterprise IIS: 7.5.7600.16385

Machine account

  • NetbiosName: COLOVWEB
  • ServerFQDN: colovweb.co.local

Using host headers

  • IISSiteName: CONET
  • IISSiteNameFQDN: conet.co.local

Application pool

  • PoolName: CONET
  • Identity: NETWORK SERVICE

The web site [CONET] hosts ~50 'Applications' and ~20 'Virtual Directories'. There is one (1) application of interest for this exercise, lets call it 'MyApplication'. The DelegConfig application also lives in this web site for now.

At the top level [CONET] 'Anonymous Authentication' and 'Windows Authentication {kernelModeAuthentication = true}' are enabled.

At the MyApplication level 'Windows Authentication {kernelModeAuthentication = true}' is enabled. The same is true for DelegConfig.

Its my understanding that the servers domain computer account [CO\COLOVWEB$] needs the following:

ServicePrincipalName:

  • HTTP/conet.co.local
  • HTTP/conet

msDS-AllowedToDelegateTo:

  • MSOLAPSvc.3/colosql3.co.local:MyInstance
  • MSOLAPSvc.3/colosql3:MyInstance
  • MSOLAPDisco.3/colosql3.co.local
  • MSOLAPDisco.3/colosql3
  • MSSQLSvc/colosql3.co.local
  • MSSQLSvc/colosql3
  • MSSQLSvc/colosql3.co.local:MyInstance
  • MSSQLSvc/colosql3:MyInstance

My understanding is that, while not required in this case, the following is also appropriate:

ServicePrincipalName:

  • HTTP/colovweb.co.local
  • HTTP/colovweb

And everything should work, right?

Well, it doesn't. Depending on the exact syntax I use for the front end host name DelegConfig complains about:

The domain or workstation membership of NETWORK SERVICE (http://conet$) could not be determined.

OR

Service account NETWORK SERVICE (COLOVWEB$) is not a domain account. 

I know I'm fighting a problem with Kerberos delegation but I'm not sure if DelegConfig is helping or hurting. Answers to either appreciated.

EDIT - 20130403

The application ConnectString has been verified and is shown below:

Data Source=colosql3\MyInstance;Catalog=OurCatalog;Integrated Security=SSPI;SSPI=negotiate;Impersonation Level=Delegate;SspropInitAppName=MyApplication

The login hitting the OLAP server is still CO\COLOVWEB$ and not the account of the user.

I run elmah on the server in the root web so I get a good capture of the users session:

AUTH_TYPE negotiate
AUTH_USER CO\user
HTTP_AUTHORIZATION Negotiate YIIKMwYGKwYBBQ....

So, the client browser is clearly submitting the appropriate request.

5
  • So what do you see when you do setspn -L CO\AccountThatRunsYourAppPool
    – Ryan Ries
    Mar 26, 2013 at 23:55
  • Its the machine account so its the various machine account SPN's plus those listed above.
    – tlum
    Mar 27, 2013 at 1:33
  • I'm narrowing this down and I don't know for sure yet but its looking like DelegConfig may have issues when host headers are used, so its results may be a false negative. And, checking with AppDev; its possible connection string is missing SSPI=Kerberos, since I think NTLM is used instead. Never checked in to source control so I'm flying blind right now.
    – tlum
    Mar 27, 2013 at 1:38
  • The connection strings in the application have been verified and they are appropriate. This does not appear to be the source of the issue. I'm going to add the to the question detail.
    – tlum
    Apr 4, 2013 at 1:18
  • I have also same kind of situation. Only difference is that my back-end server is IIS server. Have you find solution? My feeling is that problem is somewhere in front-end server before impersonation, but i haven't located it. Have you use klist.exe to check tickets? There is also tool "Kerberos Authentication Tester (v 0.9.2 beta). And yes, i know this is not an answer...
    – user171302
    Apr 26, 2013 at 9:53

1 Answer 1

0

Finally, this turned out to be a problem with the application. Legacy ASP applications would use impersonation by default. However, ASP.NET disables impersonation by default. Its not enough to set impersonation in an ASP.NET data source, its also imperative that the developer explicitly enable impersonation. This can be done through the web.config

<identity impersonate="true" />

although it can be controlled at a number of different levels within a site so its important for developers to evaluate the security requirements and make the change in the appropriate place.

For example, when impersonation was enabled at the site level the SSAS connection started working... and just about everything else broke, because most other things had been written to not use impersonation, so you can easily end up with contention in your security model... and it didn't help the Microsoft pushed IE9 the night before which broke a bunch of stuff that was obviously blamed on this change until someone figured it out.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .