How do I configure IIS so that the user's domain credentials are used when connecting to SQL server?

Question

We've recently released the latest version of our intranet application, which now uses windows authentication as standard, and needs to be able to connect to a configured SQL server with the end-user's domain credentials.

Lately we've found that on a couple of customer deployments, although IIS can see the user's domain credentials, it will not pass these on to SQL server. Instead, it seems to use the anonymous account. This is in spite of following all the correct steps (changing the directory security to Win Auth, updating Web.Config to use Win Auth and denying anonymous users).

I've been doing a lot of reading that suggests we need to make sure that Kerberos is in place, but I'm not sure (a) how valid this is (i.e. is it really a requirement?) or (b) how to go about investigating if it's set up or how to go about setting it up.

We're in a situation where we need to be able to either configure IIS or the application to work for the customer, or explain to the customer exactly what they need to do to get it working.

We've managed to reproduce this on our internal network with a test SQL server and a developer's IIS box, so we're going to mess around with this set up and see if we can come up with a solution, but if anyone has any bright ideas, I'd be most happy to hear them!

I'd especially like to hear people's thoughts or advice in terms of Kerberos. Is this a requirement, and if it is, how do I outline to customers how it should be configured?

Oh, and I've also seen a couple of people mention the 'classic one-hop rule' for domains and passing windows credentials around, but I don't know how much weight this actually holds?

Thanks!

Matt

Answer 1

This is called the Double-Hop Problem and prohibits the forwarding of user's credentials to third parties. This occurs when they browse from one machine, against a site on another (first hop), and forwarding the credentials to a third machine (second hop).

The problem will not appear if you host IIS and SQL Server on the same machine.

There's alot more technical details published on this at How to use the System.DirectoryServices namespace in ASP.NET, which explains the double-hop issue, and primary and secondary tokens.

Answer 2

To run your application under the user's Active Directory or Windows credentials, ensure these:

• the IIS application is set to NOT allow anonymous access
• the IIS application uses Integrated Windows authentication

• your connection string should have Integrated Security=SSPI to ensure the user's Windows/AD credentials are passed to SQL Server.

  i.e. Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;