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.

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,

--

(O)enone

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.

"Oenone" wrote:

> > 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.
>
> 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,
>
> --
>
> (O)enone
>
>
>

Patrick wrote:
> 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.

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?

--

(O)enone

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?)

"Oenone" wrote:

> Patrick wrote:
> > 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.
>
> 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?
>
> --
>
> (O)enone
>
>
>

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)



"Patrick" <(E-Mail Removed)> wrote in message
news:5A512722-7ECF-4A98-9DD2-(E-Mail Removed)...
>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?

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)?

"Bruce Barker" wrote:

> 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)
>
>
>
> "Patrick" <(E-Mail Removed)> wrote in message
> news:5A512722-7ECF-4A98-9DD2-(E-Mail Removed)...
> >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?
>
>
>

"Patrick" <(E-Mail Removed)> wrote in message
news:E6B83B86-3EA2-4E89-A545-(E-Mail Removed)...
> 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)?
>

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

John

Thanks a lot for Bruce and John's informative inputs.

Hi Patrick,

For the ASP.NET impersonation, when we use implicit impersonation like:

<identity impersonate="true" />

the remote client's credential will be used to establish a network logon
session which doesn't have network credential for further network hop.
That's why for general condition, when client visist your asp.net app and
the asp.net use clientside credential to access the remote sqlserver will
fail.

For the local IIS5 XP condition, because you are test through the local
browser , when the IIS accept the clientside credential (local client), it
directly use the client user's interactive logon session( rather than
establish a network logon session) which surely contains the network
credential, so in such condiitino, the asp.net is able to use the
credential to access the remote sqlserver.

Anyway, I think Bruce has completely listed all the possible solutions
currently we have. If there're anything else unclear ,please feel free to
post here.

Thanks & Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)






--------------------
| From: "WJ" <(E-Mail Removed)>
| References: <5A512722-7ECF-4A98-9DD2-(E-Mail Removed)>
<#(E-Mail Removed)>
<E6B83B86-3EA2-4E89-A545-(E-Mail Removed)>
| Subject: Re: ASP.NET -> SQL Server : Impersonation not working!
| Date: Mon, 8 Aug 2005 20:58:36 -0400
| Lines: 13
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| Message-ID: <(E-Mail Removed)>
| Newsgroups:
microsoft.public.dotnet.framework.adonet,microsoft.public.dotnet.framework.a
spnet
| NNTP-Posting-Host: ip70-187-233-38.dc.dc.cox.net 70.187.233.38
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.dotnet.framework.aspnet:116826
microsoft.public.dotnet.framework.adonet:33679
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
|
|
| "Patrick" <(E-Mail Removed)> wrote in message
| news:E6B83B86-3EA2-4E89-A545-(E-Mail Removed)...
| > 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)?
| >
|
| 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)?


"WJ" wrote:

>
> "Patrick" <(E-Mail Removed)> wrote in message
> news:E6B83B86-3EA2-4E89-A545-(E-Mail Removed)...
> > 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)?
> >
>
> Because you "login" to Windows XP where the IIS-5 is on the same box. This
> is called integrated Windows security.
>
> John
>
>
>