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

Solution 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.

Solution 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;

Comments

[Matt Winward]

The second and third points I have already. I also tried disabling anonymous access earlier today and this didn't help. Interestingly, we have this working fine in similar customer environments where we have anonymous access enabled (as well as win auth obviously). But thanks for the feedback though!

[Matt Winward]

It connects to SQL server as Windows NT\Anonymous Logon

[Matt Winward]

Thanks for that link. Looks like a good read! Will go through it tomorrow.

[Matt Winward]

Sorry, that should have been 'NT Authority\Anonymous Logon'

[Matt Winward]

Excellent articles.. thank you! Looks like we're facing three possible solutions: telling the customer to deploy Kerberos, run the web app using a fixed domain account to connect to SQL Server or hosting the web application with SQL Server.

[Matt Winward]

For anyone interested, it was the 'double-hop' problem, and our final solution was in the Active Directory Users and Computers MMC snap-in. We looked at the properties of the computer account (for the IIS server) and on the “Delegation” tab allowed delegation to specific services.We added the SQL Server that hosts the application databases to the list of machines that accept delegation, and specified the MSSQLSvc service. This ensures that the IIS server is only trusted to pass credentials for SQL Server, and not for other services.

[Matt Winward]

That's brilliant - thank you! Indeed, we're now a lot more aware of the issue and now have white papers detailed how customers should configure their active directory settings. But this could be very useful for customers who aren't savvy enough to know how their network is configured! It will certainly help when troubleshooting, so thanks for that. I'll go have a play!