Re: Windows Authentication to SQL Server?

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" <joseph.e.kaplan@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OE6HyfwMKHA.4124@xxxxxxxxxxxxxxxxxxxxxxx
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" <NoSpam@xxxxxxxxxx> wrote in message
news:e4TJowvMKHA.4124@xxxxxxxxxxxxxxxxxxxxxxx
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" <joseph.e.kaplan@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:eWu0SxqMKHA.508@xxxxxxxxxxxxxxxxxxxxxxx
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" <NoSpam@xxxxxxxxxx> wrote in message
news:%23uJ9EZZMKHA.3384@xxxxxxxxxxxxxxxxxxxxxxx
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.







.

Relevant Pages

  • Re: Login failed for ServerGuest
    ... I think it is not a limitation in Windows 2000. ... access SQL server on Win2000 server by using Windows authenctication if I ... | I have noticed that when I try to log in using Windows Authentication ...
    (microsoft.public.sqlserver.connect)
  • RE: Integrated Authentication (Kerberos) Problem
    ... Verify the SPN for the SQL service account is registered such as the ... >Thread-Topic: Integrated Authentication Problem ... A Windows XP SP1 with IE6 client machine ...
    (microsoft.public.inetserver.iis.security)
  • Re: User authentication
    ... What I want to do is configure scheduled backup. ... However, if possible, I would like to use Windows authentication as opposed ... the backup job in SQL server. ...
    (microsoft.public.sqlserver.clients)
  • Re: How to SELECT records based upon ASP.NET Roles
    ... security principals directly. ... also be able to use SQL row-level security as well (which is something ... using Windows authentication in IIS, ...
    (microsoft.public.dotnet.security)
  • Re: How to SELECT records based upon ASP.NET Roles
    ... security principals directly. ... be able to use SQL row-level security as well (which is something I've never ... When using Windows ... authentication in IIS, this will be a WindowsPrincipal object, but it would ...
    (microsoft.public.dotnet.security)
Quantcast