ASP.NET -> SQL Server : Impersonation not working!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I set my web.config as follows:
<authentication mode="Windows" />
<identity impersonate="true" />

Logon to my ASP.NET website as a user who can authenticate to the target
database.

1) Works fine on my local PC running IIS5.1 on WinXP Pro SP1
2) does not work on IIS6.0 on Windows 2003 server:
System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason:
Not associated with a trusted SQL Server connection.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.Practices.EnterpriseLibrary.Data.Database.OpenConnection()
HOWEVER, Environment.UserName returns the correct username!


Why? How to fix?
 
2) does not work on IIS6.0 on Windows 2003 server:
System.Data.SqlClient.SqlException: Login failed for user '(null)'.
Reason:
Not associated with a trusted SQL Server connection.
Click to expand...

I spent a lot of time fighting this error message and eventually figured it
all out.

How are you specifying the username and password that you want the process
to impersonate?

The way I solved this was as follows (this assumes you're not running in IIS
5.0 isolation mode):

1. Create a domain user that is allowed to access the SQL Server database.

2. On your Windows Server 2003 PC, edit the IIS_WPG usergroup and add the
user you have configured.

3. Right-click the DefaultAppPool in IIS Manager and select Properties. On
the Identity tab, select "Configurable" and then enter the DOMAIN\UserName
and Password values into the appropriate boxes.

4. Back in IIS Manager, select Properties for your web site and ensure that
its Application pool is set to DefaultAppPool, that it has an Application
name (click Create if it's not set) and that the Execute permissions are set
to Scripts only.

With this all done, it worked fine for me, using the user credentials
entered against the Application pool as its impersonation user.

Hope that helps,
 
What I do NOT want connection to the SQL Server to be with a fixed Domain
username/password, but rather I want the user to pass the username/password
from the web browser to IIS6 and for IIS6/ASP.NET to pass the credentials to
SQL Server.
 
Patrick said:
What I do NOT want connection to the SQL Server to be with a fixed
Domain username/password, but rather I want the user to pass the
username/password from the web browser to IIS6 and for IIS6/ASP.NET
to pass the credentials to SQL Server.
Click to expand...

Aha -- I'm not sure how you'd do it in that case...

Are you wanting the user credentials to be those of the user in whose
identity the browser is running? (For example, if I logged on to your
network as MYDOMAIN\Fred and opened the web browser, would you want the
connection to the server to be under the user credentials of MYDOMAIN\Fred?)
Or would you want the user to type them into a form in the browser?
 
wanting the user credentials to be those of the user in whose identity the
browser is running? (For example, if I logged on to your network as
MYDOMAIN\Fred and opened the web browser, would you want the connection to
the server to be under the user credentials of MYDOMAIN\Fred?)
 
this will only work if the sqlserver is on the same box as IIS. this is
because ntlm authentication does not allow forwarding of creditals (1 hop
rule). you have 4 options:

1) switch to basic authentication. this will give IIS a primary token it can
use to access a remore sqlserver.
2) switch to kerberos authentication and enable creditials forwarding.
3) use a fixed account
4) move the SqlServer to the IIS box.


-- bruce (sqlwork.com)
 
Why does it work when the ASP.NET is on IIS5.1 on WinXP SP1 (which is on a
different box but in the same domain as the SQL Server)?
 
Patrick said:
Why does it work when the ASP.NET is on IIS5.1 on WinXP SP1 (which is on a
different box but in the same domain as the SQL Server)?
Click to expand...

Because you "login" to Windows XP where the IIS-5 is on the same box. This
is called integrated Windows security.

John
 
But surely, when I login to my XP, then open up
http://myServer/impersonation.aspx, my IE6 browser also pass in my
credentials to myServer, and that is called Integrated Windows
Authentication, too regardless of whether myServer is IIS6.0 or IIS5.1, as
long as it is in the same domain!

How else did myServer managed to log Environment.UserName correctly
(corresponding to the user launching http://myServer/impersonation from a
remote WinXP IE6 browser in the same domain)?
 
¤ But surely, when I login to my XP, then open up
¤ http://myServer/impersonation.aspx, my IE6 browser also pass in my
¤ credentials to myServer, and that is called Integrated Windows
¤ Authentication, too regardless of whether myServer is IIS6.0 or IIS5.1, as
¤ long as it is in the same domain!
¤

No, NTLM does not pass credentials to IIS when your web app is configured for Integrated Windows
security. NTLM handles the authentication. In order to delegate credentials via the web server NTLM
must be used in conjunction with Kerberos.

¤ How else did myServer managed to log Environment.UserName correctly
¤ (corresponding to the user launching http://myServer/impersonation from a
¤ remote WinXP IE6 browser in the same domain)?
¤

As was mentioned, the difference is that IIS is not capable of forwarding encrypted credentials (it
does not have) for delegation to a remote server when using Integrated Windows security. This is
different than being authenticated locally on your machine where the credentials are known and can
be forwarded for remote resource authentication. In addition, while the authenticated user name may
be known, the password is not.

BTW if you were using Basic authentication, where clear text credentials can be delegated remotely,
the integrated security mechanism should function as you expect.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
I have the same problem. Is there a book or something that spells out exactly
what needs to happen to make this work? I also have it working locally with
XP (so i thought this solution was good), promoted it to the server, only to
run into this problem. Seems to be more difficult than it has to be for
something that appears to be common to do (retrieve data from your SQL server
and display it in your .net pages).

Looking for an answer also,
lyners
 
Assuming you are using windows auth to connect to SQL Server - which it
looks like you are - you are more than likely running into the infamous
"double hop" issue. You cannot go from client -> (one hop) web server ->
(2nd hop) sql server unless you allowed delegation for the account that are
trying to authenticate.

It is possible to achieve what you are trying to do, but it will require
some additional setup. Check out these links for more info:

http://odetocode.com/Blogs/scott/archive/2005/02/24/1053.aspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
http://pluralsight.com/blogs/keith/archive/2004/07/08/1586.aspx

Kevin Cunningham
 
Actually, if the user is logged onto our intranet, that is good enough
security. I am trying to make the IIS talk to the SQL server, I have been
experimenting with different setups, but i can't get any data back. I usually
get "Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection" What is the correct way to setup a connection to a SQL
server running on a Windows 2003 server from an IIS Windows 2003 server? I
know the second hop is my problem, but what is the best business practice for
setting up the conection?
 
Back
Top