Issue Passing Credentials from ASP.NET To SQL Server

Setup:

IIS on Windows 2008 Server R2 Enterprise, SQL Server 2008 R2 Enterprise, ASP.NET 2.0 Web Application.

Purpose:

We are converting the security model of an old web app from using a single SQL Server account to using Active Directory accounts. In the ASP.NET application, we've added <identity impersonate="true" /> as the first item in the <system.web> section of web.config. We've added database roles, which contain Active Directory groups, for granting access to the existing stored procedures. Our web.config already contains <authentication mode="Windows"/>. Here is my connection string, editing out server and DB names:

<add name="DbConn" 
     connectionString="
         Data Source=SERVER;
         Initial Catalog=DBNAME;
         Integrated Security=SSPI;"
     providerName="System.Data.SqlClient"
/>

Problem:

I am getting an error when trying to open an SqlConnection to the database:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Instead of going through all the logical layers of the application (web page to .DLL layers), I threw in a quick, plain web page that does some Response.Write in Page_Load(). I get this (account info edited out):

HttpContext.Current.User.Identity.Name = domain\my account

My.User.Name = domain\my account

Then Page_Load calls a Sub that does some Response.Write and tries to run an existing stored procedure. I get this:

Before opening the DB connection ...

HttpContext.Current.User.Identity.Name = domain\my account

My.User.Name = domain\my account

Sub TestDbCall() Failed! ex.Message = Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

It fails on opening the SqlConnection with error message above (Login failed ...). Clearly, the ASP.NET application recognizes my domain account. It seems that my Windows credentials are not propagating from the ASP.NET app to SQL Server. I'm at a loss as to why.

EDIT:

I found this page on microsoft.com that says in order to access an SQL Server that is not on the IIS server (who would even DO this???), you can't use Windows Authentication in IIS. Rather, you have to use Basic Authentication. It works, but I don't like the idea of users' account nam开发者_如何转开发e and password floating around the corporate network in plain text. So even though it "works", I don't accept my own finding as an answer.

---

When your IIS authenticates the user using Windows Authentication and then, while impersonating the connected user, it connects to any resource outside the local IIS box (like a SQL Server instance) the process is called Delegation, as described in Kerberos Protocol Transition and Constrained Delegation. The process, sometimes referred to informally as 'double hop', is subject of many How-Tos:

• How To: Use Impersonation and Delegation in ASP.NET 2.0
• How to configure an ASP.NET application for a delegation scenario
• Configuring Servers for Delegation (IIS 6.0)
• How to Implement Kerberos Constrained Delegation with SQL Server 2008

---

There is some IIS setup required. See the following document for a good explanation:

How to: Access SQL Server Using Windows Integrated Security

---

I would suggest to change the way you are going to use AD in your app.

I would map AD users to app internal users together with their AD roles mapped to app roles and use app roles to determine access not to stored proc directly but to Business Layer functions