Windows Authentication to SQL Server?

Discussion in 'ASP .Net Security' started by 7777, Sep 9, 2009.

  1. 7777

    7777 Guest

    Hello, a little foggy on this but are both methods of Impersonation /
    Delegation vs. Trusted Subsystem from the following link of
    http://msdn.microsoft.com/en-us/library/ms998292.aspx are both Windows based
    authentication to SQL Server? Kind of had the impression that Impersonation
    was more like a bunch of basic usernames/passwords stored in a user db table
    that all shared 1 Windows authentication account? Ultimately we'd like to
    go with individual Windows Authentication accounts for all the users for
    high granular auditing/security in still utilizing Windows security and
    wasn't quite sure if the Impersonation/Delegation is able to do this.
    Thanks in advance.
     
    7777, Sep 9, 2009
    #1
    1. Advertising

  2. 7777

    Joe Kaplan Guest

    Yes, both Windows authentication.

    In delegation, authentication to SQL server is done as the user
    authenticated by the front end application (typically a web app using
    integrated auth), allowing you to implement very granular security on
    individual Windows users in SQL itself.

    Trusted sub system is basically the practice of access SQL as a fixed
    service account. In SQL, this can be done with either Windows security or
    SQL security but the article is discussing Windows security. Trusted sub
    system is typically easier to configure (delegation can be quite tricky) and
    may perform better in some use cases but forced all authorization logic to
    be implemented at the front tier. This is sometimes a good thing and
    sometimes a bad thing, depending on how you want to use SQL.

    It is also fair to say that delegation and trusted sub system are
    architectural styles for implementing authentication and authorization in
    distributed applications and the principals can be applied without using
    Windows security as long as other protocols that support these principals
    exist. Trusted sub system is typically easy to set up while delegation
    tends to be much harder to implement effectively without Windows security.
    Technology enabled by protocols like WS-Trust/WS-Federation and Geneva
    server will begin to change all that, at least for applications and services
    that can implement those protocols instead of only Kerberos/negotiate auth
    in Windows. Alas, as far as I know SQL is not there yet.

    --
    Joe Kaplan-MS MVP Directory Services Programming
    Co-author of "The .NET Developer's Guide to Directory Services Programming"
    http://www.directoryprogramming.net
    "7777" <> wrote in message
    news:%...
    > Hello, a little foggy on this but are both methods of Impersonation /
    > Delegation vs. Trusted Subsystem from the following link of
    > http://msdn.microsoft.com/en-us/library/ms998292.aspx are both Windows
    > based authentication to SQL Server? Kind of had the impression that
    > Impersonation was more like a bunch of basic usernames/passwords stored in
    > a user db table that all shared 1 Windows authentication account?
    > Ultimately we'd like to go with individual Windows Authentication accounts
    > for all the users for high granular auditing/security in still utilizing
    > Windows security and wasn't quite sure if the Impersonation/Delegation is
    > able to do this. Thanks in advance.
    >
     
    Joe Kaplan, Sep 11, 2009
    #2
    1. Advertising

  3. 7777

    7777 Guest

    Thanks for your informative reply Joe, much appreciated. So if the asp.net
    app and SQL Server were to be set up in the Delegation method would there be
    individual Windows user accounts listed in SQL Server -> Security -> Logins
    section (which each corresponding database username would be mapped to their
    individual Windows accounts) in that when SQL Profiler is run in the
    background while users are using the asp.net app each of their individual
    Windows accounts would show up in dispalying fine detailed auditing of which
    user did what to the db data?



    "Joe Kaplan" <> wrote in message
    news:...
    > Yes, both Windows authentication.
    >
    > In delegation, authentication to SQL server is done as the user
    > authenticated by the front end application (typically a web app using
    > integrated auth), allowing you to implement very granular security on
    > individual Windows users in SQL itself.
    >
    > Trusted sub system is basically the practice of access SQL as a fixed
    > service account. In SQL, this can be done with either Windows security or
    > SQL security but the article is discussing Windows security. Trusted sub
    > system is typically easier to configure (delegation can be quite tricky)
    > and may perform better in some use cases but forced all authorization
    > logic to be implemented at the front tier. This is sometimes a good thing
    > and sometimes a bad thing, depending on how you want to use SQL.
    >
    > It is also fair to say that delegation and trusted sub system are
    > architectural styles for implementing authentication and authorization in
    > distributed applications and the principals can be applied without using
    > Windows security as long as other protocols that support these principals
    > exist. Trusted sub system is typically easy to set up while delegation
    > tends to be much harder to implement effectively without Windows security.
    > Technology enabled by protocols like WS-Trust/WS-Federation and Geneva
    > server will begin to change all that, at least for applications and
    > services that can implement those protocols instead of only
    > Kerberos/negotiate auth in Windows. Alas, as far as I know SQL is not
    > there yet.
    >
    > --
    > Joe Kaplan-MS MVP Directory Services Programming
    > Co-author of "The .NET Developer's Guide to Directory Services
    > Programming"
    > http://www.directoryprogramming.net
    > "7777" <> wrote in message
    > news:%...
    >> Hello, a little foggy on this but are both methods of Impersonation /
    >> Delegation vs. Trusted Subsystem from the following link of
    >> http://msdn.microsoft.com/en-us/library/ms998292.aspx are both Windows
    >> based authentication to SQL Server? Kind of had the impression that
    >> Impersonation was more like a bunch of basic usernames/passwords stored
    >> in a user db table that all shared 1 Windows authentication account?
    >> Ultimately we'd like to go with individual Windows Authentication
    >> accounts for all the users for high granular auditing/security in still
    >> utilizing Windows security and wasn't quite sure if the
    >> Impersonation/Delegation is able to do this. Thanks in advance.
    >>

    >
     
    7777, Sep 11, 2009
    #3
  4. 7777

    Joe Kaplan Guest

    I'm not much of a SQL guy so I'm not totally sure on all the details
    required to get the logons configured in SQL and what your options are. It
    seems like you should be able to do both individual user accounts and also
    groups as well. Typically, you'd want a role mapping scheme where windows
    groups end up getting mapped to SQL roles for the actual security
    implementation so that you have a nice abstraction layer in SQL for managing
    the actual permissions (roles) and can use AD and groups as a way of
    assigning membership to those roles. Depending on the number of users
    involved, working with individual accounts may be fine for you. I'd
    definitely ask for more details in a SQL group. :)

    You should definitely see the individual Windows logons for the users
    connecting to the database in profilers or other logs. That's kind of the
    whole point. :)

    --
    Joe Kaplan-MS MVP Directory Services Programming
    Co-author of "The .NET Developer's Guide to Directory Services Programming"
    http://www.directoryprogramming.net
    "7777" <> wrote in message
    news:...
    > Thanks for your informative reply Joe, much appreciated. So if the
    > asp.net app and SQL Server were to be set up in the Delegation method
    > would there be individual Windows user accounts listed in SQL Server ->
    > Security -> Logins section (which each corresponding database username
    > would be mapped to their individual Windows accounts) in that when SQL
    > Profiler is run in the background while users are using the asp.net app
    > each of their individual Windows accounts would show up in dispalying fine
    > detailed auditing of which user did what to the db data?
    >
    >
    >
    > "Joe Kaplan" <> wrote in message
    > news:...
    >> Yes, both Windows authentication.
    >>
    >> In delegation, authentication to SQL server is done as the user
    >> authenticated by the front end application (typically a web app using
    >> integrated auth), allowing you to implement very granular security on
    >> individual Windows users in SQL itself.
    >>
    >> Trusted sub system is basically the practice of access SQL as a fixed
    >> service account. In SQL, this can be done with either Windows security
    >> or SQL security but the article is discussing Windows security. Trusted
    >> sub system is typically easier to configure (delegation can be quite
    >> tricky) and may perform better in some use cases but forced all
    >> authorization logic to be implemented at the front tier. This is
    >> sometimes a good thing and sometimes a bad thing, depending on how you
    >> want to use SQL.
    >>
    >> It is also fair to say that delegation and trusted sub system are
    >> architectural styles for implementing authentication and authorization in
    >> distributed applications and the principals can be applied without using
    >> Windows security as long as other protocols that support these principals
    >> exist. Trusted sub system is typically easy to set up while delegation
    >> tends to be much harder to implement effectively without Windows
    >> security. Technology enabled by protocols like WS-Trust/WS-Federation and
    >> Geneva server will begin to change all that, at least for applications
    >> and services that can implement those protocols instead of only
    >> Kerberos/negotiate auth in Windows. Alas, as far as I know SQL is not
    >> there yet.
    >>
    >> --
    >> Joe Kaplan-MS MVP Directory Services Programming
    >> Co-author of "The .NET Developer's Guide to Directory Services
    >> Programming"
    >> http://www.directoryprogramming.net
    >> "7777" <> wrote in message
    >> news:%...
    >>> Hello, a little foggy on this but are both methods of Impersonation /
    >>> Delegation vs. Trusted Subsystem from the following link of
    >>> http://msdn.microsoft.com/en-us/library/ms998292.aspx are both Windows
    >>> based authentication to SQL Server? Kind of had the impression that
    >>> Impersonation was more like a bunch of basic usernames/passwords stored
    >>> in a user db table that all shared 1 Windows authentication account?
    >>> Ultimately we'd like to go with individual Windows Authentication
    >>> accounts for all the users for high granular auditing/security in still
    >>> utilizing Windows security and wasn't quite sure if the
    >>> Impersonation/Delegation is able to do this. Thanks in advance.
    >>>

    >>

    >
    >
     
    Joe Kaplan, Sep 11, 2009
    #4
  5. 7777

    7777 Guest

    Will look into this further, thanks again Joe, cheers :)


    "Joe Kaplan" <> wrote in message
    news:...
    > I'm not much of a SQL guy so I'm not totally sure on all the details
    > required to get the logons configured in SQL and what your options are.
    > It seems like you should be able to do both individual user accounts and
    > also groups as well. Typically, you'd want a role mapping scheme where
    > windows groups end up getting mapped to SQL roles for the actual security
    > implementation so that you have a nice abstraction layer in SQL for
    > managing the actual permissions (roles) and can use AD and groups as a way
    > of assigning membership to those roles. Depending on the number of users
    > involved, working with individual accounts may be fine for you. I'd
    > definitely ask for more details in a SQL group. :)
    >
    > You should definitely see the individual Windows logons for the users
    > connecting to the database in profilers or other logs. That's kind of the
    > whole point. :)
    >
    > --
    > Joe Kaplan-MS MVP Directory Services Programming
    > Co-author of "The .NET Developer's Guide to Directory Services
    > Programming"
    > http://www.directoryprogramming.net
    > "7777" <> wrote in message
    > news:...
    >> Thanks for your informative reply Joe, much appreciated. So if the
    >> asp.net app and SQL Server were to be set up in the Delegation method
    >> would there be individual Windows user accounts listed in SQL Server ->
    >> Security -> Logins section (which each corresponding database username
    >> would be mapped to their individual Windows accounts) in that when SQL
    >> Profiler is run in the background while users are using the asp.net app
    >> each of their individual Windows accounts would show up in dispalying
    >> fine detailed auditing of which user did what to the db data?
    >>
    >>
    >>
    >> "Joe Kaplan" <> wrote in message
    >> news:...
    >>> Yes, both Windows authentication.
    >>>
    >>> In delegation, authentication to SQL server is done as the user
    >>> authenticated by the front end application (typically a web app using
    >>> integrated auth), allowing you to implement very granular security on
    >>> individual Windows users in SQL itself.
    >>>
    >>> Trusted sub system is basically the practice of access SQL as a fixed
    >>> service account. In SQL, this can be done with either Windows security
    >>> or SQL security but the article is discussing Windows security. Trusted
    >>> sub system is typically easier to configure (delegation can be quite
    >>> tricky) and may perform better in some use cases but forced all
    >>> authorization logic to be implemented at the front tier. This is
    >>> sometimes a good thing and sometimes a bad thing, depending on how you
    >>> want to use SQL.
    >>>
    >>> It is also fair to say that delegation and trusted sub system are
    >>> architectural styles for implementing authentication and authorization
    >>> in distributed applications and the principals can be applied without
    >>> using Windows security as long as other protocols that support these
    >>> principals exist. Trusted sub system is typically easy to set up while
    >>> delegation tends to be much harder to implement effectively without
    >>> Windows security. Technology enabled by protocols like
    >>> WS-Trust/WS-Federation and Geneva server will begin to change all that,
    >>> at least for applications and services that can implement those
    >>> protocols instead of only Kerberos/negotiate auth in Windows. Alas, as
    >>> far as I know SQL is not there yet.
    >>>
    >>> --
    >>> Joe Kaplan-MS MVP Directory Services Programming
    >>> Co-author of "The .NET Developer's Guide to Directory Services
    >>> Programming"
    >>> http://www.directoryprogramming.net
    >>> "7777" <> wrote in message
    >>> news:%...
    >>>> Hello, a little foggy on this but are both methods of Impersonation /
    >>>> Delegation vs. Trusted Subsystem from the following link of
    >>>> http://msdn.microsoft.com/en-us/library/ms998292.aspx are both Windows
    >>>> based authentication to SQL Server? Kind of had the impression that
    >>>> Impersonation was more like a bunch of basic usernames/passwords stored
    >>>> in a user db table that all shared 1 Windows authentication account?
    >>>> Ultimately we'd like to go with individual Windows Authentication
    >>>> accounts for all the users for high granular auditing/security in still
    >>>> utilizing Windows security and wasn't quite sure if the
    >>>> Impersonation/Delegation is able to do this. Thanks in advance.
    >>>>
    >>>

    >>
    >>

    >
     
    7777, Sep 11, 2009
    #5
  6. 7777

    7777 Guest

    Would it be accurate in saying that it would be like comparing apples and
    oranges in trying to convert an asp.net app which uses forms authentication
    mode throughout the app with one SQL login account into SQL Server versus
    converting the asp.net app into a full individual Windows authentication
    mode into SQL Server? Ultimate goal would be to see the individual windows
    accounts activity in sql server's trace profiler tool.



    "Joe Kaplan" <> wrote in message
    news:...
    > I'm not much of a SQL guy so I'm not totally sure on all the details
    > required to get the logons configured in SQL and what your options are.
    > It seems like you should be able to do both individual user accounts and
    > also groups as well. Typically, you'd want a role mapping scheme where
    > windows groups end up getting mapped to SQL roles for the actual security
    > implementation so that you have a nice abstraction layer in SQL for
    > managing the actual permissions (roles) and can use AD and groups as a way
    > of assigning membership to those roles. Depending on the number of users
    > involved, working with individual accounts may be fine for you. I'd
    > definitely ask for more details in a SQL group. :)
    >
    > You should definitely see the individual Windows logons for the users
    > connecting to the database in profilers or other logs. That's kind of the
    > whole point. :)
    >
    > --
    > Joe Kaplan-MS MVP Directory Services Programming
    > Co-author of "The .NET Developer's Guide to Directory Services
    > Programming"
    > http://www.directoryprogramming.net
    > "7777" <> wrote in message
    > news:...
    >> Thanks for your informative reply Joe, much appreciated. So if the
    >> asp.net app and SQL Server were to be set up in the Delegation method
    >> would there be individual Windows user accounts listed in SQL Server ->
    >> Security -> Logins section (which each corresponding database username
    >> would be mapped to their individual Windows accounts) in that when SQL
    >> Profiler is run in the background while users are using the asp.net app
    >> each of their individual Windows accounts would show up in dispalying
    >> fine detailed auditing of which user did what to the db data?
    >>
    >>
    >>
    >> "Joe Kaplan" <> wrote in message
    >> news:...
    >>> Yes, both Windows authentication.
    >>>
    >>> In delegation, authentication to SQL server is done as the user
    >>> authenticated by the front end application (typically a web app using
    >>> integrated auth), allowing you to implement very granular security on
    >>> individual Windows users in SQL itself.
    >>>
    >>> Trusted sub system is basically the practice of access SQL as a fixed
    >>> service account. In SQL, this can be done with either Windows security
    >>> or SQL security but the article is discussing Windows security. Trusted
    >>> sub system is typically easier to configure (delegation can be quite
    >>> tricky) and may perform better in some use cases but forced all
    >>> authorization logic to be implemented at the front tier. This is
    >>> sometimes a good thing and sometimes a bad thing, depending on how you
    >>> want to use SQL.
    >>>
    >>> It is also fair to say that delegation and trusted sub system are
    >>> architectural styles for implementing authentication and authorization
    >>> in distributed applications and the principals can be applied without
    >>> using Windows security as long as other protocols that support these
    >>> principals exist. Trusted sub system is typically easy to set up while
    >>> delegation tends to be much harder to implement effectively without
    >>> Windows security. Technology enabled by protocols like
    >>> WS-Trust/WS-Federation and Geneva server will begin to change all that,
    >>> at least for applications and services that can implement those
    >>> protocols instead of only Kerberos/negotiate auth in Windows. Alas, as
    >>> far as I know SQL is not there yet.
    >>>
    >>> --
    >>> Joe Kaplan-MS MVP Directory Services Programming
    >>> Co-author of "The .NET Developer's Guide to Directory Services
    >>> Programming"
    >>> http://www.directoryprogramming.net
    >>> "7777" <> wrote in message
    >>> news:%...
    >>>> Hello, a little foggy on this but are both methods of Impersonation /
    >>>> Delegation vs. Trusted Subsystem from the following link of
    >>>> http://msdn.microsoft.com/en-us/library/ms998292.aspx are both Windows
    >>>> based authentication to SQL Server? Kind of had the impression that
    >>>> Impersonation was more like a bunch of basic usernames/passwords stored
    >>>> in a user db table that all shared 1 Windows authentication account?
    >>>> Ultimately we'd like to go with individual Windows Authentication
    >>>> accounts for all the users for high granular auditing/security in still
    >>>> utilizing Windows security and wasn't quite sure if the
    >>>> Impersonation/Delegation is able to do this. Thanks in advance.
    >>>>
    >>>

    >>
    >>

    >
     
    7777, Sep 23, 2009
    #6
  7. 7777

    Joe Kaplan Guest

    It would be atypical to use forms auth in the web app and then attempt to do
    some sort of delegation via Windows auth to SQL. It is possible through the
    magic of protocol transition logon, but that seems less like something you
    would probably actually do.

    Are you planning to use forms auth with the app or currently using that? Do
    you need more info on protocol transition? In the context of forms auth,
    are the users being authenticated against AD using LDAP to implement the
    forms auth or are they going against a different store? If AD, why not just
    use Windows auth instead? If a different store, who exactly are you
    impersonating? Are there shadow accounts between AD and the other auth
    store?

    --
    Joe Kaplan-MS MVP Directory Services Programming
    Co-author of "The .NET Developer's Guide to Directory Services Programming"
    http://www.directoryprogramming.net
    "7777" <> wrote in message
    news:%23dVC4P%...
    > Would it be accurate in saying that it would be like comparing apples and
    > oranges in trying to convert an asp.net app which uses forms
    > authentication mode throughout the app with one SQL login account into SQL
    > Server versus converting the asp.net app into a full individual Windows
    > authentication mode into SQL Server? Ultimate goal would be to see the
    > individual windows accounts activity in sql server's trace profiler tool.
    >
    >
    >
     
    Joe Kaplan, Sep 23, 2009
    #7
    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. Lior Amar
    Replies:
    2
    Views:
    690
    Lior Amar
    Aug 27, 2003
  2. Mark
    Replies:
    5
    Views:
    4,345
    Dimitre
    Apr 14, 2008
  3. =?Utf-8?B?UmV6YQ==?=
    Replies:
    3
    Views:
    17,983
    Carlos Barini
    Jun 7, 2004
  4. mcollier
    Replies:
    6
    Views:
    4,199
    Vikram Vamshi
    Feb 24, 2005
  5. Nils Magnus Englund

    Windows authentication from ASP.NET to SQL Server

    Nils Magnus Englund, Aug 8, 2005, in forum: ASP .Net
    Replies:
    8
    Views:
    11,949
    Paul Clement
    Aug 16, 2005
Loading...

Share This Page