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

From: TimmyG (tim@pracctice.com)
Date: 09/06/02 

From: "TimmyG" <tim@pracctice.com>
Date: Fri, 6 Sep 2002 17:05:39 +0100

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

  • 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)
  • Re: Connecting to a sql server database
    ... You are trying to use integrated security in your app (as is shown in the ... line numbered code where the connection string is shown). ... sql server, hence integrated security fails. ... > causing the error message and what the correction for this. ...
    (microsoft.public.dotnet.general)
  • Re: Active Directory and SQL Server Connection
    ... when you say that you intend to use integrated security, ... creating a WindowsIdentity and calling Impersonate on that. ... > Thanks Sahil, ... so they have access to the sql server. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Please explain loss of token between web server box and sql box
    ... 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. ... > relating to Integrated Security with Asp.Net IIS 5 and Sql Server 2k. ...
    (microsoft.public.dotnet.framework.aspnet.security)