ASP.NET > SqlServer; trust and delegation

Question

we have a system where they client wants integrated Windows authentication. This is an ASP.NET 3.5 application, connecting to Sql Server 2005. The web server is Server 2003 R2 SP2. The db server is Server 2003 SP2 (not R2).

In the db server, I ran the following script

exec sp_grantlogin 'myDomain\myUserGroup'
USE myDbName
exec sp_grantdbaccess 'myDomain\myUserGroup'

I have 3 users in the Windows user group 'myDomain\myUserGroup' right now. All three users' accounts are marked as trusted for delegation. The web server account in ADs is marked trusted for delegation.

The web application is marked as using Windows authentication (all others turned off). The web.config has the following lines:

<authentication mode="Windows" ></authentication>
<identity impersonate="true" />
<authorization>
    <deny users="?"/>
</authorization>

Yet when i try to connect to the web application with a user which is in the user group, i get the error:

System.Data.SqlClient.SqlException: 
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

My connection string is being built from a Sql ConnectionStringBuilder constructed as such:

ConnectionStringBuilder.DataSource = "MYDBSERVER"
ConnectionStringBuilder.InitialCatalog = "MYDBCATALOG"
ConnectionStringBuilder.IntegratedSecurity = True

If i HARD CODE one of the allowed accounts to impersonate on the web.config <identity /> line it works. But if i take off the hard coded account and try to pass the identity from the client's machine. I get the error.

So it seems that i don't have something configured correctly for the multi-hop integrated login scenario, but i can't figure out what.

Thanks in advance!

Answer 1

The ASP machine has authenticated the user connecting to IIS via NTLM/Kerberos. The authentication is guaranteed by the domain controller that has asked the original user process (IE) to present a secret that guarantees his identity: his password he typed when he logged into the box. The authentication is actually not done by the processes involved, but by the Local Security Authority (LSA, aka. lsass.exe) on each machine involved. Because the LSA on the ASP machine knows that the authentication is OK, it will allow an impersonation of the remote user to access anything it has the rights to access under the control of the said LSA (in other words, everything on the local ASP machine).

As soon as the ASP process that impersonates the user makes another hop to a new machine, it has left the realm controlled by the LSA on the ASP machine. The LSA on the SQL machine has no reason to trust the LSA on the ASP machine. So it ask it to present a proof that it is who it claims it is (the impersonated user). The ASP machine unfortunately cannot present such a proof, since it does not has the user secret (its password).

The work around is something called 'constrained delegation'. Through constrained delegation the domain controller intervenes in the negotiation between the SQL's machine LSA and the ASP machine LSA and says 'the ASP machine is OK, I vouch for him'. So the SQL's machine LSA trust the authentication and authenticate the original, impersonated user.

The technical details how to set up constrained delegation are described in How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0

Note that this is true anytime a 'double hop' and impersonation is involved, no matter the type of resources involved (can be a SQL server, can be a file share, can be a new back end ASP service).

Answer 2

If you're using Windows authentication then Impersonation doesn't flow past the ASP.NET process itself. You have two options here - swap to Basic Authentication, where Identity flows or, if you're running on Win2003 or later, you can use Kerberos and some hackery to impersonate when you connect