Problem with sql connection with impersonate true

  • Thread starter Thread starter WT
  • Start date Start date
W

WT

Hello,

IIS6 on W2K3, .net 3.5, Sql 2005.
All sp applied.

My site is using windows authentication only and the web application
connects to sql server residing on another server in the same domain using
following connection string:
<connectionStrings>
<add name="My_ConnectionString"
connectionString="server=localhost;database=MyDB;Trusted_Connection=true;"
providerName="System.Data.SqlClient"/>
</connectionStrings>


When browsing on the IIS server, I have no connection problem, but when
starting the application from anoter station or server in same domain I get
following error:

System.Data.SqlClient.SqlException: Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

I don't understand why ado.net is doing the connexion with the account NT
AUTHORITY\ANONYMOUS LOGON a my current user is member of and authentified on
domain.
It even is member of an AD group which has public access to the DB used by
my application ?


Any help welcome.

CS
 
Hello,

IIS6 on W2K3, .net 3.5, Sql 2005.
All sp applied.

My site is using windows authentication only and the web application
connects to sql server residing on another server in the same domain using
following connection string:
<connectionStrings>
  <add name="My_ConnectionString"
connectionString="server=localhost;database=MyDB;Trusted_Connection=true;"
providerName="System.Data.SqlClient"/>
 </connectionStrings>

When browsing on the IIS server, I have no connection problem, but when
starting the application from anoter station or server in same domain I get
following error:

System.Data.SqlClient.SqlException: Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
   at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
   at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()

I don't understand why ado.net is doing the connexion with the account NT
AUTHORITY\ANONYMOUS LOGON a my current user is member of and authentifiedon
domain.
It even is member of an AD group which has public access to the DB used by
my application ?

Any help welcome.

CS
Click to expand...

You should look at adding an Identity to your configuration. One way
is simply to enter an identity tag containing user name and password
info into your application's web.config file. It would then run in the
context of that identity. There are even options for encrypting the
password.
 
Thanks Joe,

But I need to impersonate in all the application, using credential to
authentify on other services.
My question is why SQL doesn't recognize my credentials as my IE is
configured to use them ?
Is it a windows/kerberos problem between IIS being on one server and sql on
another (2 jumps) ?
How to avod this ?

CS

"Joey" <[email protected]> a écrit dans le message de
Hello,

IIS6 on W2K3, .net 3.5, Sql 2005.
All sp applied.

My site is using windows authentication only and the web application
connects to sql server residing on another server in the same domain using
following connection string:
<connectionStrings>
<add name="My_ConnectionString"
connectionString="server=localhost;database=MyDB;Trusted_Connection=true;"
providerName="System.Data.SqlClient"/>
</connectionStrings>

When browsing on the IIS server, I have no connection problem, but when
starting the application from anoter station or server in same domain I
get
following error:

System.Data.SqlClient.SqlException: Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

I don't understand why ado.net is doing the connexion with the account NT
AUTHORITY\ANONYMOUS LOGON a my current user is member of and authentified
on
domain.
It even is member of an AD group which has public access to the DB used by
my application ?

Any help welcome.

CS
Click to expand...

You should look at adding an Identity to your configuration. One way
is simply to enter an identity tag containing user name and password
info into your application's web.config file. It would then run in the
context of that identity. There are even options for encrypting the
password.
 
asp.net seperates the thread nt credentials used for accessing resources and
the request credentials. by default they are seperate.

normally to access sqlserver, you need to set the app pool account to a
domain account with access to the sqlserver. if you can not update the pool
account, you can specifiy a user and and password in the web.config via the
<identity> tag.

you also can enable impersonation of the web request account via this tag,
but window authenication will not allow access to a remote server (1 hop
rule), you would need to use basic or kerberos.


-- bruce (sqlwork.com)
 
Hi,

What Bruce said is reasonable. This is a typical í¦double hopí¦ issue. For
the security reason the impersonated identity can only be used to access
the resources that are owned by the web server itself.

The following article describes the í¦double hopí¦ issue in detail and
provides some workarounds:

http://blogs.msdn.com/nunos/archive/2004/03/12/88468.aspx

If you have further questions please feel free to ask.

Regards,
Allen Chen
Microsoft Online Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "WT" <[email protected]>
| References: <[email protected]>
<f8e6e86a-3cb2-44bf-b2e3-a7ccf166852a@w39g2000prb.googlegroups.com>
| In-Reply-To:
<f8e6e86a-3cb2-44bf-b2e3-a7ccf166852a@w39g2000prb.googlegroups.com>
| Subject: Re: Problem with sql connection with impersonate true
| Date: Tue, 11 Nov 2008 17:40:24 +0100
| Lines: 63
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 8bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6001.18000
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18049
| X-MS-CommunityGroup-PostID: {73784E56-5FAD-4E19-AB55-3967B406894E}
| X-MS-CommunityGroup-ThreadID: 3FAEB607-A1D7-48DB-9EB9-03477E55BB8B
| X-MS-CommunityGroup-ParentID: 73A55EAF-493C-4261-A896-307D82E2F318
| Newsgroups: microsoft.public.dotnet.framework.aspnet
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl
microsoft.public.dotnet.framework.aspnet:79661
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
|
| Thanks Joe,
|
| But I need to impersonate in all the application, using credential to
| authentify on other services.
| My question is why SQL doesn't recognize my credentials as my IE is
| configured to use them ?
| Is it a windows/kerberos problem between IIS being on one server and sql
on
| another (2 jumps) ?
| How to avod this ?
|
| CS
|
| "Joey" <[email protected]> a écrit dans le message de
| | > Hello,
| >
| > IIS6 on W2K3, .net 3.5, Sql 2005.
| > All sp applied.
| >
| > My site is using windows authentication only and the web application
| > connects to sql server residing on another server in the same domain
using
| > following connection string:
| > <connectionStrings>
| > <add name="My_ConnectionString"
| >
connectionString="server=localhost;database=MyDB;Trusted_Connection=true;"
| > providerName="System.Data.SqlClient"/>
| > </connectionStrings>
| >
| > When browsing on the IIS server, I have no connection problem, but when
| > starting the application from anoter station or server in same domain I
| > get
| > following error:
| >
| > System.Data.SqlClient.SqlException: Login failed for user 'NT
| > AUTHORITY\ANONYMOUS LOGON'.
| > at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
| > owningObject)
| > at
| > System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
| > owningConnection)
| > at
| > System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
| > outerConnection, DbConnectionFactory connectionFactory)
| > at System.Data.SqlClient.SqlConnection.Open()
| >
| > I don't understand why ado.net is doing the connexion with the account
NT
| > AUTHORITY\ANONYMOUS LOGON a my current user is member of and
authentified
| > on
| > domain.
| > It even is member of an AD group which has public access to the DB used
by
| > my application ?
| >
| > Any help welcome.
| >
| > CS
|
| You should look at adding an Identity to your configuration. One way
| is simply to enter an identity tag containing user name and password
| info into your application's web.config file. It would then run in the
| context of that identity. There are even options for encrypting the
| password.
|
|
 
Hi,

Do you have any progress on this issue?

Regards,
Allen Chen
Microsoft Online Community Support
 
Yes, thanks.
I found something related to Kerberos double hope problem on Windows 2003
server.
I had to set approbation in AD for the server running IIS concerning the Sql
service and it solved my problem.

I discovered further strange features, as for example trying to access my
server from a PC out of the domain (in workgroup) and entering a domain
account allows access to web site but do not allways benefits of SPN: on
some conditions it works, on some other no access to sql, I have been unable
to find why.

Similarly, playing with firefox to check compatibility I discovered that
FireFox is unable to use windows authentification even after asking to enter
log/pwd I don't get access. But if I had first authentified with IE7 on same
PC, FireFox runs the app. Found this funny...but not very usefull.

I thing that we need the 'Dragon's book for authentication and access' in MS
technologies.

Ragards
 
Hi,

Have you got the expected answer?

Regards,
Allen Chen
Microsoft Online Community Support
 
Back
Top