Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Security > Windows Authentication to SQL Server?

Reply
Thread Tools

Windows Authentication to SQL Server?

 
 
7777
Guest
Posts: n/a
 
      09-09-2009
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.


 
Reply With Quote
 
 
 
 
Joe Kaplan
Guest
Posts: n/a
 
      09-11-2009
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.
>


 
Reply With Quote
 
 
 
 
7777
Guest
Posts: n/a
 
      09-11-2009
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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.
>>

>



 
Reply With Quote
 
Joe Kaplan
Guest
Posts: n/a
 
      09-11-2009
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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.
>>>

>>

>
>


 
Reply With Quote
 
7777
Guest
Posts: n/a
 
      09-11-2009
Will look into this further, thanks again Joe, cheers


"Joe Kaplan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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.
>>>>
>>>

>>
>>

>



 
Reply With Quote
 
7777
Guest
Posts: n/a
 
      09-23-2009
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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.
>>>>
>>>

>>
>>

>



 
Reply With Quote
 
Joe Kaplan
Guest
Posts: n/a
 
      09-23-2009
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" <(E-Mail Removed)> wrote in message
news:%23dVC4P%(E-Mail Removed)...
> 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.
>
>
>


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
Java - Integrated Windows Authentication - NTLM Authentication Forwarding Will Java 5 12-03-2005 01:00 AM
SQL integrated authentication when using forms authentication Brett Smith ASP .Net 2 10-26-2004 02:15 PM
Can't connect to SQL Server, using Windows Authentication users of SQL server? help =?Utf-8?B?UmV6YQ==?= ASP .Net 3 06-07-2004 06:42 PM
Basic Authentication v. Integrated Windows Authentication w/ Delegation Mark ASP .Net 0 01-20-2004 03:13 PM



Advertisments