4

We have a 3rd party product that allows some of our users to manipulate data in a database (on what we'll call SvrSQL) via a website on a separate server (SvrWeb).

On SvrWeb, we have a specific, non-default website setup for this application so instead of going to http://SvrWeb.company.com to get to the website we use http://application.company.com which resolves to SvrWeb and the host headers resolve to the correct website.

There is also a specific application pool set up for this site which uses an Active Directory account identity we'll call "company\SrvWeb_iis". We're setup to allow delegation on this account and to allow it to impersonate another login which we want it to do. (we want this account to pass along the AD credentials of the person signed into the website to SQL Server instead of a service account.

We also set up the SPNs for the SrvWeb_iis account via the following command: setspn -A HTTP/SrvWeb.company.com SrvWeb_iis

The website pulls up, but the section of the website that makes the call to the database returns the message: Cannot execute database query. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I thought we had the SPN information set up correctly, but when I check the security event log on SrvWeb I see entries of my logging in, but it seems to be using NTLM and not kerberos:

Logon Type: 3
Logon Process:  NtLmSsp 
Authentication Package: NTLM

Any ideas or articles that cover this setup in detail would be extremely appreciated!

If it helps, we are using SQL Server 2005, and both the web and SQL servers are Windows 2003.

crosan
  • 486
  • 4
  • 13
  • Have you setup Integrated Windows Authentication for the web application? – Klaus Byskov Pedersen Jan 06 '11 at 19:00
  • Yessir, it's only using Integrated Authentication. Let me clarify, it is using Integrated Auth for the website, I don't think the application pool gives you an option to change this does it? – crosan Jan 06 '11 at 20:01
  • i wish i knew more about our setup here. – DForck42 Jan 06 '11 at 20:39
  • Yeah, it's been kind of a challenge. I've set up linked servers that required SPN setup after the SQL Installation, but IIS paththrough is another beast for me. On the upside we may have found the issue, I'll post the fix if/once we've confirmed. – crosan Jan 07 '11 at 16:40
  • Did you setup your SPN on SQL Server properly? – Harvey Kwok Jan 11 '11 at 03:13
  • Yes, it appears the SPN for the IIS Application Pool identity was setup incorrectly. We were mistakenly specifying port 80 in the setspn, we needed to remove this and point it to the host header address (instead of the server name) and it works now. – crosan Jan 11 '11 at 16:11

1 Answers1

5

There are several possible reasons for kerberos failures which includes lack of SPN and duplicate SPN as well.

If SQL is running under custom account you would need to add SPN for SQL as well.
Also keep in mind, you should be adding SPN for the FQDN which is the host (A) entry in DNS and not a CNAME.

Check the value of NTAuthenticationProviders
http://support.microsoft.com/kb/215383

Try DelegConfig which would show what is missing if its SPN or something else.
http://www.iis.net/community/default.aspx?tabid=34&g=6&i=1887

Sukesh Ashok Kumar
  • 639
  • 5
  • 7