Very Computer

Hi. Hope one of you gurus out there can help me.

If possible, I want to keep the generic web user (IUSR_machine) out of
our SQL Server. I also want to leverage our SQL Server integrated
permissions structure for intranet users accessing SQL Server via the
web, since those users already have usernames and passwords in the NT
domain. Otherwise I have to reinvent the wheel and build and maintain a
whole separate permission structure for intranet users, using SQL Server
standard security.

I have an ASP app running on IIS 4. It connects with SQL Server running
on a different machine in the same domain. The web server is the PDC
and the db server is a BDC. Anonymous and NT Challenge/Response
authentication are enabled on IIS. The folders on the web server where
the asp app lives are intentionally off-limits to IUSR_machinename,
because I want IE to either use the user's active NT logon (if any), or
else prompt the iser for his/her NT logon info to proceed. That much
works fine: If the user is not logged onto the NT domain when he/she
surfs to the directory, the browser displays a prompt, the web server
accepts the logon, and then allows access.

Unfortunately, the next piece doesn't work. Somehow the NT logon info
for
the user is not being passed successfully over to SQL Server, since it
rejects the logins every time. (*Direct* trusted connections by the same
users to SQL Server work fine.)  If I set the DSN on the web server to
use SQL standard security, and have the web page query the user for a
standard username and password - or else allow IUSR_machine into SQL as
a guest under standard security - the connection works fine, so there is
probably no fundamental problem with connectivity in the asp. I have
also tried using "DSN-less" connections and that doesn't seem to help.

I'm pretty sure the problem lies between the web server and SQL Server,
but I don't know how to fix it. Any ideas?

Thanks in advance!!

- Tony

Tony Scilipoti
****************
Jamaica Plain, Massachusetts

How are you making the connection to SQL Server?  

Named Pipes or TCP/IP Sockets.  

If you call MSoft, they will most likely suggest you setup the SQL
connection to utilize standard security and TCP/IP Sockets and don't
utilize trusted connections - it works with IIS 4.0 and SQL 6.x with
anonymous or NT Challenge/Response.  

You should be able to configure your global.asa file as either a system DSN
or DSN-less if you utilize Sockets.

With NT Challenge/Response, your clients will have to be IE 3.x and above.

I believe Microsoft's preferred way of doing this is to host your data
access components in MTS. The component runs under a specific username; I
use MTSUser, but it doesn't matter, as long as it belongs to the
MTS$Trusted$Impersonators group, which has permissions on SQL Server (using
NT Integrated or Mixed security). Users have (or not, as the case may be)
access rights to the component, via MTS role-based security, which simply
maps standard NT user accounts and/or groups to MTS roles.

It works very well for me.

--
Kevin Klasman
The Taylor Group