Impersonation and SQL access (again)

From: Al Blake (al_at_blakes.net)
Date: 05/27/04 

Date: Thu, 27 May 2004 18:41:23 +1000

I have read about 10,000 posts on this topic but still can't get it to work
:(
We are designing a web app under ASP.NET VB that connects to a SQL database.
It needs to connect as the AD user currently using the browser (ie windows
authentication), because we already have all the security setup correctly in
SQL and many of views use the currently connected user to detirmine what to
return.

Setup
Web server: Windows 2003 + IIS6 + ASP.NET + Windows Authentication
    web.config contains <identity impersonate="true" />
 Trusted for delegation under AD

SQL Server: Windows 2003 + SQL 2000 + SQL and Windows Authentication

We have created a test aspx page that simply displays the name of the
impersonated user and it correctly returns:

domain\user

which changes dependant on who is accessing the browser, exactly as you
would expect.

The problkem occurs when we then try to connect to a SQL database using a
trusted authentication connection string.
The page fails indicating that it cant connect to the SQL DB as user null.
In other words the IIS process has NOT used the user authenticaitonj info to
connect to SQL.

Why not?
I have been working on this for days and run into a brick wall. I thought
once the IIS process was sicessfully impersonating domain\user it would use
those credentials for the SQL connect. Why doesnt it do this?
Al Blake, Canberra, Australia

Relevant Pages