.net windows authentication on sql 2005

[ChiWhiteSox]

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]

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.