Windows authentication not making it past first machine

[Doug]

The Setup
---------------
Machine A: Windows 2000 Workstation
Machine B: Windows 2000 Server running IIS 5.0
Machine C: Windows 2000 Server running SQL Server 2000

* User is logged into Machine A with userid/password.
* All machines are networked on a domain.
* Due to security requirements, we have removed the "ASPNET" user
account.
* Therefore, we had to add "<identity impersonate="true"></identity>"
in the web.config file.
* Using a System DSN.

The Problem
------------------
Using Windows Authentication, "A" hits "B" and is authenticated. When
IIS ("B") attempts to query data from SQL Server ("C"), we get the
following error:

ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login
failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.

We have verified (using the Request object) that "B" is getting the
credentials. "C" is not and we can't figure out why.

Most people, it seems, rely on SQL Authentication, but our first choice
(for security reasons) is to rely on passthrough ("Windows")
authentication.

Is this a documented bug or are we doing something wrong?

If I need to provide more info, please ask. Thanks.

P.S. Oh, and if we physically sit at the server and run the code, it
works fine.

 

[bruce barker \(sqlwork.com\)]

this is a security feature of nt known as the one hop rule. ntlm creditals
are good only one hop. you can switch to kerberos security which was
designed to support passing credentials from machine to machine. this will
require using active directory, and enabling creditrals forwarding (off by
default) on the servers. you could also switch to basic authenication but
its not secure unless you use https

security design explained:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/PAGExplained0001.asp

kerberos setup:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT05.asp


-- bruce (sqlwork.com)