Sql-server – IIS 7.5 web application failing with NT Authority\Anonymous Logon

application-poolsiis-7.5kerberossql serverwindows-authentication

I am finding various google results, but none seem to fix my problem.

I am setting up a new WINDOWS 2008 R2 box at work that is to communicate with an existing SQL 2012 box via web tools running in IIS 7.5 within our intranet. We are to use windows authentication through out – IE -> Web Server -> SQL. We are using Kerberos. When viewing the tool locally on the web server, everything is fine, but once I try to view it on a remote client, I get the "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" error.

Let me break down how we have the web site –
Application pool running .NET 2.0 in classic mode with an identity of ApplicationPoolIdentity
Windows authentication is enabled with Extended Protection set to Off, Enable Kernel-mode authentication is checked, and the enabled Providers are (in order) Negotiate and NTLM. ASP.NET Impersonation is enabled set to impersonate as the authenticated user.

SQL Connection String in the following format:

Data Source=THESQLBOXNAME;Initial Catalog=DATABASENAME;Integrated Security=True

I have a test page which I have placed on the web server (following the above mentioned settings) that displays the following data:

HttpContext.Current.User.Identity.IsAuthenticated is true

HttpContext.Current.User.Identity.Name is the expected user (user launching the browser)

System.Security.Principal.WindowsIdentity.GetCurrent.Name is the expected user

I attempt a basic sql query to the sql box and get the login error mentioned above.

I have checked AD and verified that the web box has delegation set – "Trust this computer for delegation to specified services only / Use Kerberos only"

I have run this test page on an existing WINDOWS 2008 R2 box running IIS 7.5 (with same above mentioned settings) and I get no error whatsoever.

I have checked the SPN settings for both web boxes and they are the same (with the exception of the machine's name):

setspn -L EXISTINGBOX

WSMAN/EXISTINGBOX.domain.com
WSMAN/EXISTINGBOX
TERMSRV/EXISTINGBOX.domain.com
TERMSRV/EXISTINGBOX
HOST/EXISTINGBOX.domain.com
HOST/EXISTINGBOX
RestrictedKrbHost/EXISTINGBOX.domain.com
RestrictedKrbHost/EXISTINGBOX

setspn -L NEWBOX
WSMAN/NEWBOX.domain.com
WSMAN/NEWBOX
TERMSRV/NEWBOX.domain.com
TERMSRV/NEWBOX
HOST/NEWBOX.domain.com
HOST/NEWBOX
RestrictedKrbHost/NEWBOX.domain.com
RestrictedKrbHost/NEWBOX

I realize that it is acting like the double-hop problem, but the fact that it works on another box, makes me think it is something specific with the new web box. What the heck am I missing?????

Best Answer

The last two things I can think of is to check of you have the MSSQLSvc SPNs registered under the SQL server service account registered (which you may have already since you have a working scenario). Just in case:

  • MSSQLSvc\NetBIOS
  • MSSQLSvc\NetBIOS:1433
  • MSSQLSvc\FQDN.domain.com
  • MSSQLSvc\FQDN.domain.com:1433

If that is done, then going back to the AD tab where you have the trust option, add the SQL server account as one of the allowed service. If sone correctly, you should see the MSSQLSvc* in the list.

If the above methods do not work, then you'll have to maybe enable Keberos tracing or use a network trace to find Kerberos errors.