problem connecting to dbase from webservice with impersonation

[Nadav Popplewell]

Hi everybody,

I've got a ASP.NET 2.0 WebService that need to connect to a SQL Server on
another machine.

I've setup the anonymous Account of the web application to an domain account
that has permissions to access the dbase,
and I've set

<identity impersonate="true" />

in the web.config file.

The connection to the dbase fails with error
"Login failed for user ''. The user is not associated with a trusted SQL
Server connection."

If I put the user and password of the domain account in the <identity ..>
element (SAME account as that defined as the annonymous account) then the
WebService Connects to the dbase.

I've debugged the WebService and checked
System.Security.Principal.WindowsIdentity.GetCurrent().

These are the properties of the WindowsIdentity:

No user/password:

AuthenticationType "MICROSOFT_AUTHENTICATION_PACKAGE_V1_0
ImpersonationLevel Delegation
Name "NI\\nadav_p"

with user/password:

AuthenticationType "Kerberos"
ImpersonationLevel Impersonation
Name "NI\\nadav_p"

I've tried reading about this, and as far as I can see delegation does not
work unless kerberos is used.
Is this right?

Has anybody got any ideas how I can connect to the dbase from my WebService
without writing my password in the web.config?

Thanks,

Nadav

 

[Nadav Popplewell]

I've tried connecting to the same SQL from an asp.net 1.1 WebService on the
same IIS (same anonymous account as the ASP.NET 2.0 WebSite).

for ASP.NET 1.1 the connection succeeds even if the user/password are not
specified in the web.config file.

This seems to be something that's changes in asp.net 2.0.

Nadav

 

[Steven Cheng[MSFT]]

Hello Nadav,

From your description, you have an ASP.NET 2.0 webservice application which
will acess a remote SQL Server instance, so you want to let your ASP.NET
application run under a domain account so as to access the remote SQL
Server. Your current implementation is configured the domain account as
IIS virtual directory's anonymous user identity and set impersonate=true in
web.config so as not to expose username/password credentials in web.config
file. However, you found this not work and will result to "Login failed for
user The user is not associated with a trusted SQL Server connection."
error. If there is anything I missed , please feel free to corret me.


As you mentioned that the same setting works well for ASP.NET 1.1, based on
my experience, there is no such significant change from ASP.NET V1 to V2.
For your scenario, I've also tested according to your setting on my local
environment and such solution can work well for connecting remote SQL
Server through domain account. There is not particular difference on this
from ASP.NET V1 and V2.

As for the problem you met, I think it is likely environment specific. I
think you can have a check on the following things:

1. As for the connection string, are you specify the "integraetd
security=true" option as below?

<add name="TestConnectionString" connectionString="Data
Source=sha-weilu-test;Initial Catalog=Test;Integrated Security=True"
providerName="System.Data.SqlClient" />

2. Since you configure your ASP.NET application to impersonate and running
under a domain account, have you granted that domain account sufficient
permission for accessing any necessary ASP.NET resources. For .net 2.0,
you can use the aspnet_regiis -ga command to grant a given account
sufficient permission to executing ASP.NET code:

#ASP.NET IIS Registration Tool (Aspnet_regiis.exe)
http://msdn2.microsoft.com/en-us/library/k6h9cz8h.aspx

3. Based on the debugger trace info, when you encountered the problem, the
authenticationtype display " MICROSOFT_AUTHENTICATION_PACKAGE_V1_0" which
is not clear whether it uses NTLM or Kerberos( the two main protocol for
intergrated windows authentication). It is possible that the anonymous
account's login is not authenticated through one of the valid integrated
windows authentication protocol and result to the "login failed" at SQL
Server side. (in my local test under ASP.NET 2.0, it display "kerberos")

for this point, you can check your IIS virtual directory or site's
authentication setting to see whether it is configured to use NTML or
kerberos(negotiate), the following kb article has demonstrate how to
configure this in IIS. You can even force your ASP.NET 2.0 webservice's
virtual directory to use NTLM only for test to see whether it works.(NTLM
is enough for accessing remote resource under single hop)

#How to configure IIS to support both the Kerberos protocol and the NTLM
protocol for network authentication
http://support.microsoft.com/kb/215383/en-us

Just some of my understanding and suggestion. Please feel free to let me
know if you have anything unclear or any further finding.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 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 or complex
project analysis and dump analysis issues. 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/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Nadav Popplewell]

Hi Steven,

Thanks for your reply,

1. As for the connection string, are you specify the "integraetd
security=true" option as below?
<add name="TestConnectionString" connectionString="Data
Source=sha-weilu-test;Initial Catalog=Test;Integrated Security=True"
providerName="System.Data.SqlClient" />

Yes, I've specified Integrated Security=True.

2. Since you configure your ASP.NET application to impersonate and running
under a domain account, have you granted that domain account sufficient
permission for accessing any necessary ASP.NET resources. For .net 2.0,
you can use the aspnet_regiis -ga command to grant a given account
sufficient permission to executing ASP.NET code:

The ASP.NET code runs, I can debug it, it's just that the Connection to the
Dbase fails.

for this point, you can check your IIS virtual directory or site's
authentication setting to see whether it is configured to use NTML or
kerberos(negotiate), the following kb article has demonstrate how to
configure this in IIS. You can even force your ASP.NET 2.0 webservice's
virtual directory to use NTLM only for test to see whether it works.(NTLM
is enough for accessing remote resource under single hop)

#How to configure IIS to support both the Kerberos protocol and the NTLM
protocol for network authentication
http://support.microsoft.com/kb/215383/en-us

I've executed the steps in article.

My IIS is set to "Negotiate,NTLM"

Nadav

 

[Steven Cheng[MSFT]]

Hi Nadav,

Thanks for your reply.

What's your server and the domain environment? (such as server OS version,
IIS version and Domain condition). My local test is performed on a windows
2003 server sp1 with IIS6 and virtual directory is allowing NTLM/Negotiate
authentication.

So far, I think the problem is likely due to the windows authentication
against the domain account on the webserver machine. And the problem can be
specific to ether ther webserver(IIS metabase) and the domain account. If
you have some other server machine available, I suggest you do the
following tests:

1. Move the same application to another server machine and configure the
same authentication setting in IIS and test again to see whether the
problem remains.

2. I noticed that the debug info in your last reply indicate that the
problem windowsIdentity has a "Delegation" level and the authenticationType
is also a bit strange, I wonder whether the domain account is configured as
delegatable in DC, you can also verify this. And for test, you can switch
to use another normal domain account to see the behavior.

I've also discussed with some other engineers and they also mentioned that
it is possible caused by the kerberos authentcation context.

Anyway, please feel free to post here if you have any new finding.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Nadav Popplewell]

Hi Steven,

Both my Web Server and Dbase Server are windows XP SP2.
the IIS version is 5.1, and is set to "Negotiate,NTLM".
The Domain controller is Windows 2003 server std R2.

I have performed the same test on three different computers with three
different domain accounts with the same results.

I don't know if the account has the delegatable permission.
How do I see if it does?

Nadav

 

[Steven Cheng[MSFT]]

Thanks for your response Nadav,

Seems quite strange comparing to my local test. For account's delegation
setting, you can refer to the following msdn article:

http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000024.asp?frame=tr
ue

BTW, I think you can still check whether the impersonated account can be
used to access any other remote resources. For example, a straightforward
test is accessing some file on a share directory of the remove server
machine, you can open the folder's NTFS system auditing log to get whether
the impersonated anonymous account is correctly loged on the remote
machine. If file access can work correctly, it seems the problem also
related to database. Else, you can concentrate on the IIS to ASP.NET's
anonymous account authentication.

Please feel free to let me know if you have any further finding.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Nadav Popplewell]

Hi Steven,

Thanks for the quick reply.

I've created a shared folder on the dbase server with write permissions for
the account the ASP.NET is impresonating.
The WebSite can create a file in that shared folder (and the owner is
reported as the impersonated account), but the connection to the dbase fails.

Nadav

 

[Nadav Popplewell]

Hi Steven,

I've did some more tests and I've managed to narrow down the problem.

The WebSite code CAN read from the dbase.
The problem is with the Membership provider.
When I run Membership.ValidateUser() I get the execption about the not
trusted connection.
The Membership provider (SqlMembershipProvider) uses the same connection
string saved in web.config, but for some reason Membership.ValidateUser()
fails even though my code can connect to the SQL and read from it.

I can't believe I didn't notice this before...

Nadav

 

[Steven Cheng[MSFT]]

Thanks for your followup Nadav,

Seems the problem has been quite narrowed. So it is only when performing
query against the SQLMembershipProvider 's database table does the error
occur. Are you currently put the membership's database tables in the same
database with your other application data?

I think you can run SQL profiler at SQLServer side to see what is output
when the Validateuser is called at client-side, you can also compare it
with your normal database access to see what's the difference between them
in SQL profiler trace.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Nadav Popplewell]

Hi Steven,

Yes, The membership's database tables are in the same database as our
application data.
(Some other developer in our team created the dbase, As I understand it he
used some tool of Microsoft to create the membership's tables).

I'll check to see what the difference is between the sql queries from the
membeship provider and normal queries.

Nadav

 

[Steven Cheng[MSFT]]

Hey Nadav,

Any further update on this? Have you got any useful clue from the SQL
profile log?

Please feel free to let me know if there is anything we can help.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.