I have an asp.net application that should access data from two SQL Servers. One of the SQL Servers is present on the same machine as IIS (let us call it SQLSERVER1) whereas the other SQL server is present on another machine (SQLSERVER2).
The connection strings are trusted for both the SQL servers. Impersonation has been set to true in my web.config file. I am using Windows authentication in both IIS and web.config.
When I try to access data from SQLSERVER2, I get login failed for user(null) error. The user through which I have logged in through Windows exists as a SQL server account in SQLSERVER2.
What could be the possible reason?
NOTE: This is a newbie question IMHO.
NOTE: The IIS used is 6.0 (Windows 2003). It is not set to IIS 5.0 isolation mode.
EDIT:The user getting impersonated is a domain user
Addition:
I also want to state that I get this error message when I access it as a client of the server where IIS is running. In other words, let me say I am working on machine A, the IIS and SQLSERVER1 are on machine B, and SQLSERVER2 is on machine C.
I do not get this error message when I am working on machine B. This is stumping me more.
Best Answer
This is absolutely a delegation problem. As one person pointed out, you need to make sure Kereberos authentication is being used. The old style NTLM isn't going to cut it. Here's more on Kerberos vs. NTLM.
In a nutshell, if you have a webserver and a database and you want the webserver to impersonate the user when making database requests (so that you can set up permissions on the database directly on a per-user or user-group basis) you're performing a double-hop. Credentials must past first from the user's computer to the webserver and again to the database. As you can imagine, the database has to trust the webserver to "do no evil" or this could be an extremely dangerous security hole. As a result, you have to set up what is called in the Windows Server world "delegation"...
Microsoft has a good article about all this here. Further, you can look over an article like this to get an idea of how to set it all up. We've run into this frequently, and it can be a pain at first, especially since as a developer you're probably not in control of the servers directly (especially production ones) and you'll have to spend a lot of time with the server guys down the hall.