The Two-Hop issue and Web apps
1. Your ASP.NET web app has an <identity impersonate="true"/> in the web.config. You are accessing SQL server from your web app using Windows authentication. Your users' windows accounts DO have access provided in SQL server. IIS is set to Integrated Windows authentication.
Your application works locally or in a Dev server as long as SQL server is installed in the web server itself. You move the app to User Acceptance environment, where SQL server is in a different box from the web server. Permissions are unchanged. Yet, your web application throws an authentication error when accessing SQL server - it is a classic case of "it works in dev, but not in other environments".
2. Your ASP.NET web app accesses a network file share for some files. The web app, again, has <identity impersonate="true/> in the web.config. Your users have permission to the network file share. IIS is set to Integrated Windows authentication.
Your app works fine in dev, as long as your browser is in the same box as the web server. Thus it works well in development. You move the app to a different server, SRV 1. All of a sudden, even for you, when you browse the app from your computer and hit SRV 1, you get an authentication error - suddenly, your network share is not accessible.
But it works if you open the browser in the SRV 1 box itself!
When encountering the above two and various similar scenarios, I have seen developers think that the problem is specific to SQL server, their web app, IIS or the network share. But what you are probably facing is the dreaded Two-Hop issue - it is an important infrastructural issue. It has nothing to do with specific services like SQL Server or IIS.
The Two-Hop issue
Very specifically, what you are attempting in the above two scenarios is this: you are trying to traverse, using an user's windows identity, from your browser to the web server (First Hop) and then from the web server to the SQL server (or network share or LDAP server - this is the Second Hop).
The user (you) have entered your windows network user name and password in your box, and you have been authenticated. With this authentication (that is, with your password) you can ONLY go from your box to one other (any other) server that you have permission to. Once your id reaches the web server, the web app is trying to pass it on to the SQL server or network server(that is the meaning of <identity impersonate="true"/>). BUT IT CANNOT DO SO! Why not? Because in a windows network, the primary authentication token (in other words, your password) is only valid for one server to another. The recipient server cannot pass it on to another server and so forth.
This is a very important security restriction - and it has nothing to do with specific services like IIS or SQL server. It is the nature of the windows security infrastructure.
When IIS or SQL server "authenticate" you, and they use Integrated Windows Authentication, then the authentication process is handled by the underlying operating system. The operating system treats it simply as a request from one server to another. The web app's request to SQL server is no different from opening a network share on the SQL server machine from the web server - both these requests use the SAME authentication mechanism.
Since the flow is from server to server, the Two-Hop issue kicks in. Now let us see the above scenarios again:
Your browser makes the request to web app - you enter your network user id and password. Therefore you are authenticated to the web server.
ASP.NET now sees the <identity impersonate="true"/> tag and uses your identity for all further network requests.
Now, the web app makes a connection to SQL server.
If the SQL server is on the same server (as the web app), then you are already authenticated to the web server (it is the first hop). Therefore, SQL server successfully authenticates you.
Instead, as is normal, SQL server is on a different server, then the web app tries to pass on your identity to the SQL server service. The service now asks the underlying operating system to authenticate your network id. That authentication FAILS because the call from the web server to the SQL server is a second hop.
The same applies to the network share scenario.
What does this mean for enterprises? Enterprises prefer Windows authentication in SQL server because it is not passed around easily and is much easier to manage. But SQL server with windows authentication will NEVER work the ideal way for web apps - by passing through the user's identity from the browser to the web server and then the web server to a separate SQL server.
The enterprise faces the following options:
1. Go for SQL server authentication and its attendent issues (such as passwords and user ids being shared by everyone).
2. Go for Windows authentication, BUT impersonate a specific "service" user account in the web app. That is, instead of setting "<identity impersonate="true"/>", set "<identity impersonate="true" username="appserviceuser" password="password"/>".
You could also encrypt this password and thereby protect yourself from attacks.
In SQL server, then, you give this serviceuser account limited privileges.
This will work because now you are not passing in the primary (browser) user's token. Instead you are generating a new primary token (through the password) for a specific account and authenticating with SQL server.(This will also work for network shares).
The big con here is the proliferation of such service user accounts, making them compliant with password reset policies etc. For example if the service user account's password expires after 30 days, suddenly one fine morning your web app will stop working. So you have to go in and change the password every time.
But, this is the option many smaller companies choose.
3. Use Basic Authentication in IIS. This means that your password is available in the web server and the web application can then (programmatically) use an API to impersonate the web browser user.
This is a really bad option, for a simple reason - let us say this particular app is used by the company's executive management. The web developer can log that supplied password to a file or email it to himself and then get sensitive information about the company.
4. Enable Kerberos delegation
This is a complex mechanism - but the crux is that setting a certain set of options in Active directory enables the web server (or any second server in the first hop) to propagate the server's primary token to other downstream servers. This is a preferred option in some places but it does have a downside: similar to basic authentication, the web developer can use the higher privileges of a end user and programmatically do things he/she is not supposed to do -such as access the boss's email account.
5. Use SiteMinder or Micorosft ISA or such a Single Sign On solution. This will usually result in an Agent mediating authentication. Using such a solution, you can basically use Windows authentication all through - for example, we recently implemented a Business Intelligence solution, with SiteMinder mediating. We could propagate from Sharepoint (MOSS 2007) in one web server to Reporting Services in another to Analysis Services in yet another server - no issues. But this is an expensive solution that bigger enterprises use.
So, take the decision to use Windows authentication (for web-browser end users) in SQL server carefully - it may not be a simple choice.
The simplest way to remember the Two-Hop rule is this: when you supply a password, it is good for one server to another. That is it. For propagating your identity further in a windows network, you need to enable delegation or collect the password and do it programmatically.
It is important to note that this is not a "limitation" in the network - it is a good rule and exists for a reason.