How to configure IIS so that user credentials are used when connecting to the SQL server?

Question

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

Recently, we found that on several client deployments, although IIS can see the domain user credentials, it will not pass them to the SQL server. Instead, it uses an anonymous account. This is despite all the right steps (changing directory security on Win Auth, updating Web.Config to use Win Auth and rejecting anonymous users).

I read a lot, which suggests that we need to make sure Kerberos is in place, but I'm not sure (a) how fair this is (i.e. is this a requirement?) Or (b) how to start an investigation, if configured or how to configure it.

We are in a situation where we need to be able to configure the IIS or application to work for the client or explain to the client exactly what they need to do to make it work.

We managed to reproduce this on our internal network using the test SQL server and the IIS developer box, so we are going to team up with this setting and see if we can come up with a solution, but if anyone has bright ideas, I would be happy to hear them!

I especially wanted to hear people's thoughts or advice in terms of Kerberos. Is this a requirement, and if so, how can I tell clients how it should be configured?

Oh, and I also noticed that a few people mention the “classic one-hop rule” for domains and transferring Windows credentials, but I don’t know how much weight this really takes place?

Thanks!

Matt

Answer 1

This is called the Double-Hop Problem and prohibits the transfer of user credentials to third parties. This happens when they browse from one computer to another site (the first interception) and redirect credentials to a third computer (second jump).

The problem does not appear if you are hosting IIS and SQL Server on the same computer.

There are more technical details published on this site. How to use the System.DirectoryServices namespace in ASP.NET , which explains the double hop release, primary and secondary tokens.

Answer 2

To run the application under an Active Directory or Windows user account, verify the following:

• IIS Application Set to NOT Allow Anonymous Access
• The IIS application uses integrated Windows authentication.

• your connection string must have Integrated Security=SSPI in order to provide Windows / AD user credentials for SQL Server.

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

Answer 3

You state that you are not sure "how to start an investigation if it is set up or how to set it up."

For this, I would recommend the DelegConfig tool. This is a very handy application that you can tell you if kerberos is configured correctly.

Unzip it to a directory, configure the virtual directory in IIS to point to it. Go to the main page and tell which server server you want to allow access to (for example, UNC, SQL, HTTP, etc.), and it will tell you correctly or incorrectly about its settings and explain why .

He even has a desire to overdo it in kerberos to fix the problem, if you want it (although I did not use it), I would rather reconfigure it myself to understand what I did in the future)

I understand that this is too late for your specific problem, but I thought it was worth sharing for others that follow - especially tools that can explain why the delegation is working or not working. I found him unclaimed.