.net windows authentication on sql 2005

Discussion in 'ASP .Net Security' started by ChiWhiteSox, Jul 8, 2008.

  1. ChiWhiteSox

    ChiWhiteSox Guest

    Hi this may be a simple design question, not sure...

    i have a web app that needs to be logged in to our domain and eventually
    will access our sql 2005 which also on the domain using win authentication.

    1. how do u typically setup the users on the sql machine? can we setup
    different DB roles and assign users to that role? we dont want to setup
    individual users on the sql machine. We also dont want to setup a global user
    to be used for all the departments. We need to setup an audit trail per user
    login on the DB.

    2. What connection string would u use now on your web config file?

    3. How would u now write the code to authenticate a user?
    (ex: MYDOMAIN\John Purutong)

    would it be something like:

    connection = SqlConnection("Server=Adiamor;Integrated
    Security=SSPI;Database=Bookstore;");
    connection.Open();
     
    ChiWhiteSox, Jul 8, 2008
    #1
    1. Advertising

  2. ChiWhiteSox

    Joe Kaplan Guest

    There are basically two different security architectures you can use for
    doing this type of application, the trusted subsystem model or the delegate
    model.

    In the trusted subsystem, the front end tier connects to the backend tier
    using a fixed service account. This account has permissions to perform all
    of the actions that any user of the front end tier can do. Security is
    basically not really managed at a granular level on the backend since the
    fixed service account is basically a "super user" of some sort. The front
    end tier is responsible for implementing any authorization logic that grants
    specific permissions to different users. This is why it is called a trusted
    subsystem, as the backend is essentially giving up control over
    authorization policy and is trusting the front end to do that for it.

    In a delegated model, the identity of the authenticated user of the front
    end is used to access services in the backend tier. In this model, granular
    permissions can be applied at the backend and front end is more of a broker
    that translates requests on behalf of the user.

    Either can be made to work and in some cases you can even do both if some
    parts of the app need one approach and other parts need the other. The key
    is deciding which model you want to implement. Neither one is essentially
    better than the other. They both have their own benefits and tradeoffs.

    When using Windows authentication in SQL, the connection string is usually
    the same for both approaches. You basically just use SSPI.

    The key difference with the delegated approach is that the application is
    usually configured to impersonate the authenticated browser user and
    Kerberos delegation is configured in Active Directory such that the web tier
    is granted permissions to delegate the security context of a user it
    authenticated to a remote service. Configuring delegation is what usually
    trips people up since it can be a bit complicated to understand, configure
    and troubleshoot and not that many people really have a strong grasp on it.
    It is definitely possible, but can be frustrating. Note that if SQL runs on
    a remote machine, you have to configure delegation. Enabling impersonation
    isn't adequate to make this work.

    Best of luck!

    Joe K.
    --
    Joe Kaplan-MS MVP Directory Services Programming
    Co-author of "The .NET Developer's Guide to Directory Services Programming"
    http://www.directoryprogramming.net
    --
    "ChiWhiteSox" <> wrote in message
    news:...
    > Hi this may be a simple design question, not sure...
    >
    > i have a web app that needs to be logged in to our domain and eventually
    > will access our sql 2005 which also on the domain using win
    > authentication.
    >
    > 1. how do u typically setup the users on the sql machine? can we setup
    > different DB roles and assign users to that role? we dont want to setup
    > individual users on the sql machine. We also dont want to setup a global
    > user
    > to be used for all the departments. We need to setup an audit trail per
    > user
    > login on the DB.
    >
    > 2. What connection string would u use now on your web config file?
    >
    > 3. How would u now write the code to authenticate a user?
    > (ex: MYDOMAIN\John Purutong)
    >
    > would it be something like:
    >
    > connection = SqlConnection("Server=Adiamor;Integrated
    > Security=SSPI;Database=Bookstore;");
    > connection.Open();
    >
    >
    >
    >
    >
    >
    >
     
    Joe Kaplan, Jul 9, 2008
    #2
    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. Juan T. Llibre
    Replies:
    0
    Views:
    480
    Juan T. Llibre
    Jun 7, 2005
  2. Sql Agentman
    Replies:
    0
    Views:
    671
    Sql Agentman
    Jan 19, 2006
  3. =?Utf-8?B?c2NvdHRybQ==?=

    sql session state for .net v1.1/.net v2.0 and sql server 2005

    =?Utf-8?B?c2NvdHRybQ==?=, Feb 13, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    2,811
    Steven Cheng[MSFT]
    Feb 16, 2006
  4. JM_newsgroups
    Replies:
    2
    Views:
    838
    JM_newsgroups
    Dec 12, 2008
  5. John
    Replies:
    4
    Views:
    563
Loading...

Share This Page