5

I've written a simple ASP.NET application that works as a frontend for a simple MSSQL database. The application is accessible over the Internet.

There are two physical servers involved: a WS2008R2 Active Directory domain controller which is also running MSQL Server 2008 R2, and another server, the webserver (WS2008R2/IIS7.5) where my application resides.

The Application Pool for my application "FooPool" has its own AD User identity it runs under "FooUser". FooUser does not have any permission to access the SQL Server database, instead only my own personal user account "MyUser" has that permission.

The idea is that attempts to access this web application first perform Windows Authentication with IIS, my web application then uses Impersonation to access the SQL Server database.

However my application does not work.

I tested the application without it touching SQL Server, just to test impersonation, so I did Response.Write( WindowsIdentity.GetCurrent(false).Name ); which correctly shows the application impersonating MyUser and not acting as FooUser. This works from all modern browsers and across the Internet.

But as soon as it touches MSSQL Server I get the error "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." but that should not be happening because no user tokens are for Anonymous Logon.

I've done my homework and read all about Delegation and Impersonation in ASP.NET and I have set up delegation: The FooUser account has a Service Principal Name set-up (I set the SPN to an arbitrary string, is that doing it right?) and is marked for delegation in ADUC.

Finally, my connection string has SSPI enabled, Connection pooling disabled, and the network library set to "dbmssocn".

What else am I forgetting?

Dai
  • 141,631
  • 28
  • 261
  • 374
  • In ADUC, on the webserver's delegation tab, you have selected "Trust this computer for delegation to specified services only?" And then you've added the MSSQLSvc from the SQL Server machine (and the proper port #)? – ethorn10 May 20 '11 at 20:09
  • The webserver's computer account is not configured for delegation, only the user identity that the application pool runs under. The "FooUser" account is set up to "Trust this user for delegation to any service (Kerberos only)". – Dai May 20 '11 at 20:11
  • Not the computer account...the computer itself still needs delegation. This sounds like the double-hop issue. – ethorn10 May 20 '11 at 20:15
  • I was under the impression that you can either do delegation on the application pool identity OR the computer account; that doing both is not necessary. – Dai May 20 '11 at 20:16
  • Ok - so once you selected the delegation for that identity, you then selected the SPN that you created and restarted IIS? – ethorn10 May 20 '11 at 20:28
  • Just restarted W3SVC, no dice. – Dai May 20 '11 at 20:50
  • did you ever get this to work? – eych Jul 27 '12 at 18:00
  • did you ever get this to work? – Sabareesh Kkanan Dec 09 '13 at 19:27
  • I am trying to do exactly same thing but no dice – Sabareesh Kkanan Dec 09 '13 at 19:27

1 Answers1

2

Finishing the Configuration for Delegation to Work you must enable constrained delegation:

  1. Open Active Directory Users and Computers
  2. Find the user account that the IIS Web site is using for the web application pool and double-click it
  3. Select the option: Trust this user for delegation to specified
    services only.
  4. Make sure that the user is constrained to the SPN associated with the MSSQLSvc service
  5. Restart IIS

http://blogs.technet.com/b/askds/archive/2008/11/25/fun-with-the-kerberos-delegation-web-site.aspx

SoftwareCarpenter
  • 3,835
  • 3
  • 25
  • 37
ethorn10
  • 1,889
  • 1
  • 18
  • 29
  • I did try that, but I ended up frying my domain's SPN set-up. I had to get a Microsoft Support incident to repair the damage. I haven't tried to get SPNs to work again (and what's with SPNs anyway? What purpose do they serve that isn't already provided by Windows security?). – Dai Aug 25 '12 at 13:21