Windows authentication problems using asp.net

Question

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.

Answer 1

You're probably running into this problem because non-Kerberos based impersonation (NTLM) is only valid on the local machine (the webserver). If you want to be able to use those credentials to access another machine, you're going to need to make sure you're using Kerberos.

Try this: http://support.microsoft.com/kb/810572

Answer 2

Your authentication to the webserver is not passed through to the sql server. The web server is authenticating to the SQL Server using the account that your application pool is running under.

Answer 3

You should check that the machine account for SQLSERVER1 has trusted for delegation enabled. Otherwise SQLSERVER2 won't trust the impersonation running on SQLSERVER1. This is in addition to confirming that Kerberos is used to set up the impersonation in the first place. This also assumes that the servers and the users are all members of the same domain.

BTW, are sure you want to do things this way, you end up creating a lot more connections because they end up being unique to a user?

Answer 4

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.

Answer 5

Have you tried to access the database on server2 using SQL SErver administrator from Server1 and made a successful connection?

If not then this could be because by default SQL Server installs itself with tcp turned off by default.

You will need to make sure that this is turned on for server2 to allow server1 to connect. server1 has no problems connecting due to the fact it can use the shared memory connection.

Answer 6

Dear Bloggers

Error while trying to run project: Unable to start debugging on the web server. Debugging failed because integrated Windows authentication

Please give me a proper solution if someone have

Vijay Kumar