Search code examples
asp.netsql-serverwindows-authenticationkerberosintegrated-security

asp.net via kerberos integrated windows authentication to sql server

Please, can someone PLEASE give a simple, straight-forward way to enable ASP.NET > Kerberos > Sql Server?

We have clientMachine > webServer > databaseServer. The client is insistent that the site must pull the windows login and not prompt, hence the need for Kerberos and Integrated Authentication. It also MUST impersonate the user onto the db server, inducing a double-hop.

Our domain is a Windows 2003 domain, which as far as I can tell online, means Kerberos is enabled. And on a domain-joined computer,when logged in, kerbtray shows me I have a slew of tickets, so it is apparently working.

The web and database server computer accounts in AD are both "trusted for delegation".
The AD user accounts which must access the system are both "trusted for delegation".
When it is all working, I will add more users, for now two is it.

The Sql Server instance is running under LocalSystem on the database server, which as near as i can tell online, means it does NOT require messing with those SPN things at all.

Yet, when i try to log in with either user, i get

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

indicating a double-hop failure. This is furthered by the fact that in the application log on the db server there are a slew of entries from "MSSQL" saying the same thing.

I really can't believe how hard this is... i mean, IIS, SQL and Windows are all Microsoft, can't they talk better???

So in summary, i have

  • a kerberos domain,
  • db and web server both trusted for delegation
  • users trusted for delegation
  • users in a AD group
  • AD group as a login in sql (and user in db)
  • IIS with Anonymous off, Integrated on, Basic and Digest off
  • IE with Enable Integrated on

Solution

  • The answer ultimately DID lie in the SPNs.

    This system was set up on our test site, which is test.ourcompany.com, so i needed to add a new SPN for that reference. So in addition to the following: 

    HTTP/server 
HTTP/server.ourdomain.com

    I also needed to add an additional one for: 

    HTTP/test.ourcompany.com

    Geez, what a battle! At least it's over! :)
    Thanks for the help guys!