using windows authentication to log in to a sql server

Question

I have a web application set up on our intranet that uses windows authentication (successfully) that I am trying to link to a database running on a separate server.

The problem that I am having however, is that when I try to use a trusted connection to log in to the sql server, instead of passing the domain/username of the person using the website the application is passing NT AUTHORITY\ANONYMOUS LOGON.

I checked that the authentication was working by having the website display a greeting that does identify my username correctly when I log on to the site, so it isn't an anonymous access issue. I think it is an impersonation issue somehow despite the fact that set in the web.config file for the application.

Curiously, the web application works fine when I run it on either my dev machine or even locally through remote desktop on the production server. I'm not sure what's going on here.

Finally, I'm running iis 7.5, windows server 2008 r2 and sql server 2008

Answer 1

it is a known behavior called double-hop issue. See http://weblogs.asp.net/owscott/archive/2008/08/22/iis-windows-authentication-and-the-double-hop-issue.aspx

Answer 2

If your web application tries to access your database it does it with the account under which the IIS w3wp.exe process is running (networkservice by default). If you want to change this, you will need to use impersonation.

This document explains it pretty good.

As the document also explains depending on your application, you might want to use trusted subsystem instead of impersonation.

Answer 3

This is called Constrained Delegation. Basically what that means is that an impersonated context by default cannot be delegated to authenticate with a resource on the network. If constrained delegation would not be in place anyone could create a web site in the enterprise and expose some benign application. But underneath, once the user authenticated with the site, it could impersonate that user to do anything, like read his mail, sale stock on his behalf, give raises to site developer, *anything. This is why impersonated contexts are not trusted outside the machine that impersonated the user.

To allow an impersonated context to connect to a remote resource (like a file share, or a database server) the domain administrator has to explicitly set up Constrained Delegation, which allows the impersonated context to authenticate with one specific resource.

There are numerous articles describing the problem and the solution:

• How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0
• Protocol Transition with Constrained Delegation Technical Supplement
• How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
• Configuring Servers for Delegation (IIS 6.0)
• How to: Use Protocol Transition for Impersonating and Delegating the Original Caller in WCF