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

[Patrick]

I set my web.config as follows:
<authenticati on mode="Windows" />
<identity impersonate="tr ue" />

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.Sql Client.SqlExcep tion: Login failed for user '(null)'. Reason:
Not associated with a trusted SQL Server connection.
at System.Data.Sql Client.Connecti onPool.GetConne ction(Boolean&
isInTransaction )
at
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne ctionString options, Boolean& isInTransaction )
at System.Data.Sql Client.SqlConne ction.Open()
at Microsoft.Pract ices.Enterprise Library.Data.Da tabase.OpenConn ection()
HOWEVER, Environment.Use rName returns the correct username!
Why? How to fix?

[Oenone]

> 2) does not work on IIS6.0 on Windows 2003 server:

System.Data.Sql Client.SqlExcep tion: 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 "Configurab le" 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]

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.Sql Client.SqlExcep tion: 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 "Configurab le" 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

[Oenone]

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

[Patrick]

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

[Bruce Barker]

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" <qu*******@news group.nospam> wrote in message
news:5A******** *************** ***********@mic rosoft.com...

I set my web.config as follows:
<authenticati on mode="Windows" />
<identity impersonate="tr ue" />

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.Sql Client.SqlExcep tion: Login failed for user '(null)'.
Reason:
Not associated with a trusted SQL Server connection.
at System.Data.Sql Client.Connecti onPool.GetConne ction(Boolean&
isInTransaction )
at
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne ctionString
options, Boolean& isInTransaction )
at System.Data.Sql Client.SqlConne ction.Open()
at Microsoft.Pract ices.Enterprise Library.Data.Da tabase.OpenConn ection()
HOWEVER, Environment.Use rName returns the correct username!
Why? How to fix?

[Patrick]

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" <qu*******@news group.nospam> wrote in message
news:5A******** *************** ***********@mic rosoft.com...

I set my web.config as follows:
<authenticati on mode="Windows" />
<identity impersonate="tr ue" />

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.Sql Client.SqlExcep tion: Login failed for user '(null)'.
Reason:
Not associated with a trusted SQL Server connection.
at System.Data.Sql Client.Connecti onPool.GetConne ction(Boolean&
isInTransaction )
at
System.Data.Sql Client.SqlConne ctionPoolManage r.GetPooledConn ection(SqlConne ctionString
options, Boolean& isInTransaction )
at System.Data.Sql Client.SqlConne ction.Open()
at Microsoft.Pract ices.Enterprise Library.Data.Da tabase.OpenConn ection()
HOWEVER, Environment.Use rName returns the correct username!
Why? How to fix?

[WJ]

"Patrick" <qu*******@news group.nospam> wrote in message
news:E6******** *************** ***********@mic rosoft.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)?

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

John

[Steven Cheng[MSFT]]

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="tr ue" />

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" <Jo*******@HotM ail.Com>
| References: <5A************ *************** *******@microso ft.com>
<#k************ **@tk2msftngp13 .phx.gbl>
<E6************ *************** *******@microso ft.com>
| 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: <OE************ **@TK2MSFTNGP10 .phx.gbl>
| Newsgroups:
microsoft.publi c.dotnet.framew ork.adonet,micr osoft.public.do tnet.framework. a
spnet
| NNTP-Posting-Host: ip70-187-233-38.dc.dc.cox.ne t 70.187.233.38
| Path: TK2MSFTNGXA01.p hx.gbl!TK2MSFTN GP08.phx.gbl!TK 2MSFTNGP10.phx. gbl
| Xref: TK2MSFTNGXA01.p hx.gbl
microsoft.publi c.dotnet.framew ork.aspnet:1168 26
microsoft.publi c.dotnet.framew ork.adonet:3367 9
| X-Tomcat-NG: microsoft.publi c.dotnet.framew ork.aspnet
|
|
| "Patrick" <qu*******@news group.nospam> wrote in message
| news:E6******** *************** ***********@mic rosoft.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)?
| >
|
| Because you "login" to Windows XP where the IIS-5 is on the same box.
This
| is called integrated Windows security.
|
| John
|
|
|

[Patrick]

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.Use rName correctly
(corresponding to the user launching http://myServer/impersonation from a
remote WinXP IE6 browser in the same domain)?
"WJ" wrote:

"Patrick" <qu*******@news group.nospam> wrote in message
news:E6******** *************** ***********@mic rosoft.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)?

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

John