[thelist] Pass Through authentication

[Ken Schaefer]

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org [mailto:thelist-
> bounces at lists.evolt.org] On Behalf Of Brian Delaney
> Subject: [thelist] Pass Through authentication
> 
> This has proven to be tough one:
> 
> Scenario:
> 
> SQL 2000, IIS Web Server  running asp/javascript/html application.
> Goal: Have the user hit the Intranet web site and with their existing
> credential in MS Active Directory be able to access the SQL data.
> 
> Looking for different ideas as how to best handle this.



The generally recommended solution to this is to use Kerberos and Delegation.
I'm writing up a Howto on this right at the moment, but here are the various
things you need to configure:

a) Website must be in IE's Intranet security zone. So, if you are using a
FQDN or IP address to access the website, you need to manually add that
address to IE's Intranet security zone. You can do this via a Group Policy
Object (GPO) if you're in a domain and need to apply this setting across a
large number of machines

b) "Use Integrated Windows Auth (Requires a restart)" must be checked in
Tools -> Internet options -> Advanced in IE. It is by default unless it was a
original a Windows 2000 machine with IE5 that was upgraded to IE6

c) Integrated Windows Auth must be checked for the files/folders on IIS. You
need to ensure that IIS is sending the WWW-Authenticate: Negotiate header to
the clients. The clients must be IE - no other browser currently (as far as
I'm aware) supports Kerberos auth.

d) Clients must be able to contact a DC to obtain a TGT and Service ticket.
This is why Kerberos Auth generally doesn't work in an internet scenario. But
you did say this was an intranet

e) You need to be in a Windows 2000 or Windows 2003 domain (not NT), however
you can be in a "mixed mode" domain - you don't need to be in Windows 2000 or
Windows 2003 native mode

f) The computer account for the IIS server must be marked as "trusted for
delegation" in Active Directory. If you're in a Windows 2003 Server domain
you can optionally choose constrained delegation in that same dialogue box
and specify that the IIS box can only delegate to the SQL Server box

f) The user account(s) in question must not be marked as "sensitive and not
trusted for delegation" in Active Directory

g) If you are connecting to an SQL Server named instance that is running on
an arbitrary port *and* there is no SPN (Service Principal Name) configured
for that instance, you will need to use the SetSPN tool to register a new
SPN. However, SPNs are generally created for you when you install SQL Server,
so you shouldn't have to worry about this. If you are running the IIS web app
pool under a custom identity, you will need to register a HTTP SPN under the
custom user account in question.


One word of warning - if you are allowing credentials to flow to the backend
SQL Server, you do get granular control over access to various objects in the
server. However you will lose all benefits of connection pooling, as each
user will have their own connection pool. If you have a look at Chapter 12 of
"Building Secure .NET Applications", which covers data access security, you
will see what options you have available to you:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/htm
l/SecNetch12.asp?frame=true You can download that whole book as a PDF, or buy
it in bookstores.

Cheers
Ken