Re: Please explain loss of token between web server box and sql box

From: Willy Denoyette [MVP] (willy.denoyette@pandora.be)
Date: 09/06/02 

From: "Willy Denoyette [MVP]" <willy.denoyette@pandora.be>
Date: Fri, 6 Sep 2002 18:26:42 +0200

This will only work if you are running in a W2K Active Directory Kerberos realm.

That is :

- Your clients are using IE 5.x or higher with integrated sucurity enabled.
- The client user principal is a W2K AD domain member, the users accounts must be set up as "delegatable" in the AD.
The IIS server's "machine account" is trusted for delegation in the AD.
The SQL server SPN must be registered in the AD, or must run under "localsystem" identity.
Only TCP/IP can be used to access SQL server (see; SQL books online search for SPN).

And if you get this working, keep in mind that by doing this, you did throw away the connection pooling facility offered by the ADO
and the managed SQL prividers, that is; each client will use a separate authenticated connection.

Willy.

"TimmyG" <tim@pracctice.com> wrote in message news:uuvkb8bVCHA.4092@tkmsftngp11...
> Hello all,
>
> I gather this problem has been rolling for sometime now.
>
> All I ask for is a 'be all and end all' answer to the following issues
> relating to Integrated Security with Asp.Net IIS 5 and Sql Server 2k.
>
> The problem arises when endeavouring to use Integrated Security at all
> application levels (IIS, Asp.Net and Sql Server).
>
> I.e. I set up a windows user group for my application (lets keep it simple).
> The IIS site is switched to Integrated Security and the Asp.Net app is set
> to allow users from that group and to impersonate the client account. I also
> grant the group Sql Server access and rights to the relevant database.
>
> What I wanted to achieve with this approach is to lock down all application
> levels with windows security (i.e. no anonymous web server access and no
> mixed security on the sql server).
>
> By chance the first time I tried out this approach I happened to have IIS
> and Sql Server on the same box and it worked superbly (apparently the
> *machine* authenticates the user and the token is retained).
>
> The problem however, occurs when Sql Server and IIS are on two different
> boxes. When this scenario is encountered the client user token is 'lost'
> between the Asp.Net app and the sql server. You are suddenly morphed into
> NT_ANONYMOUS_USER which is of course no use whatsoever when trying to access
> the sql database with integrated security.
>
> From this problem arise several issues:
>
> - Is this how it is supposed to work?
> - If so surely it defeats much of the point of integrated
> security.
> - If so then surely it should NOT work when IIS and Sql are on
> the same box (consistency?)
> - If this is not how it is supposed to work then...
> - Will impersonating a particular account from the Asp.Net
> application work for integrated SQL authentication?
>
> It just seemed to me that Integrated Security was perfectly suited to this
> type of application (indeed I presumed this is what it was aimed at) but
> from my experiments it appeared to 'fall apart' when using distributed
> servers.
>
> Please can someone provide a clear answer to this problem. I would also love
> to know if Integrated Security will work in this manner in the future.
>
> Much obliged,
> TimmyG.
>
>

Relevant Pages

  • Please explain loss of token between web server box and sql box
    ... relating to Integrated Security with Asp.Net IIS 5 and Sql Server 2k. ... The problem arises when endeavouring to use Integrated Security at all ... grant the group Sql Server access and rights to the relevant database. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Integrated Security
    ... That was where I was leaning towards but he said that the SQL server and IIS ... > | page using SqlClient with integrated security. ... > | believe I have all the correct web.config settigs correct ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Please explain loss of token between web server box and sql box
    ... > Only TCP/IP can be used to access SQL server (see; ... > and the managed SQL prividers, that is; each client will use a separate ... >> The problem arises when endeavouring to use Integrated Security at all ... >> grant the group Sql Server access and rights to the relevant database. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Integrated Windows && Anonymous combination
    ... IIS set up with Integrated Windows && Anonymous ... credentials to the SQL Server from IIS. ... When evaluating client credentials, IIS represents our ...
    (microsoft.public.inetserver.iis.security)
  • Integrated Windows && Anonymous combination
    ... >alike, credentials are passed to the SQL Server, ... >credentials to the SQL Server from IIS. ... >When evaluating client credentials, IIS represents our ...
    (microsoft.public.inetserver.iis.security)