Windows Authentication

Discussion in 'ASP .Net' started by Terry Holland, Sep 1, 2009.

  1. Not sure if this question best placed in ASP or SQL group so trying both...

    I have an internal asp.net application that connects to an sql server 2005
    database. We are using Windows authentication. When the SQL database was
    on the same box as the iis server, things work working fine. Now we are
    ready to deploy to live environment where sql database in on seperate box to
    iis server and we are getting authentication errors. It seems that the user
    credentials being passed to sql are for anonymous user and not those of the
    user logged onto client machine.

    I have read info at http://msdn.microsoft.com/en-us/library/ms998292.aspx.
    From this I have set the following in the web.config but this has had no
    effect.

    <configuration>
    <system.web>
    <authentication mode="Windows"/>
    <identity impersonate="true"/>
    ...
    </system.web>
    </configuration>


    Createing a dummy page I am able to see that IIS knows whoe the logged on
    user is but these credentials are not being passed to SQL

    This problem needs to be resolved ASAP so any help would be appreciated

    Regards

    Terry Holland
    Terry Holland, Sep 1, 2009
    #1
    1. Advertising

  2. Terry Holland

    bruce barker Guest

    this by design. windows authentication does not allow forwarding (1 hop
    rule). you will need to switch to kerberos and enable credentials
    forwarding on the servers involved, or switch to basic (which would give
    the iis server a primary token with which to access sqlserver).

    if you don't need to use the users credentials, set impersonate to
    false, and give the pool a domain account with access to sqlserver

    -- bruce (sqlwork.com)

    Terry Holland wrote:
    > Not sure if this question best placed in ASP or SQL group so trying both...
    >
    > I have an internal asp.net application that connects to an sql server 2005
    > database. We are using Windows authentication. When the SQL database was
    > on the same box as the iis server, things work working fine. Now we are
    > ready to deploy to live environment where sql database in on seperate box to
    > iis server and we are getting authentication errors. It seems that the user
    > credentials being passed to sql are for anonymous user and not those of the
    > user logged onto client machine.
    >
    > I have read info at http://msdn.microsoft.com/en-us/library/ms998292.aspx.
    > From this I have set the following in the web.config but this has had no
    > effect.
    >
    > <configuration>
    > <system.web>
    > <authentication mode="Windows"/>
    > <identity impersonate="true"/>
    > ...
    > </system.web>
    > </configuration>
    >
    >
    > Createing a dummy page I am able to see that IIS knows whoe the logged on
    > user is but these credentials are not being passed to SQL
    >
    > This problem needs to be resolved ASAP so any help would be appreciated
    >
    > Regards
    >
    > Terry Holland
    >
    >
    bruce barker, Sep 1, 2009
    #2
    1. Advertising

  3. Thanks for responce bruce.

    could you point me to a good "step by step" source of info for setting up
    kerberos for the scenario I have described. The method I use is not set in
    stone at this point but I would like to know what is involved in going down
    the kerberos route. The client requested object level permissions based on
    ad accounts as the db will be accessed by a number of different
    applications. securing the data at db level ensures that only people who
    have authority to access data can, regardless of the client application they
    use.

    Terry



    "bruce barker" <> wrote in message
    news:<>...

    > this by design. windows authentication does not allow forwarding (1 hop


    > rule). you will need to switch to kerberos and enable credentials


    > forwarding on the servers involved, or switch to basic (which would give


    > the iis server a primary token with which to access sqlserver).


    >


    > if you don't need to use the users credentials, set impersonate to


    > false, and give the pool a domain account with access to sqlserver


    >


    > -- bruce (sqlwork.com)


    >


    > Terry Holland wrote:


    > > Not sure if this question best placed in ASP or SQL group so trying
    > > both...


    > >


    > > I have an internal asp.net application that connects to an sql server
    > > 2005


    > > database. We are using Windows authentication. When the SQL database was


    > > on the same box as the iis server, things work working fine. Now we are


    > > ready to deploy to live environment where sql database in on seperate
    > > box to


    > > iis server and we are getting authentication errors. It seems that the
    > > user


    > > credentials being passed to sql are for anonymous user and not those of
    > > the


    > > user logged onto client machine.


    > >


    > > I have read info at
    > > http://msdn.microsoft.com/en-us/library/ms998292.aspx.


    > > From this I have set the following in the web.config but this has had no


    > > effect.


    > >


    > > <configuration>


    > > <system.web>


    > > <authentication mode="Windows"/>


    > > <identity impersonate="true"/>


    > > ...


    > > </system.web>


    > > </configuration>


    > >


    > >


    > > Createing a dummy page I am able to see that IIS knows whoe the logged
    > > on


    > > user is but these credentials are not being passed to SQL


    > >


    > > This problem needs to be resolved ASAP so any help would be appreciated


    > >


    > > Regards


    > >


    > > Terry Holland


    > >


    > >
    Terry Holland, Sep 2, 2009
    #3
  4. I, for one don't recommend SSPI authentication. Yes, that's not the
    Microsoft POV. However, I think it's dramatically easier to use
    application-specific SQL Server credentials that are managed through custom
    user-authentication. Once the user identity is established, your code can
    choose an appropriate SQL Server role/login account that appropriately
    limits rights. This might mean that you keep a table that maps users with
    the groups to which they belong but there are other approaches. I think it's
    far simpler to configure and it does not require complex (and somewhat
    brittle) Kerberos authentication configurations.

    --
    __________________________________________________________________________
    William R. Vaughn
    President and Founder Beta V Corporation
    Author, Mentor, Dad, Grandpa
    Microsoft MVP
    (425) 556-9205 (Pacific time)
    Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
    http://betav.com http://betav.com/blog/billva
    ____________________________________________________________________________________________



    "Terry Holland" <> wrote in message
    news:#...
    > Thanks for responce bruce.
    >
    > could you point me to a good "step by step" source of info for setting up
    > kerberos for the scenario I have described. The method I use is not set in
    > stone at this point but I would like to know what is involved in going
    > down the kerberos route. The client requested object level permissions
    > based on ad accounts as the db will be accessed by a number of different
    > applications. securing the data at db level ensures that only people who
    > have authority to access data can, regardless of the client application
    > they use.
    >
    > Terry
    >
    >
    >
    > "bruce barker" <> wrote in message
    > news:<>...
    >
    >> this by design. windows authentication does not allow forwarding (1 hop

    >
    >> rule). you will need to switch to kerberos and enable credentials

    >
    >> forwarding on the servers involved, or switch to basic (which would give

    >
    >> the iis server a primary token with which to access sqlserver).

    >
    >>

    >
    >> if you don't need to use the users credentials, set impersonate to

    >
    >> false, and give the pool a domain account with access to sqlserver

    >
    >>

    >
    >> -- bruce (sqlwork.com)

    >
    >>

    >
    >> Terry Holland wrote:

    >
    >> > Not sure if this question best placed in ASP or SQL group so trying
    >> > both...

    >
    >> >

    >
    >> > I have an internal asp.net application that connects to an sql server
    >> > 2005

    >
    >> > database. We are using Windows authentication. When the SQL database
    >> > was

    >
    >> > on the same box as the iis server, things work working fine. Now we are

    >
    >> > ready to deploy to live environment where sql database in on seperate
    >> > box to

    >
    >> > iis server and we are getting authentication errors. It seems that the
    >> > user

    >
    >> > credentials being passed to sql are for anonymous user and not those of
    >> > the

    >
    >> > user logged onto client machine.

    >
    >> >

    >
    >> > I have read info at
    >> > http://msdn.microsoft.com/en-us/library/ms998292.aspx.

    >
    >> > From this I have set the following in the web.config but this has had
    >> > no

    >
    >> > effect.

    >
    >> >

    >
    >> > <configuration>

    >
    >> > <system.web>

    >
    >> > <authentication mode="Windows"/>

    >
    >> > <identity impersonate="true"/>

    >
    >> > ...

    >
    >> > </system.web>

    >
    >> > </configuration>

    >
    >> >

    >
    >> >

    >
    >> > Createing a dummy page I am able to see that IIS knows whoe the logged
    >> > on

    >
    >> > user is but these credentials are not being passed to SQL

    >
    >> >

    >
    >> > This problem needs to be resolved ASAP so any help would be appreciated

    >
    >> >

    >
    >> > Regards

    >
    >> >

    >
    >> > Terry Holland

    >
    >> >

    >
    >> >

    >
    >
    William Vaughn \(MVP\), Sep 2, 2009
    #4
  5. Terry Holland

    bruce barker Guest

    http://www.microsoft.com/windowsserver2003/technologies/security/kerberos/default.mspx

    -- bruce (sqlwork.com)

    Terry Holland wrote:
    > Thanks for responce bruce.
    >
    > could you point me to a good "step by step" source of info for setting up
    > kerberos for the scenario I have described. The method I use is not set in
    > stone at this point but I would like to know what is involved in going down
    > the kerberos route. The client requested object level permissions based on
    > ad accounts as the db will be accessed by a number of different
    > applications. securing the data at db level ensures that only people who
    > have authority to access data can, regardless of the client application they
    > use.
    >
    > Terry
    >
    >
    >
    > "bruce barker" <> wrote in message
    > news:<>...
    >
    >> this by design. windows authentication does not allow forwarding (1 hop

    >
    >> rule). you will need to switch to kerberos and enable credentials

    >
    >> forwarding on the servers involved, or switch to basic (which would give

    >
    >> the iis server a primary token with which to access sqlserver).

    >
    >
    >> if you don't need to use the users credentials, set impersonate to

    >
    >> false, and give the pool a domain account with access to sqlserver

    >
    >
    >> -- bruce (sqlwork.com)

    >
    >
    >> Terry Holland wrote:

    >
    >>> Not sure if this question best placed in ASP or SQL group so trying
    >>> both...

    >
    >
    >>> I have an internal asp.net application that connects to an sql server
    >>> 2005

    >
    >>> database. We are using Windows authentication. When the SQL database was

    >
    >>> on the same box as the iis server, things work working fine. Now we are

    >
    >>> ready to deploy to live environment where sql database in on seperate
    >>> box to

    >
    >>> iis server and we are getting authentication errors. It seems that the
    >>> user

    >
    >>> credentials being passed to sql are for anonymous user and not those of
    >>> the

    >
    >>> user logged onto client machine.

    >
    >
    >>> I have read info at
    >>> http://msdn.microsoft.com/en-us/library/ms998292.aspx.

    >
    >>> From this I have set the following in the web.config but this has had no

    >
    >>> effect.

    >
    >
    >>> <configuration>

    >
    >>> <system.web>

    >
    >>> <authentication mode="Windows"/>

    >
    >>> <identity impersonate="true"/>

    >
    >>> ...

    >
    >>> </system.web>

    >
    >>> </configuration>

    >
    >
    >
    >>> Createing a dummy page I am able to see that IIS knows whoe the logged
    >>> on

    >
    >>> user is but these credentials are not being passed to SQL

    >
    >
    >>> This problem needs to be resolved ASAP so any help would be appreciated

    >
    >
    >>> Regards

    >
    >
    >>> Terry Holland

    >
    >
    >
    >
    bruce barker, Sep 3, 2009
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mark
    Replies:
    0
    Views:
    675
  2. Will
    Replies:
    5
    Views:
    2,611
  3. Dadi
    Replies:
    2
    Views:
    183
    Scott Scott
    Sep 16, 2003
  4. Fabio Gouw

    ASP.NET Authentication and Windows Authentication

    Fabio Gouw, Nov 15, 2004, in forum: ASP .Net Security
    Replies:
    2
    Views:
    142
    Ken Schaefer
    Nov 16, 2004
  5. jfer
    Replies:
    3
    Views:
    552
    Dominick Baier [DevelopMentor]
    Sep 16, 2005
Loading...

Share This Page