ASP.Net web application trying to use Impersonation and Delegation to connect to SQL Server

Question

I'm trying to use Impersonation and Delegation in an intranet ASP.Net web-app in order to pass authenticated users' credentials onto a SQL Server.

The web server and SQL server are two separate machines, but in the same domain, so Delegation is required.

I've done the following:

• set <authentication mode="Windows"/> and <identity impersonate="true"/> in my web-app's web.config.
• enabled Constrained Delegation from the web server to the MSSQLSvc service on the SQL Server, in Active Directory.
• enabled only Windows Authentication in the website, through IIS.

Apparently this should all work, but it doesn't (the SQL Server is denying access to the anonymous user - "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'").

In IIS7, the Application Pool is set to use Integrated Pipleline Mode and is running with the NetworkService Identity. The website only has Windows Authentication enabled, Extended Protection is Off, Kernel-mode authentication is enabled, and NTLM is the provider.

All the web pages I've read seem to indicate that my setup should work. What am I missing?

Solution

I've discovered the answer:

The Windows Authentication provider in IIS7 must be set to Negotiate:Kerberos, not NTLM. This means that the Kernel-mode authentication setting must be disabled. This seems to be fine. I think I'm right in saying that Kernel-mode authentication is required when using a custom identity, i.e. one specific identity. Delegation can use an arbitrary number of identities. So all is well.

I've written a blog post about this too, which goes into a bit more detail.

Comments

[Ethan Reesor]

This doesn't actually answer his question

[vapcguy]

@FireLizzard, uh, I gave all the steps required to "use Impersonation and Delegation in an intranet ASP.Net web-app in order to pass authenticated users' credentials onto a SQL Server". This requires 1) a svc account, 2) rights in the Local Security Policy (described above) 3) rights in SQL (dbo - described above), 3) add account to App Pool identity, 4) set Web Site in IIS to Windows authentication, 5) set conn string in web.config, 6) set authentication tag and identity in web.config, 7) add connection string into code, 8) use impersonation code at MSDN link if it doesn't work.

[vapcguy]

Different environments are going to have different rights, as far as accounts go, and he may or may not have privileges to give the account the rights it needs. Without knowing his environment, it is hard to give the specifics of exactly what will work. He will need to try giving the svc acct the rights I specified, and set up the web site and web.config. Maybe it will work without the MSDN impersonation code, maybe not. For my implementation I had just completed when I posted these steps, I needed it. For one I've done recently using Kerberos, SPNs, and a svc acct, I did not.

[Ethan Reesor]

How is this passing the user's credentials to SQL? Your web.config snippet has the app impersonating the service account, not the user.

[vapcguy]

That's because, as I wrote, I identified that you don't want to send the user's credentials to the DB, but use a service account with the access. Ideally, you would take that person's user name and have a log-in routine for that site to begin with, or they shouldn't be on it. Or simply rely on Windows authentication if you don't care about restricting to individual users, but want to allow all authenticated.

[Ethan Reesor]

So, since your solution isn't using the user's credentials to authenticate the SQL session, you're not answering the question. You're telling Graham what you think he should do instead of telling him how to do what he wants to do.

[vapcguy]

Ok, granted. But I believe in using best practices, and his way would mean having to allow everyone in the organization SQL access, and having to create accounts in SQL for each person - or at a minimum, create a security group and add them all into it. This could be hundreds, if not thousands, of people in some organizations. I believe if there's a better way to do something, we ought to share that, instead.

[Peter Wright]

Did you have any issues with users not getting their credentials forwarded to the SQL server when using a browser other than Internet Explorer? I've found that the once they have an established session in IE then the website can delegate their credentials if they switch to a different browser.

[Doreen]

@Peter - I know this is older post but what you describe is the problem I am trying to solve. How can I get Chrome to delegate credentials without opening the app in IE first? I posted a question about it earlier this week: stackoverflow.com/questions/56135670/…