CODEWITHSUNDEEP
sql-serveriiskerberos
crosan
crosan

Reputation: 486

IIS to SQL Server kerberos auth issues

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.

Upvotes: 4

Views: 7164

Answers (1)

Sukesh Ashok Kumar
Sukesh Ashok Kumar

Reputation: 659

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

Upvotes: 5

Related Questions